Reuse of Query Execution Plans in VS .NET

Creation Data Matrix ECC200 in VS .NET Reuse of Query Execution Plans

Reuse of Query Execution Plans
Recognizing Data Matrix ECC200 In VS .NET
Using Barcode Control SDK for .NET framework Control to generate, create, read, scan barcode image in .NET framework applications.
Data Matrix ECC200 Encoder In .NET
Using Barcode printer for Visual Studio .NET Control to generate, create Data Matrix ECC200 image in VS .NET applications.
A simple query can be reused only in two scenarios. First, the query text of the second query must be identical to the text of the query described by the execution plan in the cache. Everything has to match spaces, line breaks, indentation even case on case-sensitive servers. The second scenario may occur when the query contains fully qualified database objects to reuse execution plans:
Read Data Matrix ECC200 In .NET
Using Barcode recognizer for .NET framework Control to read, scan read, scan image in VS .NET applications.
Bar Code Drawer In .NET Framework
Using Barcode creator for .NET framework Control to generate, create bar code image in Visual Studio .NET applications.
Select * from Asset.dbo.Inventory
Bar Code Recognizer In VS .NET
Using Barcode reader for .NET framework Control to read, scan read, scan image in VS .NET applications.
Create Data Matrix In Visual C#.NET
Using Barcode creator for Visual Studio .NET Control to generate, create ECC200 image in .NET framework applications.
Parameterized Queries
Generating DataMatrix In .NET
Using Barcode printer for ASP.NET Control to generate, create DataMatrix image in ASP.NET applications.
DataMatrix Creator In VB.NET
Using Barcode maker for VS .NET Control to generate, create DataMatrix image in Visual Studio .NET applications.
The designers of SQL Server have created two methods to improve the reuse of queries that are not designed as stored procedures: Autoparameterization The sp_executesql stored procedure We will cover the first of these methods in the following section and the second one in 9.
Make 2D Barcode In .NET
Using Barcode encoder for Visual Studio .NET Control to generate, create Matrix Barcode image in VS .NET applications.
Printing Code-128 In Visual Studio .NET
Using Barcode generation for .NET framework Control to generate, create Code-128 image in .NET applications.
Autoparameterization
Code-39 Printer In Visual Studio .NET
Using Barcode encoder for .NET Control to generate, create Code39 image in .NET framework applications.
Generate MSI Plessey In VS .NET
Using Barcode creation for Visual Studio .NET Control to generate, create MSI Plessey image in VS .NET applications.
When a Transact-SQL statement is sent to SQL Server, it attempts to determine whether any of its constants can be replaced with parameters. Subsequent queries that use the same template will reuse the same execution plan. For example, let s say that SQL Server receives the following ad hoc query:
Generating GS1-128 In Java
Using Barcode generation for Java Control to generate, create UCC - 12 image in Java applications.
Encoding Code39 In Java
Using Barcode drawer for Android Control to generate, create ANSI/AIM Code 39 image in Android applications.
SELECT FirstName, LastName, Phone, Fax, Email, OrgUnitId, UserName FROM Asset.dbo.Contact where ContactId = 3
Code 3/9 Maker In None
Using Barcode maker for Excel Control to generate, create Code 3 of 9 image in Excel applications.
Scan ANSI/AIM Code 128 In VS .NET
Using Barcode decoder for .NET framework Control to read, scan read, scan image in VS .NET applications.
3: Stored Procedure Design Concepts
Bar Code Scanner In .NET Framework
Using Barcode Control SDK for ASP.NET Control to generate, create, read, scan barcode image in ASP.NET applications.
Printing USS Code 39 In Objective-C
Using Barcode printer for iPhone Control to generate, create Code 3/9 image in iPhone applications.
It will try to parameterize it in the following manner and create an execution plan:
Make Bar Code In None
Using Barcode drawer for Software Control to generate, create barcode image in Software applications.
Drawing EAN128 In Visual C#
Using Barcode creator for .NET framework Control to generate, create UCC.EAN - 128 image in VS .NET applications.
SELECT FirstName, LastName, Phone, Fax, Email, OrgUnitId, UserName FROM Asset.dbo.Contact where ContactId = @P1
After this, all similar queries will reuse the execution plan:
SELECT FirstName, LastName, Phone, Fax, Email, OrgUnitId, UserName FROM Asset.dbo.Contact where ContactId = 11
SQL Server applies autoparameterization only when a query s template is safe that is, when the execution plan will not be changed and the performance of SQL Server will not be degraded if parameters are changed.
NOTE
SQL Server might decide to create and use a different execution plan even if the query is based on the same field. For example, imagine that you are querying a table with contact information using the Country field. If your company is operating predominantly in North America, SQL Server might carry out a query for Denmark contacts based on the index on the Country field and a query for USA contacts as a table scan.
SQL Server attempts autoparameterization on Insert, Update, and Delete statements too. In fact, the query must match a set of four templates in order for SQL Server to attempt autoparameterization:
Select {* | column-list} From table Where column-expression [Order by column-list] Insert table Values ({constant | NULL | Default} [, ...n]) Update table set column-name = constant where column-expression Delete table Where column-expression
Note that a column-expression is an expression that involves only column names, constants, the And operator, and comparison operators: <, >, =, >=, <=, and <>.
SQL Server 2000 Stored Procedure & XML Programming
SQL Server is more forgiving about formatting the query when autoparameterization is used, but it still does not allow changes in capitalization or changes in the way an object is qualified.
Reuse of Stored Procedure Execution Plans
Stored procedures do not have the limitations associated with ad hoc queries, and that is the main reason stored procedures are reused more often then queries. The reuse of execution plans is one of the main reasons why the use of stored procedures is a better solution than the use of ad hoc queries. For example, if you execute a query three times, SQL Server will have to parse, recompile, and execute it three times. A stored procedure will most likely be parsed and recompiled only once just before the first execution.
NOTE
Someone might argue that the time needed to compile is insignificant compared with the time needed to execute a query. That is sometimes true. But the SQL Server query engine in this version compares dozens of new processing techniques in order to select the best one to process the query or stored procedure. Therefore, the time needed to recompile a stored procedure is greater in this version than it used to be in earlier versions.
The execution plan consists of two parts. One is reentrant and can be used concurrently by any number of processes. The other part contains the data context; that is, the parameters to be used during execution. Although this part can be reused, it cannot be used by another process concurrently, so more instances of this part will be created. The execution plan will be removed from the procedure cache when a process called lazywriter concludes that the execution plan has not been used for a while and SQL Server needs more memory, or when the execution plan s dependent database objects are changed in any of the following ways: The amount of data is significantly changed Indexes are created or dropped Constraints are added or changed Distribution statistics of indexes are changed sp_recompile was explicitly called to recompile the stored procedure or trigger I was impressed with the way that lazywriter determines which execution plans are obsolete. Microsoft SQL Server 2000 contains a sophisticated emulation of the aging process. When SQL Server creates an execution plan, it assigns it a compilation cost factor. The value of this factor depends on the expense required to create the
Copyright © OnBarcode.com . All rights reserved.