asp.net c# qr code generator Figure 7-11. Execution plan for the stored procedure usp_nextpage in Visual Studio .NET

Generating QR Code in Visual Studio .NET Figure 7-11. Execution plan for the stored procedure usp_nextpage

Figure 7-11. Execution plan for the stored procedure usp_nextpage
QR Code Creator In Visual Studio .NET
Using Barcode generator for VS .NET Control to generate, create Quick Response Code image in Visual Studio .NET applications.
www.OnBarcode.com
QR Code ISO/IEC18004 Decoder In .NET Framework
Using Barcode scanner for .NET framework Control to read, scan read, scan image in .NET framework applications.
www.OnBarcode.com
[View full size image]
Create Barcode In VS .NET
Using Barcode creation for .NET framework Control to generate, create bar code image in .NET framework applications.
www.OnBarcode.com
Bar Code Decoder In Visual Studio .NET
Using Barcode reader for .NET 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 efficient plan. Ideally, the optimizer would have performed a seek within the covering index to the first 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
Painting QR In Visual C#.NET
Using Barcode encoder for VS .NET Control to generate, create Denso QR Bar Code image in .NET framework applications.
www.OnBarcode.com
QR Generation In VS .NET
Using Barcode generator for ASP.NET Control to generate, create QR Code ISO/IEC18004 image in ASP.NET applications.
www.OnBarcode.com
reason for getting an inefficient plan is because the filter has an OR operator between the expression O.OrderDate > A.OrderDate, and the expression O.OrderDate = A.OrderDate AND O.OrderID > A.OrderID. See the sidebar "Logical Transformations" for details about OR optimization vs. AND optimization. Following the sidebar, I'll provide the optimized implementation of the stored procedure using AND logic.
QR Code Generation In VB.NET
Using Barcode generator for .NET Control to generate, create Denso QR Bar Code image in .NET applications.
www.OnBarcode.com
2D Barcode Printer In .NET
Using Barcode drawer for .NET Control to generate, create 2D Barcode image in .NET framework applications.
www.OnBarcode.com
Logical Transformations
Code-39 Creation In VS .NET
Using Barcode maker for VS .NET Control to generate, create Code 3 of 9 image in .NET applications.
www.OnBarcode.com
Printing Data Matrix 2d Barcode In VS .NET
Using Barcode generation for .NET framework Control to generate, create DataMatrix image in .NET applications.
www.OnBarcode.com
In several solutions I've presented, I used logical expressions with an OR operator to deal with precedence based on multiple attributes. Such was the case in the recent solutions for paging, matching current and previous occurrences, and other problems. I used OR logic because this is how human minds are accustomed to thinking. The logical expressions using OR logic are fairly intuitive for the purpose of determining precedence and identifying rows that follow a certain anchor. However, because of the way SQL Server's optimizer works, OR logic is problematic in terms of performance, especially when some of the filtered columns are not indexed. Consider for example a filter such as col1 = 5 OR col2 = 10. If you have individual indexes on col1 and col2, the optimizer can filter the rows in each index and then perform an index intersection between the two. However, if you have an index on only one of the columns, even when the filter is very selective, the index is useless. SQL Server would still need to scan the whole table to see whether rows that didn't match the first filter qualify for the second condition. On the other hand, AND logic has much better performance potential. With each expression, you narrow down the result set. Rows filtered by one index are already a superset of the rows you'll end up returning. So an index on any of the filtered columns can potentially be used to advantage. Whether or not it is worthwhile to use the existing index is a matter of selectivity, but the potential is there. For example, consider the filter col1 = 5 AND col2 = 10. The optimal index here is a composite one created on both columns. However, if you have an index on only one of them, and it's selective enough, that's sufficient already. SQL Server can filter the data through that index, and then look up the rows and examine whether they also meet the second condition. In this chapter, the logical expressions I used in my solutions used OR logic to identify rows following a given anchor. For example, say you're looking at the row with an OrderID of 11075, and you're supposed to identify the rows that follow, where precedence is based on OrderDate and OrderID is the tiebreaker. The OrderDate of the anchor is '19980506'. A query returning the rows that come after this anchor row is very selective. I used the following logic to filter these rows: [View full width]OrderDate > '19980506' OR (OrderDate = '19980506' AND OrderID > 11075)
GS1 DataBar Maker In VS .NET
Using Barcode drawer for .NET framework Control to generate, create GS1 DataBar Limited image in .NET applications.
www.OnBarcode.com
Draw Postnet 3 Of 5 In VS .NET
Using Barcode drawer for .NET Control to generate, create USPS POSTal Numeric Encoding Technique Barcode image in VS .NET applications.
www.OnBarcode.com
Say that you could afford creating only one index, on OrderDate. Such an index is not sufficient in the eyes of the optimizer to filter the relevant rows because the logical expression referring to OrderDate is followed by an OR operator, with the right side of the operator referring to other columns (OrderID, in this case). Such a filter would yield a table scan. You can perform a logical transformation here and end up with an equivalent expression that uses AND logic. Here's the transformed expression: [View full width]OrderDate >= '19980506' AND (OrderDate > '19980506' OR OrderID > 11075)
PDF-417 2d Barcode Generation In Java
Using Barcode encoder for BIRT reports Control to generate, create PDF-417 2d barcode image in Eclipse BIRT applications.
www.OnBarcode.com
Encode Code128 In Java
Using Barcode printer for Android Control to generate, create USS Code 128 image in Android applications.
www.OnBarcode.com
Instead of specifying OrderDate > '19980506', you specify OrderDate >= '19980506'. Now you can use an AND operator and request either rows where the OrderDate is greater than the anchor's OrderDate (meaning the OrderDate is not equal to the anchor's OrderDate, in which case you don't care about the value of OrderID); or the OrderID is greater than the anchor's OrderID (meaning the OrderDate is equal to the anchor's OrderDate). The logical expressions are equivalent. However, the transformed one has the form OrderDate_comparison AND other_logical_expressionmeaning that now an index on OrderDate alone can be considered. To put these words into action, first create a table called MyOrders containing the same data as the Orders table, and an index only on OrderDate: IF OBJECT_ID('dbo.MyOrders') IS NOT NULL DROP TABLE dbo.MyOrders; GO SELECT * INTO dbo.MyOrders FROM dbo.Orders CREATE INDEX idx_dt ON dbo.MyOrders(OrderDate);
Barcode Creator In Java
Using Barcode generation for BIRT Control to generate, create bar code image in BIRT reports applications.
www.OnBarcode.com
PDF 417 Creation In Java
Using Barcode creator for Java Control to generate, create PDF 417 image in Java applications.
www.OnBarcode.com
PDF 417 Maker In None
Using Barcode drawer for Online Control to generate, create PDF 417 image in Online applications.
www.OnBarcode.com
Creating USS Code 128 In Java
Using Barcode generation for BIRT reports Control to generate, create Code 128C image in Eclipse BIRT applications.
www.OnBarcode.com
Encode Barcode In Java
Using Barcode generation for BIRT reports Control to generate, create barcode image in BIRT reports applications.
www.OnBarcode.com
Generate PDF417 In .NET Framework
Using Barcode maker for ASP.NET Control to generate, create PDF417 image in ASP.NET applications.
www.OnBarcode.com
Copyright © OnBarcode.com . All rights reserved.