SQL Server Transact-SQL General Tips Part II

By : Brad McGehee

Instead of using temporary tables, consider using a derived table instead. A derived table is the result of using a SELECT statement in the FROM clause of an existing SELECT statement. By using derived tables instead of temporary tables, you can reduce I/O and often boost your application's performance. [7.0, 2000, 2005] Updated 6-12-2006

*****

Sometimes, it is handy to be able to perform some calculation on one or more columns of a record, and then take the result of that calculation and then add it to similar calculations performed on other related records to find a grand total.

For example, let's say you want to find the total dollar cost of an invoice. An invoice will generally involve a header record and one or more detail records. Each detail record will represent a line item on the invoice. In order to calculate the total dollar cost of an invoice, based on two or more line items, you would need to multiply the quantity of each item sold times the price of each item. Then, you would need to add the total price of each line item together in order to get the total dollar cost of the entire invoice. To keep this example simple, let's ignore things like discounts, taxes, shipping, etc.

One way to accomplish this task would be to use a cursor; like we see below (we are using the Northwind database for this example code):

DECLARE @LineTotal money --Declare variables
DECLARE @InvoiceTotal money
SET @LineTotal = 0 --Set variables to 0
SET @InvoiceTotal = 0

DECLARE Line_Item_Cursor CURSOR FOR --Declare the cursor

SELECT UnitPrice*Quantity --Multiply unit price times quantity ordered
FROM [order details]
WHERE orderid = 10248 --We are only concerned with invoice 10248

OPEN Line_Item_Cursor --Open the cursor
FETCH NEXT FROM Line_Item_Cursor INTO @LineTotal --Fetch next record
WHILE @@FETCH_STATUS = 0

BEGIN
SET @InvoiceTotal = @InvoiceTotal + @LineTotal --Summarize line items
FETCH NEXT FROM Line_Item_Cursor INTO @LineTotal
END

CLOSE Line_Item_Cursor --Close cursor
DEALLOCATE Line_Item_Cursor --Deallocate cursor
SELECT @InvoiceTotal InvoiceTotal --Display total value of invoice

The result for invoice number 10248 is $440.00.

What the cursor does is to select all of the line items for invoice number 10248, then multiply the quantity ordered times the price to get a line item total, and then it takes each of the line item totals for each record and then adds them all up in order to calculate the total dollar amount for the invoice.

This all works well, but the code is long and hard to read, and performance is not great because a cursor is used. Ideally, for best performance, we need to find another way to accomplish the same goal as above, but without using a cursor.

Instead of using a cursor, let's rewrite the above code using set-based Transact-SQL instead of a cursor. Here's what the code looks like:

DECLARE @InvoiceTotal money
SELECT @InvoiceTotal = sum(UnitPrice*Quantity)
FROM [order details]
WHERE orderid = 10248
SELECT @InvoiceTotal InvoiceTotal

The result for invoice number 10248 is $440.00.

Right away, it is obvious that this is a lot less code and that is it more readable. What may not be obvious is that it uses less server resources and performs faster. In our example--with few rows--the time difference is very small, but if many rows are involved, the time difference between the techniques can be substantial.

The secret here is to use the Transact-SQL "sum" function to summarize the line item totals for you, instead of relying on a cursor. You can use this same technique to help reduce your dependency on using resource-hogging cursors in much of your Transact-SQL code. [6.5, 7.0, 2000, 2005] Updated 6-12-2006

*****

While views are often convenient to use, especially for restricting users from seeing data they should not see, they aren't always good for performance. So if database performance is your goal, avoid using views (SQL Server 2000/2005 Indexed Views are another story).

When the Query Optimizer gets a request to run a view, it runs it just as if you had run the view's SELECT statement from the Query Analyzer or Management Studio. If fact, a view runs slightly slower than the same SELECT statement run from the Query Analyzer or Management Studio--but you probably would not notice the small difference--as it is small in simple examples like this.

Another issue with views is that they are often combined (nested) with other code, such as being embedded within another view, a stored procedure, or other T-SQL script. Doing so often makes it more difficult to identify potential performance issues.

Views don't allow you to add more restrictive WHERE clauses as needed. In other words, they can't accept input parameters, which are often needed to restrict the amount of records returned. I have seen lazy developers write generic views that return hundreds of thousands of unnecessary rows, and then user other code, such as client code, to filter only those few records that are needed. This is a great waste of SQL Server's resources.

Instead of embedding SELECT statements in a view, put them in a stored procedure for optimum performance. Not only do you get an added performance boost (in many cases), you can also use the stored procedure to restrict user access to table columns, just as you can with views. [6.5, 7.0, 2000, 2005] Updated 6-12-2006