email article printer friendly

Speed Up SQL Server Apps
Learn 10 useful tips and tricks that help boost SQL Server application performance—and streamline development and deployment as well.
by Roman Rehak

Posted May 3, 2004

Technology Toolbox: SQL Server 2000, ADO, ADO.NET

Developers love database programming tips, tricks, and workarounds—especially those that slash development time or boost application performance. Here's a collection of such nuggets for developing SQL Server applications.

1) Replace COUNT(*) With EXISTS When Checking for Existence
Developers often use the value from the COUNT(*) function when enforcing business rules in Transact-SQL code. However, try the EXIST clause instead if you're using the COUNT(*) value only to evaluate whether you have at least one row that meets certain conditions. For example, consider this code from the Northwind database:

IF (SELECT COUNT(*) FROM Orders 
   WHERE ShipVia = 3) > 0
PRINT 'You cannot delete this shipper'

The execution plan shows that SQL Server has to read all 255 rows in the Orders table before evaluating the IF expression. You can achieve the same result more efficiently with EXISTS because the IF condition evaluates to true as soon as SQL Server finds the first occurrence of 3 in the ShipVia column:

IF EXISTS (SELECT * FROM Orders 
   WHERE ShipVia = 3)
PRINT 'You cannot delete this shipper'

The difference in total execution time isn't much in a sample database such as Northwind, but use this efficient query against an Orders table with millions of rows and you'll see a major speed improvement.

2) Be Careful When Using WHERE IN and WHERE NOT IN
SQL Server doesn't always choose an optimal execution plan when you have a substantial list of values in the WHERE IN clause. Using WHERE IN and WHERE NOT IN clauses in T-SQL code can produce an execution plan involving one or more nested loops. This increases the number of comparisons SQL Server must perform exponentially. Use the WHERE IN clause only if you have a short list of values you need to evaluate:

USE Northwind
   --This query takes 9 ms to execute
SELECT * 
   FROM Customers 
   WHERE CustomerID NOT IN 
   (SELECT CustomerID FROM Orders)

Replace the WHERE IN clause with OUTER JOIN if you're using a subquery to generate a potentially large list. Doing so can improve performance significantly:

USE Northwind
    --This query takes 3 ms to execute
SELECT c.*
   FROM Customers c
   LEFT OUTER JOIN Orders o
   ON o.CustomerID = c.CustomerID
   WHERE o.CustomerID IS NULL

In this case, the second query uses LEFT OUTER JOIN, producing an execution plan that lets it run about three times faster than the first query.

The LEFT OUTER JOIN selects all rows from the Customer table—whether or not a customer placed any orders—and joins them with the Orders table. Then the WHERE clause filters out the rows where the columns from the Orders table have NULL values. Either way, you get a list of customers who placed no orders, but the second way gives SQL Server a lot less work to do. I rewrote a query recently using this technique, and the execution time went from 50 seconds to about 500 ms.