Figure 3-41. Clustered index seek + ordered partial scan in .NET framework

Printer QR Code in .NET framework Figure 3-41. Clustered index seek + ordered partial scan

Figure 3-41. Clustered index seek + ordered partial scan
QR Creator In .NET
Using Barcode encoder for .NET framework Control to generate, create Denso QR Bar Code image in .NET applications.
www.OnBarcode.com
Recognize QR Code 2d Barcode In .NET Framework
Using Barcode decoder for .NET Control to read, scan read, scan image in .NET framework applications.
www.OnBarcode.com
Figure 3-42. Clustered index seek + ordered partial scan (execution plan)
Barcode Generator In VS .NET
Using Barcode maker for .NET framework Control to generate, create barcode image in VS .NET applications.
www.OnBarcode.com
Bar Code Recognizer In Visual Studio .NET
Using Barcode decoder for Visual Studio .NET Control to read, scan read, scan image in .NET applications.
www.OnBarcode.com
[View full size image]
Make QR Code In C#.NET
Using Barcode creator for VS .NET Control to generate, create Quick Response Code image in .NET framework applications.
www.OnBarcode.com
Print Quick Response Code In Visual Studio .NET
Using Barcode generator for ASP.NET Control to generate, create Quick Response Code image in ASP.NET applications.
www.OnBarcode.com
Here are the performance measures I got for this query: Logical reads: 21 Physical reads: 0 Read-ahead reads: 18 CPU time: 0 ms Elapsed time: 73 ms Estimated subtree cost: 0.0159642 Note that this plan is trivial for the optimizer to generate. That is, the plan is not dependent on the selectivity of the query. Rather, it will always be used regardless of the size of the sought range. Unless, of course, you have an even better index for the query to begin with.
QR Code Encoder In VB.NET
Using Barcode generator for .NET framework Control to generate, create QR-Code image in .NET applications.
www.OnBarcode.com
EAN-13 Creator In Visual Studio .NET
Using Barcode creation for .NET Control to generate, create EAN-13 Supplement 5 image in .NET framework applications.
www.OnBarcode.com
Covering Nonclustered Index Seek + Ordered Partial Scan
Generate Data Matrix In VS .NET
Using Barcode creator for Visual Studio .NET Control to generate, create Data Matrix image in VS .NET applications.
www.OnBarcode.com
Code128 Drawer In .NET Framework
Using Barcode generation for .NET framework Control to generate, create Code128 image in VS .NET applications.
www.OnBarcode.com
The access method covering nonclustered index seek + ordered partial scan is almost identical to the previously described access method, with the only difference being that the former uses a covering
Code-39 Maker In VS .NET
Using Barcode drawer for VS .NET Control to generate, create Code 39 image in .NET framework applications.
www.OnBarcode.com
Code 2/5 Creation In Visual Studio .NET
Using Barcode creation for .NET framework Control to generate, create 2/5 Industrial image in .NET framework applications.
www.OnBarcode.com
nonclustered index instead of the clustered index. To use this method, of course the filtered columns must be the first key columns in the index. The benefit of this access method over the previous one lies in the fact that a nonclustered index leaf page naturally can fit more rows than a clustered index one; therefore, the bulk cost of the plan, which is the partial scan cost of the leaf, is lower. The cost is lower because fewer pages need to be scanned for the same size of the range. Of course, here as well, index fragmentation plays an important performance role because the partial scan is ordered. As an example, the following query looking for a range of orderdate values for a given shipperid uses this access method against the covering index idx_nc_sid_od_cid , created on (shipperid, orderdate, custid ): SELECT shipperid, orderdate, custid FROM dbo.Orders WHERE shipperid = 'C' AND orderdate >= '20060101' AND orderdate < '20070101';
Making PDF-417 2d Barcode In C#
Using Barcode drawer for .NET framework Control to generate, create PDF417 image in VS .NET applications.
www.OnBarcode.com
Scanning Code 128 Code Set A In Visual Studio .NET
Using Barcode reader for .NET Control to read, scan read, scan image in VS .NET applications.
www.OnBarcode.com
Note
Encoding PDF417 In None
Using Barcode encoder for Excel Control to generate, create PDF 417 image in Excel applications.
www.OnBarcode.com
Code 39 Full ASCII Creator In VB.NET
Using Barcode drawer for VS .NET Control to generate, create Code 3 of 9 image in .NET framework applications.
www.OnBarcode.com
To have the partial scan read the minimum required pages, the first index key columns must be shipperid, orderdate , in that order. If you swap their order, the partial scan will end up also scanning rows that meet the date range also for other shippers, requiring more I/O.
Creating Code 128 Code Set C In Objective-C
Using Barcode creation for iPad Control to generate, create Code 128A image in iPad applications.
www.OnBarcode.com
PDF-417 2d Barcode Encoder In None
Using Barcode generator for Online Control to generate, create PDF417 image in Online applications.
www.OnBarcode.com
Figure 3-43 illustrates the access method, and Figure 3-44 shows the execution plan for the query.
EAN 13 Maker In Visual Studio .NET
Using Barcode creator for ASP.NET Control to generate, create UPC - 13 image in ASP.NET applications.
www.OnBarcode.com
Generate Code128 In .NET
Using Barcode encoder for ASP.NET Control to generate, create Code 128A image in ASP.NET applications.
www.OnBarcode.com
Figure 3-43. Covering nonclustered index seek + ordered partial scan
Figure 3-44. Covering nonclustered index seek + ordered partial scan (execution plan)
[View full size image]
Here are the performance measures I got for this query: Logical reads: 208 CPU time: 30 ms Elapsed time: 954 ms Estimated subtree cost: 0.207428 Note that this plan is also a trivial plan that is not based on the query's selectivity. Remember, the main benefit of this access method is that there are no lookups involved because the index covers the query. Also, you read fewer pages than in a similar access method against a clustered index.
Also note that when you create covering indexes, the index columns serve two different functions. Columns that you filter or sort by are required as key columns that will be maintained in all levels of the balanced tree, and they will also determine the sort order at the leaf. Other index columns might be required only for covering purposes. If you include all index columns in the index's key column list, bear in mind that this has a cost. SQL Server needs to keep the tree balanced, and it will have to apply physical movement of data and adjustments in the tree when you modify key column values in the table. That's just a waste with columns that are required only for covering purposes and not for filtering or sorting. To tackle this need, SQL Server 2005 introduces the concept of included non-key columns in the index. When you create an index, you separately specify which columns will make the key list and which will be included just for covering purposesonly at the leaf level of the index. As an example, our last query relied only on shipperid and orderdate for filtering and sorting purposes, while it relied on custid only for covering purposes. To benefit from the new feature in SQL Server 2005, drop the index and create a new one, specifying custid in the INCLUDE clause like so: DROP INDEX dbo.Orders.idx_nc_sid_od_cid; CREATE NONCLUSTERED INDEX idx_nc_sid_od_i_cid ON dbo.Orders(shipperid, orderdate) INCLUDE(custid);
Note that the key list is limited to 16 columns and 900 bytes. This is true in both SQL Server 2000 and SQL Server 2005. An added bonus with included non-key columns is that they are not bound by the same limitations. In fact, they can even include large objects such as variable-length columns defined with the MAX specifier and XML columns.
Copyright © OnBarcode.com . All rights reserved.