SQL Server Best Practices – Series 3

SQL Server Best Practices – Series 3

1.4 SELECT Queries

1.4.1 Joins

Always use ANSI join syntax instead of the old Transact-SQL style. This is faster to execute, does not have problems with ambiguity, and is directly compatible with Oracle.

Purely as a matter of style, try not to use a right outer join when a left outer join will do just as well.

1.4.2 Indexes

Make sure that all columns which are joined on or searched on (or at least the most important ones) have an index defined on them. It’s quite amazing how many people don’t do this, then find that their finely-crafted super-efficient query takes hours to run.

1.4.3 DISTINCT and UNION

Although these keywords have their place, and can sometimes be useful, they require a huge amount of processing which cannot be optimised in any way. There is usually a better way to write the query which avoids the use of these keywords, and you will notice the speed improvement immediately.

Be aware that UNION, by its very nature, involves removing identical rows from the resultset. This means that if you want to merge a DISTINCT query with another one using UNION, then the DISTINCT keyword can be removed.

By contrast, the UNION ALL operator has no performance penalty (since it simply adds the two record sets together without any processing).

1.4.4 Grouping and Sub-Queries

Knowing when to use these is a skill born out of experience. Sub-queries can usually be expressed more efficiently as joins, and grouping can often be done better using sub-queries.

Be verycareful when using the IN keyword to select or match from a list of values. If possible re-structure the SQL to resolve the query across the tables, for example:

SELECT * FROM dbo.EmployeeDetail WHERE EmployeeID IN (SELECT ID
FROM dbo.Employee
WHERE dbo.Employee.ID = dbo.EmployeeDetail.EmployeeID
AND dbo.Employee.FirstName LIKE ‘Bhagwan%’)

Same SQL, clearer and vastly more efficient:

SELECT * FROM dbo.Employee E INNER JOIN dbo.EmployeeDetail ED
ON (E.ID=ED.EmployeeID and E.FirstName LIKE ‘Bhagwan%’)

On large data volumes, this reduces the number of logical and physical reads by a factor of several thousand.

The most efficient form of sub-query is the EXISTS function, so code it like that if possible.

1.4.5 LIKE

Use of wild cards (‘%’ in SQL Server) in search criteria should be avoided whenever possible.

The only time a query should do a wild card match is if a user specifically supplies one in a search string. Even then, the interface must prompt the user and advise that a wild card may significantly slow the search. The point being that users should be discouraged from getting into the habit of doing wild card searches as they can cause serious performance degradation.

All search strings supplied by the user must be checked for the presence of “%”, for example:

If (CharIndex(‘%’,@sSearch) > 0)

SET @sSQL = @sSQL + ‘ AND FirstName LIKE ‘ + ””+ @sSearch +””

Else

SET @sSQL = @sSQL + ‘ AND FirstName = ‘ + ”” + @sSearch + ””

1.4.6 OR

Try to avoid OR expressions in queries because these may force SQL to do a table scan even if there is an index on that column. This may or may not be an issue for a particular query because the Query Optimiser in SQL Server 2005 and above can sometimes use the index anyway. Check your query against the execution plan to make sure, and please comment it to prevent another developer from “correcting your mistake”.

Regards

Bhagwan Singh Jatav

Like us on FaceBook  |  Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook