qr code generator c# mvc FiGURe 6-11 The actual execution plan of the SELECT statement without a SARG in C#

Generation QR-Code in C# FiGURe 6-11 The actual execution plan of the SELECT statement without a SARG

FiGURe 6-11 The actual execution plan of the SELECT statement without a SARG
Drawing QR Code In C#.NET
Using Barcode generator for VS .NET Control to generate, create QR-Code image in .NET framework applications.
www.OnBarcode.com
Reading QR Code 2d Barcode In Visual C#.NET
Using Barcode decoder for Visual Studio .NET Control to read, scan read, scan image in .NET applications.
www.OnBarcode.com
Because the query did not use a valid SARG (the column in the WHERE clause is used in an expression), the OrderDateIndex index can be used only for scanning and not for seeking. To be able to produce an index seek, SQL Server must maintain an index of the result of the function call, in this case, MONTH(OrderDate). You can do this by adding a computed column to the table and indexing that column as follows (the query s execution plan is shown in Figure 6-12):
Drawing Barcode In C#.NET
Using Barcode drawer for .NET framework Control to generate, create barcode image in .NET applications.
www.OnBarcode.com
Bar Code Scanner In C#.NET
Using Barcode scanner for VS .NET Control to read, scan read, scan image in .NET applications.
www.OnBarcode.com
-- Add the column. ALTER TABLE Sales.SalesOrderHeader ADD OrderMonth AS MONTH(OrderDate);
Creating QR Code JIS X 0510 In .NET
Using Barcode creator for ASP.NET Control to generate, create QR Code JIS X 0510 image in ASP.NET applications.
www.OnBarcode.com
Denso QR Bar Code Maker In .NET
Using Barcode encoder for VS .NET Control to generate, create Denso QR Bar Code image in .NET applications.
www.OnBarcode.com
Lesson 2: Creating Indexes
QR Code JIS X 0510 Generation In VB.NET
Using Barcode encoder for .NET Control to generate, create QR Code image in Visual Studio .NET applications.
www.OnBarcode.com
Generate Code 39 Extended In C#
Using Barcode maker for .NET Control to generate, create Code 39 image in .NET framework applications.
www.OnBarcode.com
-- Create an index on the computed column. CREATE NONCLUSTERED INDEX OrderMonthIndex ON Sales.SalesOrderHeader (OrderMonth); GO
USS Code 128 Creation In C#
Using Barcode encoder for VS .NET Control to generate, create USS Code 128 image in .NET framework applications.
www.OnBarcode.com
GTIN - 12 Creator In Visual C#
Using Barcode drawer for .NET framework Control to generate, create Universal Product Code version A image in VS .NET applications.
www.OnBarcode.com
SET STATISTICS IO ON;
Matrix 2D Barcode Drawer In Visual C#.NET
Using Barcode maker for .NET framework Control to generate, create Matrix 2D Barcode image in .NET framework applications.
www.OnBarcode.com
Leitcode Printer In Visual C#
Using Barcode creation for Visual Studio .NET Control to generate, create Leitcode image in VS .NET applications.
www.OnBarcode.com
-- Run the query and reference the new column. SELECT COUNT(*) FROM Sales.SalesOrderHeader WHERE OrderMonth = 5;
Code 128 Encoder In Visual Basic .NET
Using Barcode generator for .NET framework Control to generate, create Code 128 Code Set A image in Visual Studio .NET applications.
www.OnBarcode.com
Barcode Generator In None
Using Barcode printer for Online Control to generate, create bar code image in Online applications.
www.OnBarcode.com
FiGURe 6-12 The actual execution plan of the SELECT statement using the computed column
Code 128 Code Set C Generator In Java
Using Barcode printer for BIRT Control to generate, create Code 128C image in BIRT reports applications.
www.OnBarcode.com
Printing UPCA In Java
Using Barcode maker for Java Control to generate, create UCC - 12 image in Java applications.
www.OnBarcode.com
in the WHERE clause
UCC - 12 Drawer In Java
Using Barcode generation for Java Control to generate, create GTIN - 12 image in Java applications.
www.OnBarcode.com
Code 39 Full ASCII Printer In .NET
Using Barcode creation for Reporting Service Control to generate, create Code-39 image in Reporting Service applications.
www.OnBarcode.com
This time, the query performs a seek operation on the index of the computed column, resulting in only eight page reads. Depending on the complexity of your query and computed column definition, the optimizer automatically uses the index of the computed column without the computed column being referenced in the query. The following query, for example, also generates the execution plan previously shown in Figure 6-12:
Code 39 Generator In Objective-C
Using Barcode encoder for iPad Control to generate, create Code 39 Full ASCII image in iPad applications.
www.OnBarcode.com
UPC Symbol Printer In Objective-C
Using Barcode creator for iPhone Control to generate, create Universal Product Code version A image in iPhone applications.
www.OnBarcode.com
SET STATISTICS IO ON;
-- Run the query without referencing the computed column. SELECT COUNT(*) FROM Sales.SalesOrderHeader WHERE MONTH(OrderDate) = 5;
As you can see, SQL Server used the index of the computed column without having a reference to it in the query. This is a great feature because it makes it possible to add computed columns and index them without having to change the queries in applications or stored procedures to use the new index. Besides using indexed computed columns with function calls, you can also use indexed computed columns to provide indexes in different collations. Consider that you have the table Test.Person with the column Name using the Latin1_General_CI_AI collation. Now you want to find all rows starting with the character . In Latin1_General, the dots over the O are just considered accents, but in other languages, such as German and Swedish, is a different character than O. Consider that the table is typically queried by English-speaking customers who expect to get both O and back from a search such as LIKE % and occasionally by Swedish customers who expect to get only back from that same search. Because the
Techniques to Improve Query Performance
table is typically queried by English-speaking customers, it makes sense to keep the Latin1_General_CI_AI collation, and, when Swedish customers query the table, to use the COLLATE keyword to use the Finnish_Swedish_CI_AI collation explicitly. Review the following script and queries. The execution plans for the two queries in the following script are shown in Figures 6-13 and 6-14:
-- Create and populate the table CREATE TABLE Test.ProductNames ( Name NVARCHAR(50) COLLATE Latin1_General_CI_AI );
INSERT Test.ProductNames (Name) VALUES (' l'); INSERT Test.ProductNames (Name) VALUES ('Olja'); INSERT Test.ProductNames (Name) VALUES ('Beer'); INSERT Test.ProductNames (Name) VALUES ('Oil');
CREATE CLUSTERED INDEX NameIndex ON Test.ProductNames (Name); GO
-- Query 1 -- Query for all product names that begin with the letter -- using the default collation. SELECT Name FROM Test.ProductNames WHERE Name LIKE ' %';
Here is the result of Query 1:
Name ------------Oil l Olja
Query 2 looks like this:
-- Query 2 -- Query for all product names that begin with the letter -- using the Finnish_Swedish_CI_AI collation. SELECT Name FROM Test.ProductNames WHERE Name LIKE ' %' COLLATE Finnish_Swedish_CI_AI;
Here is the result of Query 2:
Name ------------ l
Lesson 2: Creating Indexes
FiGURe 6-13 The actual execution plan of Query 1 in SSMS
FiGURe 6-14 The actual execution plan of Query 2 in SSMS
Comparing the execution plans of Query 1 (Figure 6-13) and Query 2 (Figure 6-14), you can see that in Query 2, because the comparison needs to use a collation other than that of the column (and therefore, the index), a clustered index scan is used instead of an index seek, as in Query 1. By adding an indexed computed column to this table and specifying the Finnish_Swedish_CI_AS collation for this column (as shown in the next code example), SQL Server can automatically use that index instead. Note that the query itself need not change, and that this is a viable solution only if you are using a relatively low number of collations because these indexes need to be both stored and maintained, like all other indexes. The execution plan for the query in the following script is shown in Figure 6-15:
-- Add a computed column with another collation. ALTER TABLE Test.ProductNames ADD Name_Finnish_Swedish_CI_AI AS Name COLLATE Finnish_Swedish_CI_AI;
-- Create an index on the computed column. CREATE NONCLUSTERED INDEX NameIndex2 ON Test.ProductNames (Name_Finnish_Swedish_CI_AI); GO
-- Query for all product names that begin with the letter -- using the Finnish_Swedish_CI_AI collation without specifying -- the computed column. SELECT Name FROM Test.ProductNames WHERE Name LIKE ' %' COLLATE Finnish_Swedish_CI_AI;
Here is the result of this query:
Copyright © OnBarcode.com . All rights reserved.