Blog ala Vidar

When doing query optimization, there is a couple of things to remember:

NEVER USE “OR”!
This KILLS performance! Instead of using OR, use UNION (ALL) or IN

UNION vs UNION ALL
The performance difference between these are big. UNION first groups and then orders. UNION ALL just adds the resultsets together

EXISTS vs IN
In many cases, you can use:

SELECT *
	FROM table1
	WHERE Login IN (SELECT Login
				FROM table2)

instead of using:

SELECT *
	FROM table1
	WHERE EXISTS (SELECT *
			FROM table2
			WHERE table2.Login = table1.Login)

Subselects vs JOIN
In some cases, like in the queryoptimizing webcast, subselects are MUCH quicker than using JOIN. This is not the case in all queries, so test both!

WITH (NOLOCK)
When you use NOLOCK, you read uncommited data from the table. This means that there MIGHT be data that are not written to the table yet. In most of our cases this is not a problem.

DO NOT USE *
… unless you need it. When using *, you can’t use indexes to select fields. Using a * forces the execution plan to select from the table.

Indexes and Statistics
Always make good indexes! Make sure indexes and statistics are rebuild when they need to! I’ll post an input here later on about indexes and statistics.