I've created a list with 23 tips to improve your SQL Server queries. Quite often these tips can also be used for other database-engines like MS Access, Oracle or MySQL.
1) Avoid non-sargable WHERE-clauses. If possible rewrite them to sargable ones
2) In the WHERE-clause use the least likely true AND expression first
3) Avoid using OR in the WHERE-clause if not all colums have an index
4) Avoid using UNION if UNION ALL also does the trick
5) Avoid using UNION of two subsets from the same table. Instead use OR in the WHERE-clause
6) Avoid using SELECT * FROM when only a few columns are needed. Try to specify each column
7) Avoid using COUNT(*) to check the existence of a record. Instead use EXIST
8) Always try to use a WHERE-clause in your query to narrow the results
9) Try to use the best performing operator as possible
10) Avoid using NOT IN. Instead use EXIST, NOT EXIST, IN or LEFT OUTER JOIN with a check for a NULL condition
11) Avoid using IN when EXISTS is also possible
12) Avoid using IN when BETWEEN is also possible
13) In case using IN try to order the list of values so that the most frequently found values are placed first
14) Avoid using SUBSTRING in the WHERE-clause. If possible use LIKE instead
15) Sometimes consider rewriting a query using a OR to multiple queries combined with a UNION ALL
16) Don't use ORDER BY if you don't really need it
17) Keep the width and/or number of sorted columns to the minimum
18) Keep the number of rows to be sorted to a minimum
19) When sorting a specific column often conside making that column a clustered index
20) In case of using HAVING try to minimize the amount of rows using a WHERE clause
21) In case using LIKE on CHAR of VARCHAR colums quite often consider using the full-text search option
22) In case using GROUP BY without an aggregate function try using DISTINCT instead
23) Avoid using variables in a WHERE clause in case the query is located in a batch-file
Thes tips can also be found on my SQL Server factsheet for developers. This factsheet can be found here
Labels: performance, SQL Server, SQL Server 2000, SQL Server 2005
Posted by Xander Zelders
0 Comments:
Post a Comment
<< Home