Another monthly database extract

A client had a monthly process that ran for 6 hours.  Adding one index to one table helped three programs and reduced the runtime to 2 hours.  40+ minutes of that 2 hours was a program that chose to use the new index, but not a matching column.  Further analysis found the 4 SQLs that used the index had parentheses missing or coded incorrectly.  Two of them only caused a slow runtime.  The other 2 caused invalid data to be included in the results set.  In production....

Moral of the story:  While you are doing SQL tuning, expect to find a few bugs here & there in the SQL.

Coding the SQL correctly will reduce the 40+ minutes to less than 2 minutes, which means the one index caused 3 programs to reduce their runtime from from nearly 5 hours to less than 10 minutes. It can happen.

~~~~~