首页 > 代码库 > 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性能优化 即席查询(十三)
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。