Index Hints

While modifying a function one day. I noticed that a SELECT statement was running slow.
Looking at the execution plan, I found that 90% of the performance was consumed by one Key Lookup on RPT_ACTION_TEST.
After reviewing the function I saw this.

I reviewed the index that is used and it does not include the STATUS_DATE or ACTUAL_DATET. That is why you should not use the WITH statement to force an index. I had already created an index that would work great with this but it won’t get used. IDX_RECORD_STAGE includes everything the query would need to be freaky fast.

By removing the WITH hint, now it is able to use the IDX_RECORD_STAGE index.
This change increased the speed and it no longer needs the Key Lookup.
Execution time went from 20 seconds to 3 seconds.

Before using Index scan and Key lookup:


After using Index Seek:


Please read this article for more information.

Leave a Reply

Your email address will not be published. Required fields are marked *