asp.net c# qr code generator Query Tuning in Visual C#.NET

Generate QR-Code in Visual C#.NET Query Tuning

4
Quick Response Code Generation In Visual C#
Using Barcode printer for .NET Control to generate, create QR Code image in .NET framework applications.
www.OnBarcode.com
QR Code JIS X 0510 Reader In Visual C#.NET
Using Barcode decoder for VS .NET Control to read, scan read, scan image in .NET framework applications.
www.OnBarcode.com
Query Tuning
Barcode Creation In C#
Using Barcode creation for Visual Studio .NET Control to generate, create bar code image in .NET applications.
www.OnBarcode.com
Barcode Reader In C#
Using Barcode reader for .NET Control to read, scan read, scan image in VS .NET applications.
www.OnBarcode.com
assume that the query pattern is typically fairly selective (around 0.1 percent selectivity, or around 1000 qualifying rows). Use the following query in your tuning process when aiming at such selectivity:
Making QR Code In VS .NET
Using Barcode encoder for ASP.NET Control to generate, create QR-Code image in ASP.NET applications.
www.OnBarcode.com
QR Code JIS X 0510 Drawer In VS .NET
Using Barcode encoder for .NET framework Control to generate, create QR-Code image in .NET applications.
www.OnBarcode.com
SELECT orderid, custid, empid, shipperid, orderdate FROM dbo.Orders WHERE orderid >= 999001;
Denso QR Bar Code Generator In Visual Basic .NET
Using Barcode printer for .NET Control to generate, create QR Code JIS X 0510 image in .NET framework applications.
www.OnBarcode.com
Drawing QR Code JIS X 0510 In C#
Using Barcode encoder for VS .NET Control to generate, create QR Code image in VS .NET applications.
www.OnBarcode.com
I ll progress in the index optimization scale from the worst-case scenario to the best, using this query as a reference, but I ll also describe what would happen when the selectivity of the query changes.
Linear Barcode Generator In C#
Using Barcode generation for Visual Studio .NET Control to generate, create Linear Barcode image in .NET applications.
www.OnBarcode.com
Bar Code Drawer In Visual C#
Using Barcode drawer for .NET Control to generate, create bar code image in VS .NET applications.
www.OnBarcode.com
Table Scan (Unordered Clustered Index Scan)
Printing Bar Code In C#.NET
Using Barcode creation for VS .NET Control to generate, create barcode image in .NET applications.
www.OnBarcode.com
Generate USPS Confirm Service Barcode In Visual C#
Using Barcode printer for VS .NET Control to generate, create USPS PLANET Barcode image in .NET framework applications.
www.OnBarcode.com
The worst-case scenario for our query pattern with fairly high selectivity is when you have no good index. You will get the execution plan shown in Figure 4-53, using a table scan (unordered clustered index scan).
Generating Bar Code In None
Using Barcode maker for Office Word Control to generate, create bar code image in Office Word applications.
www.OnBarcode.com
Denso QR Bar Code Maker In None
Using Barcode creation for Software Control to generate, create Quick Response Code image in Software applications.
www.OnBarcode.com
FIGURE 4-53 Execution plan with table scan (unordered clustered index scan)
Create Denso QR Bar Code In None
Using Barcode printer for Office Excel Control to generate, create QR image in Office Excel applications.
www.OnBarcode.com
UPC Symbol Drawer In None
Using Barcode creation for Excel Control to generate, create GTIN - 12 image in Excel applications.
www.OnBarcode.com
Even though you re after a fairly small number of rows (1,000 in our case), the whole table is scanned. I got the following performance measures for this query:
Painting Code-128 In None
Using Barcode drawer for Office Word Control to generate, create Code 128C image in Microsoft Word applications.
www.OnBarcode.com
Code 39 Extended Scanner In None
Using Barcode scanner for Software Control to read, scan read, scan image in Software applications.
www.OnBarcode.com
Logical reads 25175 CPU time 249 ms Elapsed time 8605 Estimated subtree cost 19.3423
Code 39 Full ASCII Drawer In Java
Using Barcode generation for Java Control to generate, create Code39 image in Java applications.
www.OnBarcode.com
Recognize GTIN - 12 In .NET Framework
Using Barcode scanner for VS .NET Control to read, scan read, scan image in .NET applications.
www.OnBarcode.com
This plan is trivial and not dependent on selectivity that is, you get the same plan regardless of the selectivity of the query.
Unordered Covering Nonclustered Index Scan
The next step in the optimization scale would be to create a covering nonclustered index where the ltered column (orderid) is not the rst index column:
CREATE NONCLUSTERED INDEX idx_nc_od_i_oid_cid_eid_sid ON dbo.Orders(orderdate) INCLUDE(orderid, custid, empid, shipperid);
Inside Microsoft SQL Server 2008: T-SQL Querying
This index yields an access method that uses a full unordered scan of the leaf of the index, as shown in Figure 4-54.
FIGURE 4-54 Execution plan with unordered covering nonclustered index scan
The row size in the covering index is about a fth of the size of a full data row, and this will be re ected in the query s cost and run time. Here are the performance measures I got for this query:
Logical reads 5142 CPU time 140 ms Elapsed time 2543 ms Estimated subtree cost 4.58245
As with the previous plan, this plan is also trivial and not dependent on selectivity.
Note The run times you will get for your queries will vary based on what portion of the data is cached. If you want to make credible performance comparisons in terms of run times, make sure that the caching environment in both cases re ects what you would have in your production environment. That is, if you expect most pages to reside in cache in your production environment (warm cache), run each query twice and measure the run time of the second run. If you expect most pages not to reside in cache (cold cache), in your tests clear the cache before you run each query. Before you proceed, drop the index that you just created:
DROP INDEX dbo.Orders.idx_nc_od_i_oid_cid_eid_sid;
Unordered Nonclustered Index Scan + Lookups
The next step in our index optimization scale is to create a smaller nonclustered index that doesn t cover the query and that contains the ltered column (orderid), but not as the rst key column:
CREATE NONCLUSTERED INDEX idx_nc_od_i_oid ON dbo.Orders(orderdate) INCLUDE(orderid);
4
Query Tuning
You get an unordered nonclustered index scan + lookups, as shown in Figure 4-55.
FIGURE 4-55 Execution plan with unordered nonclustered index scan + lookups
Note that the ef ciency of this plan compared to the previous one depends on the selectivity of the query. As the selectivity of the query gets lower (low selectivity means a high percentage of rows), the more substantial the cost is of the lookups here. In our case, the query is fairly selective, so this plan is more ef cient than the previous two; however, with low selectivity, this plan will be less ef cient than the previous two. Here are the performance measures that I got for this query:
Logical reads 6501 CPU time 109 ms Elapsed time 1534 ms Estimated subtree cost 5.23753
Note that even though the number of logical reads and the query cost seem higher than in the previous plan, you can see that the run times are lower. Remember that the lookup operations here traverse the clustered index, and the nonleaf levels of the clustered index are most likely to reside in cache. Before you continue, drop the new index:
DROP INDEX dbo.Orders.idx_nc_od_i_oid;
Copyright © OnBarcode.com . All rights reserved.