Please help me to explain and understand performance cases described below. Hope this will be lesson learned for me and others. I tried to improve time execution of query and I found interesting outcome, worth to share it. 4 questions from me are marked inside text.
We see that required steps are:
QUESTION 1: Does it mean that values in primary index are always sorted according to key sequence (I think it is from primary key definition)?
Total costs does not look so bad, but the duration 9 seconds was something I tried to improve. So I created secondary index, dedicated for query fields MANDT, PERNR, BEGDA, ENDDA so the WHERE in query matches exactly. Below are results.
Statistics about indexes and data distribution for PA2002 table:
QUESTION 2: If there are two or more "<=" or ">=" comparisons in query, index can use only one field (as BEGDA in case above), right? Same effect would be if Z01 index contained only MANDT, PERNR and BEGDA? What is best index proposal?
QUESTION 3: As we see final general estimation is 3, estimated rows count 4, same as in first case. However time is reduced much (almost 3 times less). I read that we should not compare estimations to real duration, but if we try, this execution plan looks worse but gives better result. Why it is so? Does the index improves reading so much?
If we assume that index speeds up query execution so much, I wonder why optimizer is not choosing same execution plan for other table, same query and same conditions (only amount of rows is much less):
QUESTION 4: Why optimizer does not choose secondary key Z01 which matches better WHERE condition for PA2001, and it chose Z01 for PA2002 table?
I have one guess:
- PA2001 has 6 793 233 rows, divided by number of distinct persons PERNR 30 777 gives 221 rows in average for person. Filtering by BEGDA and ENDDA date is assumed to be faster than final sorting when Z01 key would be used (using primary key does not require 3rd step SORT).
- PA2002 has much more rows - 62 126 700, divided by number of distinct persons PERNR 39 598 gives 1 569 rows in average for person. That is why optimizer assumes it is faster to search by index and then sort it finally.
I wonder if using index Z01 would be give faster execution for PA2001 as well, but I cannot check it as this is SAP standard code so I cannot add hint to query.
Thank you in advance for any hints and possible improvements suggestions.