Database indexes are your friend

I guess I assumed that creating a foreign key constraint would automatically create a non-clustered index.  I mean, a primary key is a clustered index, why wouldn’t a foreign key also create an index?

A batch process today slowed from completing in about 4 hours to an estimated completion time of around 3 days.  Since this was a process that needed to complete daily, well obviously it should complete in less than a day.  First order of business was looking at the profiler to see what was going on.

It was exactly one SELECT statement per transaction, no joins, and one predicate in the WHERE clause, on a foreign key.  And it took an average of 23 seconds to execute.  Yikes.

Next up was checking the execution plan and statistics.  This was on a table that doubled in size in one day, to 7 million rows.  The culprit naturally was a table scan, and the 230K reads showed why the query took so long.  A coworker pointed out that the statistics plan also provided a hint for performance tuning, to add an index.

I really couldn’t believe the foreign key didn’t have an index, but sure enough, it didn’t.  Added the index on the foreign key, and the query time was reduced from 23 seconds to nearly instantaneous, 2-3 orders of magnitude better.

And that 4 hour batch process?  It finished in about 5 minutes.

Yes, database indexes are your friend.  Assuming database indexes are already in place, not your friend.

*sigh*