Tuesday, February 23, 2010

Don't be greedy in T-SQL queries

I am working on improving the performance of some stored procedures.  The queries all make use of index seeks and index scans to get to the data, so, nothing I can improve there.
The procs call other procs which call some more procs.  A join to a large table (+/-2million records) gets repeated in several of these procs.

So, I changed the deepest nested proc to return all the data which will be needed by the other procs.  I removed the repeated join; and the performance problem is solved.

So, two very important tips for performance tuning queries:

  1. Only return the records you are interested in; and
  2. Only touch a record once if possible.

No comments: