posted on Friday, December 16, 2005 3:53 PM by Jonathan Hodgson

SQL tuning using set based thinking

UK SQL Server community site SqlServerFAQ has a slidedesk from one of Itzik Ben-Gan of SolidQualityLearning presentations available here.

Sometimes query optimisation can be a bit of a black art, but he shows us show to use set-based thinking to optimise an sql query to reduce time and I/O.

This is example query:

"Given an Employees table with 9 employees and an Orders table with 1,660,000 orders, return active employees (those who made orders) who did not make any orders after April 1998"

So he starts off with a cursor-based solution:





(Source: Solid Quality Learning presentation by Itzik Ben-Gan)

Quite a big difference in performance!

Another good source on tuning is the seven showplan red flags article which describes potential issues to watch out for in Sql Server Query Analyzer when looking at the query plan. Also worth a read is Ken England's Microsoft Sql Server 2000 Performance Optimization and Tuning Handbook.

Comments

# re: SQL tuning using set based thinking

Wednesday, January 04, 2006 9:24 AM by Andrew
Had a read of the showplan red flags article above. Thought I'd just mention a problem I experiecned the other day whilst trying to improve a queries performance. I found that for some reason the query optimizer was using a hash match to resolve an inner join between two tables with a standard primary foriegn key relationship. I found this article
http://support.microsoft.com/default.aspx?scid=kb;en-us;328187
when googling for a solution to the problem. After discovering the development server I was using does not have service pack 3 installed I added the hint option to my query and managed to half the execution time of my query and get rid of the hash match.