Indexes are often forgotten

by 8:14 AM 1 comments
We have all worked on legacy code bases.  One of the applications I work on is truly massive, occasionally some of the internal reporting tools have an issue running some of our reports, almost always it's because there is a query being looped and 1 or more queries being executed from within that loop.  Now most of the time you can re-write a query like that but sometimes it's quite alot of work and when you have a 100 things on the go and you need the report immediately we may forget about the simple things.

Now in my case most of these issues are good issues to have, it means we have huge amounts of data, which reflect on the huge amounts of users and users ( in our case ) == $

In it's most simplest form the routines worked fine for lets say 10k users, but on 300k users the performance suffers exponentially.  When I urgently have to fix an issue like this I look to the database find one of the inner queries, take a quick look and apply an appropriate index to that table.  The most recent example went from timing out after 5 mins, to taking 9 seconds.  With time I'll re-write that section of code to either appropriately use joins, or if required a storedproc with UDF's but int he mean time ~2 mins of setting up an index made my users happy.

1 comment:

  1. I'm puzzled with lots of exercises. I was afraid I could not do the right time despite my hard work. I need a support person.