Blog ala Vidar

Have you ever though of tuning your car? Yeah, why not?
It makes your car run faster and use less fuel. Well, for starters it’s a bit expensive. But, tuning SQL is free, and it’s even more fun than tuning your car. Tuning your SQL means your queries run faster and use less fuel (CPU, memory etc)!

I’m at Conocophillips Indonesia at the moment upgrading to SQL Server 2005. I’ve used a couple of days to tune different queries, and at the most I got a query to run from an average of 4 minutes, to about 10 seconds, with basically the same code! Do NOT tell me that this was a temporary solution, because my solution was even easier! 🙂

When writing SQL code, keep these simple rules in mind:
Do not use sub-queries unless it’s ABSOLUTELY necessary
If you are setting several variables from the values of fields in tables/views, use one SELECT statement instead of three SET statements.

For example, do not use:

SET @Variable1 = 'Test'
SET @Variable2 = (SELECT Test FROM Test1 WHERE Test3='asdf')
SET @Variable3 = (SELECT Test2 FROM Test1 WHERE Test3='asdf')

Use:

SELECT TOP 1
  @Variable1 = 'Test',
  @Variable2 = Test,
  @Variable3 = Test2
    FROM Test1
    WHERE Test3 = 'asdf'

You can also use SELECT instead of SET to set static values
If possible, do not use CASE or UNION (ALL)
Use EXISTS, instead of 0 < (SELECT COUNT(*) something)
Use TOP 1. Always remember that a sub-query MAY return more than one record. Your query might then fail if you’re using Field = (SELECT SubField FROM subtable).

These rules are things that every developer should keep in mind when writing SQL code.

Of course, there might be places where you’ve followed every rule, but it still takes minutes to run. Then you might want to see the execution-plan. There are a couple of major things too look for here. For example, Scans (Index Scan, Clustered Index Scan, Table Scan etc) is NOT good. Seeks are a good thing.

You might also want to see what’s really happening when you’re running your query. To turn on execution-plan in Server Management Studio, press ctrl + M and rerun your query. It will appear as a new tab beside Results and Messages.

And, as always; Profiler might be a good tool. If you let it run for a couple of hours you will discover queries which take longer time than others. For example, you can set a filter on the Reads field, for >= 50000.

Do you have any other simple roules you’d like to share with us? Please mail them to me, and I’ll publish them here!