Cursors in VS .NET

Paint UPC-A Supplement 5 in VS .NET Cursors

You should generally avoid using cursors because of their negative effect on performance. They have such an effect partly because each execution of a FETCH statement in a cursor loop is similar in performance cost to executing a SELECT statement that returns one row. Another problem is that a DML statement is optimized as a single unit, while a cursor loop cannot be optimized in the same way. Instead, each item in the loop will be optimized and executed separately for each iteration of the loop.

7

You should try to rewrite cursor logic into one or more set-based statements (SELECT, INSERT, UPDATE, DELETE). If you must use cursors, consider implementing the logic using a CLR stored procedure or a table-valued user-defined function instead (depending on the functionality you need).

In this lab, you will test the query performance of three different queries that should produce the same result set. The query will return all customers in a specific territory and the last order received for those customers. If the customer does not have any orders, it must still be returned. The completed lab is available in the \Labs\ 07\Lab1 folder on the companion CD.

You will need to have SQL Server 2005 and the AdventureWorks database installed before you can complete this lab. Refer to the Introduction for setup instructions.

Exercise 1: Test Using a Small Result Set In this exercise, you will execute the three queries mentioned in the lab preface and record each query s cost. In this case, the parameter supplied to all three queries (TerritoryID) will yield a small result set of 64 rows. 1. Open SQL Server Management Studio and connect to an instance of SQL Server 2005. 2. In a new query window, type and execute the following SQL statements to create the TestDB database, the Test schema, and the two tables that will be used in this exercise:

CREATE DATABASE TestDB; GO USE TestDB; GO CREATE SCHEMA Test; GO SELECT * INTO Test.SalesOrderHeader FROM AdventureWorks.Sales.SalesOrderHeader; GO SELECT * INTO Test.Customer FROM AdventureWorks.Sales.Customer; GO ALTER TABLE Test.SalesOrderHeader

ADD CONSTRAINT PKSalesOrderHeader PRIMARY KEY(SalesOrderID); GO ALTER TABLE Test.Customer ADD CONSTRAINT PKCustomer PRIMARY KEY(CustomerID);

3. Turn on the Actual Execution Plan in SQL Server Management Studio by pressing Ctrl+M, or by selecting Include Actual Execution Plan from the Query menu. 4. Type and execute Query #1 to test its performance. (Because of the use of two separate correlated subqueries in this query, it is not guaranteed that both these subqueries return data from the same row in the Test.Customer table.)

-- Query #1 SELECT c.CustomerID ,c.AccountNumber ,( SELECT TOP(1) soh.SalesOrderID FROM Test.SalesOrderHeader AS soh WHERE soh.CustomerID = c.CustomerID ORDER BY OrderDate DESC ) AS SalesOrderID ,( SELECT TOP(1) soh.OrderDate FROM Test.SalesOrderHeader AS soh WHERE soh.CustomerID = c.CustomerID ORDER BY OrderDate DESC ) AS OrderDate FROM Test.Customer AS c WHERE c.TerritoryID = 2;

What was the total cost of Query #1 (You can find the value in the Execution Plan tab by moving the mouse pointer over the SELECT operator and locating the value named Estimated Subtree Cost.) 5. Type and execute Query #2 to test its performance.

-- Query #2 SELECT c.CustomerID ,c.AccountNumber ,o.* FROM Test.Customer AS c OUTER APPLY ( SELECT TOP(1) soh.SalesOrderID, soh.OrderDate FROM Test.SalesOrderHeader AS soh WHERE soh.CustomerID = c.CustomerID ORDER BY OrderDate DESC

7

) AS o WHERE c.TerritoryID = 2;

-- Query #3 WITH a AS ( SELECT c.CustomerID ,c.AccountNumber ,c.TerritoryID ,soh.SalesOrderID ,soh.OrderDate ,ROW_NUMBER() OVER (PARTITION BY c.CustomerID ORDER BY soh.OrderDate DESC) AS RowNo FROM Test.Customer AS c LEFT OUTER JOIN Test.SalesOrderHeader AS soh ON soh.CustomerID = c.CustomerID ) SELECT a.CustomerID ,a.AccountNumber ,a.SalesOrderID ,a.OrderDate FROM a WHERE a.RowNo = 1 AND a.TerritoryID = 2;

What was the total cost of Query #3 7. To clean up after this exercise, close all open query windows in SQL Server Management Studio, open a new query window, and execute the following SQL statements:

USE master; DROP DATABASE TestDB;

Exercise 2: Test Using a Large Result Set In this exercise, you will execute the three queries mentioned in the lab preface and record each query s cost. In this case, the parameter supplied to all three queries (TerritoryID) will yield a larger result set of 3,433 rows (compared to 64 rows in the previous exercise). 1. Open SQL Server Management Studio and connect to an instance of SQL Server 2005. 2. In a new query window, type and execute the following SQL statements to create the TestDB database, the Test schema, and the two tables that will be used in this exercise: