SQL Server Performance Optimization ad hoc queries (13)

Execution plan, query category:

1. Ad hoc queries 2. Pre-defined queries

Select C.englishproductcategoryname,p.englishproductname,p.color,p.sizefrom Product as P inner join ProductCategory as C on P.productsubcategorykey=c.productcategorykey where p.size> ' 1 '
--Whether the query execution plan is cached select C.usecounts,c.size_in_bytes,c.objtype,t.text from Sys.dm_exec_cached_plans as Ccross apply sys.dm _exec_sql_text (C.plan_handle) as tdbcc freeproccache--empty execution plan
--no join form will generate simple parametric select englishproductname,color,size from Product where size> ' 1 '--simple parameterization

Select Englishproductname,color,size from Product where size> ' 2 '--Simple parameterization

Select C.englishproductcategoryname,p.englishproductname,p.color,p.sizefrom Product as P inner join ProductCategory as C on P.aproductsubcategorykey=c.productcategorykey where p.size> ' 2 '

statement, an ad hoc query reuses the execution plan.

Optimization: Turn on switch

exec sp_configure ' show advanced options ', 1reconfigure with override

Query optimization for ad hoc:

exec sp_configure ' Optimize for ad hoc workloads ', 1reconfigure with override

--Using parameterized ALTER DATABASE Hrdbset Parameterization forced

Set Parameterization forced forced parameterization (like unrecognized)

Select C.englishproductcategoryname,p.englishproductname,p.color,p.sizefrom Product as P inner join ProductCategory as C on P.productsubcategorykey=c.productcategorykey where p.size> ' 2 ' select C.englishproductcategoryname,p. Englishproductname,p.color,p.sizefrom Product as P inner join ProductCategory as C on P.productsubcategorykey=c. Productcategorykey where p.size like ' 2% '

Pre-defined query:

Predefined queries-Parameterized execution plans:

Stored procedures:

1. Time-lapse check on creation

2. Compile and generate execution plan on first execution

3. Reduce network traffic

4. Package Change Point

5. Enhanced security, isolated access control

To create a stored procedure:

CREATE PROCEDURE p_querycp @size varchar asselect c.englishproductcategoryname,p.englishproductname,p.color, P.sizefrom Product as P inner join ProductCategory as C on P.productsubcategorykey=c.productcategorykeywhere p.size>@s Ize

To do a trace (formerly corresponding to):

To execute a stored procedure:

CREATE PROCEDURE p_querycp @size varchar asselect c.englishproductcategoryname,p.englishproductname,p.color, P.sizefrom Product as P inner join ProductCategory as C on P.productsubcategorykey=c.productcategorykeywhere p.size>@s ize--empty execution Plan DBCC freeproccache--exec P_QUERYCP ' 1 '

To execute a repeating statement:

DBCC FREEPROCCACHEEXEC P_QUERYCP @size = ' 1 ' exec p_querycp @size = ' 2 '

To view the cache schedule:

Select C.usecounts,c.size_in_bytes,c.objtype,t.text from Sys.dm_exec_cached_plans as Ccross apply Sys.dm_exec_sql_ Text (c.plan_handle) as T

Predefined queries---Parameterized execution plans:

sp_executesql

Avoid maintaining storage process management costs for yourself

Reusable Execution Plan

Unicode strings as parameter values and types

Case sensitive

To define a stored procedure as passing parameters:

Declare @sqltext nvarchar set @sqltext =n ' select C.englishproductcategoryname,p.englishproductname,p.color, P.sizefrom Product as P inner join ProductCategory as C on P.productsubcategorykey=c.productcategorykeywhere p.size>@s Ize ' Declare @paramsnvarchar set @params =n ' @size varchar ' exec sp_executesql @sqltext, @params, @size = ' 1 '

Replace size with  2

Called in. NET: (two notation)

public object Getcp (string size) {HRUser DbContext = new HRUser (); var cps = from P in DbContext. Product join C in DbContext. ProductCategory on P.productsubcategorykey equals C.productcategorykey where P.S                          ize = = Size//Return anonymous object select New { CName = c.englishproductcategoryname, PName = P.englishproductname, Colo            r = p.color, Size = p.size}; Return CPS.        ToList (); }
public object Getcp (string size) {HRUser DbContext = new HRUser (); var cps = DbContext. Product.join (DbContext. ProductCategory, a = a.productsubcategorykey, AR = ar. Productcategorykey, (A, AR) = = new {CName = ar. Englishproductcategoryname, PName = a.englishproductname, Color = A.color, Si Ze = a.size}).            Where (p = p.size = = Size); Return CPS.        ToList (); }

Page:

  <asp:textbox id= "TextBox1" runat= "Server" ></asp:TextBox>    <asp:button id= "Button2" runat= "server "onclick=" button2_click "text=" Display Products "/>    <asp:gridview id=" GridView1 "runat=" Server ">    </asp: Gridview>

Post-click events:

  protected void button2_click (object sender, EventArgs e)        {            Product P = new product ();            var cps = p.getcp (TextBox1.Text.Trim ());            Gridview1.datasource = CPS;            Gridview1.databind ();        }

SQL Server Performance Optimization ad hoc queries (13)