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)