Re: 11.50.xC4 Compression with workgroup edition?

Hi Madison, as indexes are not compressed do I have to rethink the value of index only access if the data item, which was copied to the index, gets compressed? Or does this technique still work as it always did? On many opportunities I saw much better gain performancewise from piggy packing a column into the index and thus avoid the data access altogether vs. N-1 normalization cached into a ram disk (as a lookaside table), which also can save a lot of disk space in the data pages of a poorly designed table, which has many rows and not so many different entries. Of course piggy-packing data into an index is an even more special case than shortening patterns, which are seen many times. dic_k Madison Pruet schrieb: > Ian Michael Gumby wrote: >> On May 20, 12:08 pm, Art Kagel <art.ka...@gmail.com> wrote: >> Many systems actually run faster with compressed data >>> according to IBM's testing and the experiences of early adopters. >>> There is >>> a breakpoint around the number of times the average row is accessed >>> once it >>> is read into the cache and the percentage of rows on a compressed >>> page that >>> are accessed once the page is in memory that determines whether >>> performance >>> improves or degrades due to compression. >> >> Hmmm. >> >> Art, >> >> I didn't think about that in terms of memory usage. However, have you >> recently looked at the price of RAM these days? ;-) >> >> Since IBM doesn't release any benchmarks... (Ooops! :-) ) ... , how is >> it that they can really guage the potential for improvement? >> >> How much do you really gain from compression? Isn't it going to be >> 'data dependent' ? I mean if I have an OLTP system where most of my >> data isn't in VARCHARs, how much compression can I expect? The less >> compression, the less gain in terms of memory paging. >> >> Lets take a look at a hotel reservation system. (Hyatt? Hilton? ...) >> You have a property name, but that gets translated to an id and most >> of your information is going to be non-var char data types. >> (property_id, room_id, room_type, checkin_id, etc ...) So in these >> systems, I don't see the potential value. So what am I missing? > > The compression algorithm is looking for patterns. Common patterns > leads to reductions - regardless of data type. The reservation systems > that I've worked with tend to have a LOT of common data, (rates, day, > etc...) Since it is pattern based, then non-var char data types are > going to just as compressable as char data types. Even numeric data > will compress. Probably the only thing which would not compress very > well would be things such as GIF and JPEG objects. > >> >> Don't get me wrong. I'm not trying to be dense.... >> I mean that I agree that if you can compress the page, you'll be more >> efficient in terms of memory usage. But then you increase your cpu >> load when you have to compress/decompress the fields. >> >> You seem to be implying that you can access the rows with the tables >> compressed. So does that mean if you're running a query and the table >> is compressed, what happens when one of the compressed fields is being >> used as a filter? >> >> Going back to the hotel example... Suppose you want to find an >> available room for a given weekend in New York City. Since the City >> field is probably going to be a varchar, it would be compressed in the >> table. But if I'm filtering on city MATCHES 'New York', does that work >> against a compressed field or does the engine have to decompress it >> when using it as a filter in a query? > > And the indexes are not compressed - so no additional work. >> >> Sorry, I'm skeptical of its value. >> >> You also go on to say the following: >> "Larger tables with higher locality and relatively lower access >> frequency >> will tend to see improvement in processing speed for larger reports >> and more >> active systems. " >> >> I'm not sure what you mean by this. What do you mean exactly by >> 'higher locality'? >> >> I'm sorry, but even as I try to think up some design examples, most >> tend to be normalized and would yield little in value of compression. >> >> Again, I apologize for appearing dense. I just don't get it. 15.5K on >> top of an already expensive enterprise system isn't a good thing. >> I'd love to see some hard numbers, even if its not a real benchmark, >> but an example that could be reproduced by anyone. No hard numbers, >> just percentages of improvement. >> >> Does this make sense? >> >> -- Richard Kofler SOLID STATE EDV Dienstleistungen GmbH Vienna/Austria/Europe

