SQL Server Performance Optimization ad hoc queries (13)

Source: Internet
Author: User

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)

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.