From Nested Subqueries to Joins

Subqueries, temp tables, and cursors are some of the powerful tools that let SQL behave like a control-of-flow programming language. But if you use these tools, you often pay a price in decreased performance. In some cases, you can improve the performance of a stored procedure that uses subqueries by rewriting the stored procedure to use a join instead. For example, one of our clients sells furniture. Here’s a description of a table called Items that a stored procedure references:

• IDN—int data type; the identity column and the primary key.
• ItemName—varchar(50) data type; the name of the piece of furniture or component sold.
• Price—money data type; the selling price of the piece of furniture or component.
• MSRP—money data type; the MSRP of the piece of furniture or component.
• RailIDN—int data type; foreign key to ITEMS(IDN). If the current row is a bed mattress, this column will contain the IDN of the corresponding bed rails.
• BoxSpringIDN—int data type; foreign key to ITEMS(IDN). If the current row is a bed mattress, this column will contain the IDN of the corresponding box spring.

The table contains an identity column called IDN and the ItemName, Price, and manufacturer’s suggested retail price (MSRP) for each item. If the item is a mattress, the table will contain corresponding IDN’s for the bed rails (RailIDN) and the box spring (BoxSpringIDN) that the company sells with the mattress.

Let’s say that you need to create a stored procedure that supplies the variable @IDN, which is the IDN number of the mattress, from the application. The procedure needs to return the price and MSRP of the mattress; the IDN, price, and MSRP of the bed rails; the IDN, price, and MSRP of the box spring; and the ItemName of the mattress. You could use subqueries to write a stored procedure, as Listing 1 shows. (The 9022.zip file contains a code file, 9022 code.sql, which you can use to create this stored procedure and the others in this article.) This example is a set of correlated subqueries. Using subqueries works, but this method tells SQL Server to process the inner query for each row in the outer query, which is a slow process.

Let’s recreate all the subqueries as a self-referencing join. First, change the inner queries to a two-table join by using only the alaised Items table, as Listing 2 demonstrates. Next, eliminate the four remaining subqueries by using a three-table join. Note that this code, which Listing 3 shows, will return a row only if the @IDN passed is actually a mattress, meaning that the RailIDN and BoxSpringIDN of the item aren't null. The query in Listing 3 creates three virtual tables. Table one being is the record for the bed rails, table three containing contains the record for the box spring, and table two containing contains the record for the mattress. The third method, which Listing 3 shows, uses only joins and is about twice as fast as the first method, which uses correlated subqueries.

You can’t translate all subqueries into joins, but I suggest that you look through your stored procedures for good candidates. Processor time is a terrible thing to waste.