Indexes, Indexes, Indexes

Around 2:30 this afternoon one of our users comes by the Data area.

"PARS is running slow."

This is no surprise. PARS has been randomly performing mediocre to tolerable for most of my two years.

"I go get a cup of coffee and it finally times out just as I get back."

Uh-oh. Slow is annoying. A timeout means the system is down. First we check with the other users. Nope, nothing unusual here. Hmmm. This user performs a search. We emulate their exact criteria. Boom - PARS times out here too!

Okay, the search function calls a stored procedure that dynamically generates an SQL query. I copy out the relevant parts and build the query by hand. It takes 35 seconds and returns a dozen rows. First, I take out the date check. Presto, the query runs in 6 seconds.

Let's see what effect other criteria have. Comment out the client check - 6 seconds. Odd, I would have expected it was just the date search. A little more playing and it becomes clear that three search clauses take 35 seconds. Two only take 6 seconds.

Now I examine the execution plans. Aha! A clue! Two criteria go into a bitmap index. Three criteria do not have the bitmap. What happens if I index the three search fields? We use this query a lot. Relatively, we upload minute amounts of data. So an index won't affect load performance. What do I have to lose?

Well, how long does building the index take? Am I going to bring down the database for 2 hours? It's down now, so again, no harm. Turns out that I was being stupid. SQL Server built the index in like 2 seconds.

I hit the query again. It still takes 35 seconds. I found that very surprising. The execution plan put a lot of time into looking up those fields. The second worst spot was a sub-select. The sub-select checks if we have any records for this row in another table. Actually, I'm looking for the case where we don't have corresponding records. The second table has a foreign key that points back to this table.

No one ever added indices to the database. SQL Server does not automatically index foreign keys either. So for kicks, I indexed it. Pow! The query displays results immediately. I couldn't believe it. I usually hit the button, sit back, and watch the timer for when they finish. This sucker ran instantaneously! I was dumb-founded. My user was happy to get back to work.

What did I learn from this experience? First, I was an idiot for waiting this long. I had noticed that we had no indices two years ago. Performance wasn't that bad. So other priorities moved ahead.

Secondly, a well placed index can breathe new life into sluggish queries. I know, I know, a million DBA's just slapped their palms against their foreheads and exclaimed "duh!". Seriously, though, this was on my radar because, well, it might shave off a few seconds. Going from timeout to instant response, though. I had no idea the effect was so dramatic. Seeing was believing.

So go ahead and chuckle. I deserve it. And now it's time that we look at our other tables. I may be able to eek even more performance out of this system!