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
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