Tips and Tricks about query optimization [SQL Server 2005]

Question

I am asking this question in stackoverflow because its the right place to ask...

I know its a very vast topic to start but some small ones which may be really handy...

It might be useful for young developers like me to know about query optimization..

Some Tips and Tricks about query optimization in SQL Server 2005..

Answer 1

The most obvious place to start if you have a slow query is to make sure it's using an index.

Answer 2

Based on questions here

• Avoid datatype precedence (eg always much like for like, including length of varchar etc)

eg

...WHERE tinyintcol = @intvalue
 means a conversion of the column and invalidates an index
...WHERE tinyintcol = @tinyintvalue

• Avoid functions on columns in WHERE clauses

eg

 ...WHERE DATEADD(day, 1, MyCol) > GETDATE()
 should be
 ...WHERE MyCol > DATEADD(day, -1, GETDATE())

• Covering indexes

• GUIDs: not clustered indexes

Answer 3

• Try to reduce your total number of joins if possible
• Consider the table sizes used in the query
• Use indexes as they are your friend
• Watch out for the types you are using as keys (int to int is a much easier comparison than two varchars)
• Avoid using 'Like' queries where possible try to get the value using an equality first

Answer 4

For queries I can add to gbn, recursive and smaclell the followings:

• try to minimize subqueries, joins
• avoid any excessing lockings, saving checkpoints
• use pragmas to set temp table creation in memory
• use pragmas to set synchronous OFF
• use pragmas to disable triggers (if possible)
• embed INSERT and DELETE queries in transactions
• sometimes UPDATE is slower for multiple records than just inserting them again. So advised is to SELECT, DELETE, than programmatically update the records and re-INSERT with the existing keys (watch out for CASCADE and TRIGGERS)
• check with your sys admins that DB cache should be ON