qr code c#.net generator sdk FIGURE 9-11 Execution plan for the stored procedure GetNextPage in C#.NET

Generate QR Code in C#.NET FIGURE 9-11 Execution plan for the stored procedure GetNextPage

FIGURE 9-11 Execution plan for the stored procedure GetNextPage
Creating QR Code JIS X 0510 In Visual C#
Using Barcode generator for .NET Control to generate, create QR Code image in .NET framework applications.
www.OnBarcode.com
QR Code Recognizer In Visual C#
Using Barcode reader for .NET framework Control to read, scan read, scan image in .NET applications.
www.OnBarcode.com
You will see a single seek operation within the clustered index to fetch the anchor row, followed by an ordered scan within the covering index to fetch the next page of rows. That s not a very ef cient plan. Ideally, the optimizer would have performed a seek within the covering index to the rst row from the desired page of orders, then it would have followed with a partial ordered scan to grab the rest of the rows in the desired page of orders, physically accessing only the relevant rows. The reason for getting an inef cient plan is because the lter has an OR operator between the expression O.orderdate > A.orderdate and the expression O.orderdate = A.orderdate AND O.orderid > A.orderid. SQL Server s optimizer tends to produce better plans for predicates that use AND logic instead of OR logic for reasons that I ll describe later in the chapter under the section Logical Transformations. For our GetNextPage procedure, here s the optimized implementation that transforms the OR logic to AND logic:
Barcode Drawer In C#
Using Barcode maker for .NET framework Control to generate, create barcode image in .NET framework applications.
www.OnBarcode.com
Barcode Recognizer In Visual C#
Using Barcode scanner for .NET Control to read, scan read, scan image in VS .NET applications.
www.OnBarcode.com
ALTER PROC dbo.GetNextPage @anchor AS INT, -- key of last row in prev page @n AS INT = 10 AS SELECT TOP (@n) O.orderid, O.orderdate, O.custid, O.empid
Encode QR Code In VS .NET
Using Barcode encoder for ASP.NET Control to generate, create Denso QR Bar Code image in ASP.NET applications.
www.OnBarcode.com
Quick Response Code Printer In .NET
Using Barcode drawer for VS .NET Control to generate, create QR image in VS .NET applications.
www.OnBarcode.com
9
Paint Quick Response Code In Visual Basic .NET
Using Barcode maker for .NET framework Control to generate, create QR Code image in Visual Studio .NET applications.
www.OnBarcode.com
Print QR Code ISO/IEC18004 In C#.NET
Using Barcode generation for VS .NET Control to generate, create QR Code image in .NET framework applications.
www.OnBarcode.com
FROM Sales.Orders AS O JOIN Sales.Orders AS A ON A.orderid = @anchor AND (O.orderdate >= A.orderdate AND (O.orderdate > A.orderdate OR O.orderid > A.orderid)) ORDER BY O.orderdate, O.orderid; GO
Print Code39 In C#
Using Barcode creator for VS .NET Control to generate, create Code-39 image in VS .NET applications.
www.OnBarcode.com
Code 128 Printer In Visual C#
Using Barcode maker for .NET Control to generate, create Code 128A image in Visual Studio .NET applications.
www.OnBarcode.com
TOP and APPLY
Barcode Maker In Visual C#.NET
Using Barcode maker for .NET framework Control to generate, create bar code image in VS .NET applications.
www.OnBarcode.com
International Standard Book Number Generator In C#.NET
Using Barcode drawer for .NET framework Control to generate, create ISBN - 13 image in .NET framework applications.
www.OnBarcode.com
Notice that the AND expression within the parentheses is logically equivalent to the previous OR expression. (I just implemented the techniques described in the section Logical Transformations later in the chapter.) To show that the AND implementation is really optimized better, run the following code and examine the execution plan shown in Figure 9-12:
Creating UPC-A Supplement 2 In None
Using Barcode generator for Font Control to generate, create UPC A image in Font applications.
www.OnBarcode.com
UPC-A Supplement 2 Reader In Visual Studio .NET
Using Barcode decoder for VS .NET Control to read, scan read, scan image in VS .NET applications.
www.OnBarcode.com
EXEC dbo.GetNextPage @anchor = 10257;
Recognizing Barcode In VS .NET
Using Barcode decoder for .NET framework Control to read, scan read, scan image in VS .NET applications.
www.OnBarcode.com
Draw ECC200 In Visual Basic .NET
Using Barcode drawer for .NET Control to generate, create DataMatrix image in .NET applications.
www.OnBarcode.com
FIGURE 9-12 Execution plan for the stored procedure GetNextPage second version
Make 1D Barcode In VB.NET
Using Barcode maker for VS .NET Control to generate, create 1D image in Visual Studio .NET applications.
www.OnBarcode.com
Print GS1 - 13 In Visual Studio .NET
Using Barcode encoder for VS .NET Control to generate, create GTIN - 13 image in Visual Studio .NET applications.
www.OnBarcode.com
Now you get the desired plan. You see a single seek operation within the clustered index to fetch the anchor row, followed by a seek within the covering index and a partial ordered scan, physically accessing only the relevant rows in the desired page of orders.
Painting Code 128 In Java
Using Barcode printer for Java Control to generate, create Code 128C image in Java applications.
www.OnBarcode.com
Bar Code Creation In None
Using Barcode creator for Font Control to generate, create bar code image in Font applications.
www.OnBarcode.com
Previous Page
You can use two approaches to dealing with requests for previous pages. One is to locally cache pages already retrieved to the client. This means that you need to develop a caching mechanism in the client. A simpler approach is to implement another stored procedure that works like the GetNextPage procedure in reverse. The anchor parameter will be the key of the rst row after the page you want. The comparisons within the procedure will use < instead of >, and the TOP clause will use an ORDER BY list that de nes the opposite sorting direction. If these were the only changes, you would get the correct page but in reverse order from normal. To x the ordering of the result set, encapsulate the query as a derived table and apply SELECT . . . ORDER BY to this derived table, with the desired ordering. Here s the implementation of the GetPrevPage procedure:
CREATE PROC dbo.GetPrevPage @anchor AS INT, -- key of first row in next page @n AS INT = 10 AS SELECT orderid, orderdate, custid, empid
Inside Microsoft SQL Server 2008: T-SQL Querying
FROM (SELECT TOP (@n) O.orderid, O.orderdate, O.custid, O.empid FROM Sales.Orders AS O JOIN Sales.Orders AS A ON A.orderid = @anchor AND (O.orderdate <= A.orderdate AND (O.orderdate < A.orderdate OR O.orderid < A.orderid)) ORDER BY O.orderdate DESC, O.orderid DESC) AS D ORDER BY orderdate, orderid; GO
To test the procedure, run it with orderid values from the rst rows on the pages you already got:
EXEC dbo.GetPrevPage @anchor = 10268; EXEC dbo.GetPrevPage @anchor = 10258;
Examine the execution plan shown in Figure 9-13, produced for the execution of the GetPrevPage procedure.
Copyright © OnBarcode.com . All rights reserved.