Monday, July 08, 2013

Use of Case Statement in Index Creation

I was working on program and needed to access only Credit Memo applications to Invoices and Debit Memos. Query was something like

In this query there was a join with many other tables and query was not performing well. Then i remembered recommendations of Lee Gunderson about usage of case statement in indexes. So i created following index and query worked very well

After creating the above index, i rewrote the query as given below and performance improved immediately.

No comments:

Post a Comment