Tips and Tricks about query optimization [SQL Server 2005]?

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.

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

In management studio, run the command set showplan_all on then run your query. They query will not run, but the execution plan will be dumped. Look in this output for the word scan this is where an index is not being used.

– KM. Jan 26 '10 at 16:40.

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.

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.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.