Seriously, a SQL Index I created scared me. Here’s the story – I’m working on a project at work, and this table that is used to collect information has about 100K records in it – basically 1 record per day with 450 unique combinations. And, not to mention our testing data is from Sept. of ‘04 – so the live database has about 130K of records.

So, anyways – I was creating a stored procedure that retrieves those 450 unique records, and have 2 sub-queries that grabs info from the “large” table. Talk about slow, right? No indexes on it, so I created 2 different indexes for columns that I use, and that sped it up from many minutes (like 10) to 9 seconds. Ohh, but thats still not fast enough – so I turned to the SQL Performance Monitor and Index Tuning Wizard (that which rarely results in providing any good information).

But this time it was different, yes, this time it suggested I create a new index that combined the 2 columns into 1 index. So, I did – reran the query…

And with a flash of the screen, and a curddling scream like a girl, I had my data…yes, it was under 1 second, and was more like a few milli-seconds. So, my word of advice, index…index…index – but only if you have to. You’d be amazed with the speed that indexing can do. Now, back to the world I know well, ASP & ASP.NET development. I think I’ll leave the DBA stuff to our Data Architect from now on.