SQL Optimisation Tip
There is a saying 'There are many ways to skin a cat' this is rarely as true as it is with optimising SQL.
However, I have a tip for you for SQL Server. You can specify that you wish to do a 'Dirty Read'. This means that you want the select to go ahead without worrying if there are locks on the table / row because of inserts, updates, and deletes. So if a row that fits your criteria is in the process of being deleted it will still end up in your results.
So obviously this is not a solution for every query but it can really relieve some pressure if you do not mind the draw backs.
SELECT * FROM myTable WITH (NOLOCK)
It will also work when you do joins but you must include the
WITH (NOLOCK) command after every table declaration.
By Simon Baynes
SELECT * FROM myTable a WITH (NOLOCK) INNER JOIN myOtherTable b WITH (NOLOCK) ON a.ID = b.ID