首页 > 代码库 > SqlServer性能优化 即席查询(十三)

SqlServer性能优化 即席查询(十三)

执行计划,查询类别:

  1.即席查询     2.预定义查询

select c.EnglishProductCategoryName,p.EnglishProductName,p.Color,p.Size
from Product as p inner join ProductCategory as c on p.ProductSubcategoryKey=
c.ProductCategoryKey where p.Size>‘1‘

--查询执行计划是否被缓存 select c.usecounts,c.size_in_bytes,c.objtype,t.text from sys.dm_exec_cached_plans as c cross apply sys.dm_exec_sql_text(c.plan_handle) as t dbcc freeproccache--清空执行计划
--没有join 的形式会生成简单参数化 select EnglishProductName,Color,Size from Product where size>‘1‘--简单参数化

select EnglishProductName,Color,Size from Product where size>‘2‘--简单参数化

 技术分享

 

select c.EnglishProductCategoryName,p.EnglishProductName,p.Color,p.Size
from Product as p inner join ProductCategory as c on p.aProductSubcategoryKey=
c.ProductCategoryKey where p.Size>‘2‘

语句一样时即席查询才会重用执行计划。

技术分享

 

优化:打开开关

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

 为ad hoc的查询优化:

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

 技术分享

 

--使用参数化
alter database HRDB
set Parameterization forced

  set Parameterization forced 强制参数化(like无法识别 ) 

select c.EnglishProductCategoryName,p.EnglishProductName,p.Color,p.Size
from 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.Size
from Product as p inner join ProductCategory as c on p.ProductSubcategoryKey=
c.ProductCategoryKey where p.Size like ‘2%‘

 

 

预定义查询:

预定义查询--参数化执行计划:

     存储过程:

           1.创建时延时检查

           2.第一次执行时编译并生成执行计划

           3.减少网络传输量

           4.封装变化点

           5.增强安全性,隔离访问控制

创建存储过程:

create procedure p_querycp @size varchar(500)
as
select c.EnglishProductCategoryName,p.EnglishProductName,p.Color,p.size
from Product as p inner join ProductCategory as c on p.ProductSubcategoryKey=c.ProductCategoryKey
where p.Size>@size

 做跟踪(以前有对应得截图):

执行存储过程:

create procedure p_querycp @size varchar(500)
as
select c.EnglishProductCategoryName,p.EnglishProductName,p.Color,p.size
from Product as p inner join ProductCategory as c on p.ProductSubcategoryKey=c.ProductCategoryKey
where p.Size>@size

--清空执行计划
dbcc freeproccache
--执行
exec p_querycp ‘1‘

 执行重复的语句:

dbcc freeproccache

exec p_querycp @size=‘1‘

exec p_querycp @size=‘2‘

 查看缓存计划:

select c.usecounts,c.size_in_bytes,c.objtype,t.text from sys.dm_exec_cached_plans as c
cross apply sys.dm_exec_sql_text(c.plan_handle) as t

 技术分享

 

预定义查询---参数化执行计划:

SP_ExecuteSql

      避免了自己维护存储过程管理成本

      可重用执行计划

      Unicode字符串作为参数值与类型

      大小写敏感

 

把存储过程定义成传递参数的:

declare @sqltext nvarchar(500)
set @sqltext=N‘
select c.EnglishProductCategoryName,p.EnglishProductName,p.Color,p.size
from Product as p inner join ProductCategory as c on p.ProductSubcategoryKey=c.ProductCategoryKey
where p.Size>@size
‘
declare @params	nvarchar(500)
set @params=N‘@size varchar(500)‘
 exec sp_executesql @sqltext,@params,@size=‘1‘

 技术分享

 

把size 的大小换成  2

 技术分享

 

 在.net中调用:(两种写法)

        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.Size == size
                      //返回匿名对象
                      select new
                      {
                          CName = c.EnglishProductCategoryName,
                          PName = p.EnglishProductName,
                          Color = 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, Size = a.Size }).Where(p => p.Size == size); return cps.ToList(); }

 页面:

  <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
    <asp:Button ID="Button2" runat="server" OnClick="Button2_Click" Text="显示产品" />
    <asp:GridView ID="GridView1" runat="server">
    </asp:GridView>

 点击后的事件:

  protected void Button2_Click(object sender, EventArgs e)
        {
            Product p = new Product();
            var cps = p.getCp(TextBox1.Text.Trim());
            GridView1.DataSource = cps;
            GridView1.DataBind();
        }

 技术分享

 

 

 

技术分享

 

SqlServer性能优化 即席查询(十三)