Richard Kofler wrote: > Hi Madison, > > as indexes are not compressed do I have to rethink the value > of index only access if the data item, which was copied to the > index, gets compressed? > Or does this technique still work as it always did? > On many opportunities I saw much better gain performancewise > from piggy packing a column into the index and thus > avoid the data access altogether vs. N-1 normalization cached into > a ram disk (as a lookaside table), which also can save a lot > of disk space in the data pages of a poorly designed table, which > has many rows and not so many different entries. > Of course piggy-packing data into an index is an even more > special case than shortening patterns, which are seen many times. > > dic_k Your strategy is still valid. However by doing key scans and then compressing the portion of the table which is rarely referenced becomes even more compelling. > > > > Madison Pruet schrieb: >> Ian Michael Gumby wrote: >>> On May 20, 12:08 pm, Art Kagel <art.ka...@gmail.com> wrote: >>> Many systems actually run faster with compressed data >>>> according to IBM's testing and the experiences of early adopters. >>>> There is >>>> a breakpoint around the number of times the average row is accessed >>>> once it >>>> is read into the cache and the percentage of rows on a compressed >>>> page that >>>> are accessed once the page is in memory that determines whether >>>> performance >>>> improves or degrades due to compression. >>> >>> Hmmm. >>> >>> Art, >>> >>> I didn't think about that in terms of memory usage. However, have you >>> recently looked at the price of RAM these days? ;-) >>> >>> Since IBM doesn't release any benchmarks... (Ooops! :-) ) ... , how is >>> it that they can really guage the potential for improvement? >>> >>> How much do you really gain from compression? Isn't it going to be >>> 'data dependent' ? I mean if I have an OLTP system where most of my >>> data isn't in VARCHARs, how much compression can I expect? The less >>> compression, the less gain in terms of memory paging. >>> >>> Lets take a look at a hotel reservation system. (Hyatt? Hilton? ...) >>> You have a property name, but that gets translated to an id and most >>> of your information is going to be non-var char data types. >>> (property_id, room_id, room_type, checkin_id, etc ...) So in these >>> systems, I don't see the potential value. So what am I missing? >> >> The compression algorithm is looking for patterns. Common patterns >> leads to reductions - regardless of data type. The reservation >> systems that I've worked with tend to have a LOT of common data, >> (rates, day, etc...) Since it is pattern based, then non-var char data >> types are going to just as compressable as char data types. Even >> numeric data will compress. Probably the only thing which would not >> compress very well would be things such as GIF and JPEG objects. >> >>> >>> Don't get me wrong. I'm not trying to be dense.... >>> I mean that I agree that if you can compress the page, you'll be more >>> efficient in terms of memory usage. But then you increase your cpu >>> load when you have to compress/decompress the fields. >>> >>> You seem to be implying that you can access the rows with the tables >>> compressed. So does that mean if you're running a query and the table >>> is compressed, what happens when one of the compressed fields is being >>> used as a filter? >>> >>> Going back to the hotel example... Suppose you want to find an >>> available room for a given weekend in New York City. Since the City >>> field is probably going to be a varchar, it would be compressed in the >>> table. But if I'm filtering on city MATCHES 'New York', does that work >>> against a compressed field or does the engine have to decompress it >>> when using it as a filter in a query? >> >> And the indexes are not compressed - so no additional work. >>> >>> Sorry, I'm skeptical of its value. >>> >>> You also go on to say the following: >>> "Larger tables with higher locality and relatively lower access >>> frequency >>> will tend to see improvement in processing speed for larger reports >>> and more >>> active systems. " >>> >>> I'm not sure what you mean by this. What do you mean exactly by >>> 'higher locality'? >>> >>> I'm sorry, but even as I try to think up some design examples, most >>> tend to be normalized and would yield little in value of compression. >>> >>> Again, I apologize for appearing dense. I just don't get it. 15.5K on >>> top of an already expensive enterprise system isn't a good thing. >>> I'd love to see some hard numbers, even if its not a real benchmark, >>> but an example that could be reproduced by anyone. No hard numbers, >>> just percentages of improvement. >>> >>> Does this make sense? >>> >>> > >