The benefits and penalties of database indices
(Database nerd alert, you’ve been warned)
I had to develop a quick piece of code yesterday to pull stock prices from a database. When I stepped through it by hand everything worked fine, but in production it timed out without completing.
I eventually isolated the problem, which lay in the SQL statement. It was running in SQL Server Studio in a time longer than the timeout period that governed the length of the connection to the SQL Server. In other words, the program was getting impatient when the database took its sweet time getting the data and stormed off in a huff. SQL Server Studio suggested I create an index on a field that I was using in the where clause, and when I added that, the program ran in less than a second, which was about as good as I can expect, given the size of the dataset.
Normally, adding an index would be a task taken with some hesitation, because every time a record is updated or added the index has to be updated, This would slow down the database, which is A Bad Thing. In this case, however, we do a number of record searches based on the field I indexed, so it isn’t a big deal, because the improvements outweigh the penalties.