SYB ASE 15.7 – Best Practices / Lessons Learned

First of all - I am not sure if your intent here is to complain about pricing - in which case it is the wrong place - or if generally just trying to argue ...

However, please don't misconstrue what I have said.  For example, I *never* stated that adding compression make a report go from 10 hours to 5 minutes - that point was about indexing - which you then jumped on with generalizations about why proper indexing was impractical.     Also, table scans - while a common side effect of improper indexing  - is not the only impact/indication of improper indexing.    A much, much, much more common effect is leaf scanning index pages of not-so-great indexes and then doing a data page fetch for every leaf node.   This is much harder to spot than with table scans and is most often missed as many DBA's simply see that the query is using an index and don't check to see how well the index really meets the criteria.

Don't also assume that negating a comment is always correct either.   If I say "Don't eat poisoned apples", stating "you are saying I should eat only unpoisoned apples" is not necessarily true....we all know that a balanced diet is best vs. just eating apples....so twisting my statements wrt to caching and indexing doesn't mean you have the correct interpretation.

My points:

1) Don't bother compressing small tables nor tables that are frequently (mostly) in cache (with today's large memory systems, the size of tables that can be cached sometimes is definitely in the large range).

2) Compression could have significant negative effect if proper indexing is not available.

3) The negative effect of compression significantly amplifies performance degradation as a result of improper indexing which in and of itself is a different issue that should be resolved.

4) The cost of adding an index is not as drastic as it sounds - AND - if there is concern about the overhead of index maintenance, in most cases, there are most commonly bigger existing issues with even more unused indexes than the overhead of adding  a new index.   If there really were concerns, those unused indexes wouldn't exist.

It sounds like your system is more like point #1....in which case data compression may not be an option for at least the current data.    WRT your experience with partitioning, etc.  - again, I can only state that I have been to many places where it worked.   It didn't for you - that is your experience.....  which differs from mine.   Doesn't mean one of us is wrong....just means we have had different experiences. 

We also all need to be aware that most features/options have trade-offs.....sometimes the trade is very advantageous......and other times the trade simply isn't worth it.   Doesn't mean the option is worthless nor does it mean that option has a bad design. You asked a legitimate question to which I explained why dictionary compression has an overhead - whether IBM, Oracle or ASE - doesn't matter - and why it is somewhat impractical (without an even bigger memory hit) to uncompress data in memory.   Having understood that, one can then make better judgment about whether compression will work in their situation.

Actually, 20% is believable - but I wouldn't assume this is a standard/consistent in all cases....  Keep in mind, that it is the number of rows and the number of columns needing to be uncompressed that have an effect.....this was made a bit better in 16.0 - forget the CR number - in which only the columns necessary for query processing were uncompressed vs. all the columns in the row being read.

However, what I was referring to is worse than you think.   For example, if you do a in-memory tablescan with compression, it could be up to 2.5x (250%) slower than an in-memory tablescan without compression - which is much worse than 20%.....and that is what I was referring to.  

In your case with repeated LIO's against the same pages, it likely doesn't help - but consider an annual report where the data is likely read from disk once (for that report) and then not re-read a lot.....the fact I can get more rows per PIO and reduce the PIO times, the better. 

The next question is whether the amount of data that can be retained in cache makes it worth while.   For example, assume I have a table of 1.3 million rows, and I can hold 1GB of data in cache.   Now, assume that in that 1GB I have a choice of either 1 million uncompressed rows - plus physical reads for the remaining 300K rows .....or whether all 1.3 million rows fit when using compression.   Yes, the (in your case) 20% overhead is there - but does it overcome the PIO cost of doing 30% PIO's???  Likely.   However, can't I just increase memory by 30% (as you did) and get the same effect.   Absolutely - as you have shown.   That assumes that a) you can increase the memory in the box; or b) you can buy new boxes with more memory but same number of cpu cores (to avoid additional licensing aspects).   If (a) or (b)....then compression may not be cost effective and have too many negative impacts.    However, for example you are running on Linux and you have an app that runs on the same box as ASE (and it must run there for whatever reason) and it can not run with huge pages (or due to OS bugs with hugepages - which there have been some in the past - fixed now), then you are limited to 256GB of memory for ASE due to OS constraints when hugepages are not in use.   Then getting that extra 30% of data into cache thanks to compression is worth something.

As far as the indexing - what I am referring to is not that all index leaf scans are bad....but if I have an index on {a,b,c,d} and my search arguments are {a,b,e,f}  - then lacking an index on {a,b,e,f}, I will pick the index on {a,b,c,d}, but simply do a leaf scan of all the leaf pages on {a,b}.    This is worse with compression as each node will have to be a datapage fetch and a decompression of the values for {e,f} minimally as well as columns being materialized....for a lot of rows/columns (see tablescan example earlier).  An index on {a,b,e,f} as we both know would be a lot better - yes - I may still to a leaf scan (assuming non-unique index) - but for a much more limited set.   This drastically reduces the amount of data being uncompressed.   In addition, since {e,f} are index keys, I don't need to uncompress those values (assuming uncompressed index), so I only need to uncompress columns being materialized.

Believe it or not - I have seen a TON of systems with that situation in which the query on {a,b,e,f} was actually run many many times per minute.....and no one wanted to add an index for the exact arguments you made - and assumption that the index on {a,b,c,d} was adequate (wrong!).

We also need to keep in mind that compression never is really about performance - it is about TCO - reducing storage costs specifically......and whenever you try to do something cheaply, often performance takes a hit somewhere.   However, you can minimize this to something more acceptable with appropriate indexing and by not compressing tables that shouldn't be - often without impacting the overall TCO aspect measurably.  In addition, the LIO costs are sometimes offset by the PIO gains.  In your case not.....   But then when you add in dev, test, QA & etc (I have been at places where there were 10+ copies for dev)....then compression might pay off again as the aggregated storage costs becomes more burdensome than 20% slower reports.

ASE and options are not priced on cpu THREAD - it does have cpu CORE pricing - but a CORE can have 2, 4 or 8 threads (depending on CPU vendor).   I also think the pricing is based on a core factor....but I am not an expert in this area as it is more a Solution Management function.   Best bet to get the straight story would be to shoot an email to  Rudi Leibbrandt <rudi.leibbrandt@sap.com> if you are really curious.   There also are differences between the old SY pricing and SAP prices - one of which (I believe) is that SAP doesn't charge for Dev/Test licenses - but check with Rudi to be sure.