qr code generator c# mvc Techniques to Improve Query Performance in C#.NET

Encoder QR Code 2d barcode in C#.NET Techniques to Improve Query Performance

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 = 1;

Record the total cost of Query 1. (You can find the value in the Execution Plan tab by moving the pointer over the SELECT operator and locating the value named Estimated Subtree Cost.)

In the existing query window, type, highlight, and execute Query 2, shown here, to test the performance of a query using OUTER APPLY and a large result set:

-- 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 ) AS o WHERE c.TerritoryID = 1;

In the existing query window, type, highlight, and execute Query 3, shown here, to test the performance of a query that uses ROW_NUMBER and a large result set:

-- 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 = 1;

1. 2.

Open SSMS, if necessary, and connect to the appropriate instance of SQL Server 2008. In a new query window, type and execute the following SQL statement to connect to the TestDB database created in Exercise 1:

USE TestDB;

3. 4.

Turn on the Actual Execution Plan feature in SSMS by pressing Ctrl+M or by selecting Include Actual Execution Plan from the Query menu. In the existing query window, type, highlight, and execute the new version of Query 3, shown here, with the small result set (TerritoryID = 2). The changes from the previous version of the query are shown in bold type:

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 WHERE c.TerritoryID = 2 )

SELECT a.CustomerID ,a.AccountNumber ,a.SalesOrderID ,a.OrderDate FROM a WHERE a.RowNo = 1;

In the existing query window, type, highlight, and execute the new version of Query 3, shown here, with the large result set (TerritoryID = 1). The difference from the previous version of the query is again shown in bold type:

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 WHERE c.TerritoryID = 1 ) SELECT a.CustomerID ,a.AccountNumber ,a.SalesOrderID ,a.OrderDate FROM a WHERE a.RowNo = 1;

To clean up after this practice, close all open query windows in SSMS, open a new query window, and execute the following SQL statements:

USE master; DROP DATABASE TestDB;

SQL Server 2008 supports two basic types of indexes: clustered and nonclustered. Both indexes are implemented as a balanced tree, where the leaf level is the bottom level of the structure. The difference between these index types is that the clustered index is the actual table; that is, the bottom level of a clustered index contains the actual rows, including all columns, of the table. A nonclustered index, on the other hand, contains only the columns included in the index s key, plus a pointer pointing to the actual data row. If a table does not have a clustered index defined on it, it is called a heap, or an unsorted table. You could also say that a table can have one of two forms: It is either a heap (unsorted) or a clustered index (sorted).

After this lesson, you will be able to: