(4.26)sql server存储过程优化
此博客介绍了简单但有用的提示和优化,以提高存储过程的性能。
0.with recompile:重编译
exec uspGetSalesInfoForDateRange ‘1/1/2009’, 31/12/2009 with recompile
1.使用SET NOCOUNT ON
SQL Server在运行select或DML操作时返回信息性消息。如果一个过程有许多这样的语句,游标或while循环SQL Server将显示许多此类消息,增加网络流量。可以使用SET NOCOUNT ON来抑制这些消息,并可以通过减少网络流量来提高性能。
2.使用完全限定的程序名称
完全限定的对象名称是database.schema.objectname。当存储过程作为schemaname.procedurename调用时,SQL Server可以快速查找已编译的计划,而不是在未指定schemaname时查找其他模式中的过程。这可能不会对性能产生很大的推动作用,但应该遵循最佳实践。过程中的所有对象也应该称为schemaname.objectname。
3. sp_executesql而不是Execute用于动态查询
sp_executesql允许重用缓存计划并防止SQL注入。我们来看一个计划重用的例子。
DBCC FREEPROCCACHE GO Declare @dynamic_sql varchar(max), @salesorderid int SET @salesorderid=43660 SET @dynamic_sql=' SELECT * FROM Sales.SalesOrderDetail where SalesOrderID=' + CAST(@salesorderid AS VARCHAR(100)) EXECUTE(@dynamic_sql)
上面的查询使用EXECUTE命令为salesorderid 43660和43661的两个值执行动态查询。让我们分析缓存的计划。
如上面的快照所示,两个salesorderids有两个单独的计划。现在让我们使用sp_execute SQL执行相同的查询并分析缓存的计划。
DECLARE @dynamic_sql NVARCHAR(100) SET @dynamic_sql = N'SELECT * FROM Sales.SalesOrderDetail where SalesOrderID=@salesorderid' EXECUTE sp_executesql @dynamic_sql, N'@salesorderid int', @salesorderid = 43661
上面的查询使用sp_executesql为2个不同的salesorderid值执行动态查询。我们来分析一下缓存的计划。
如上面的快照所示,只缓存了一个计划,并用于salesorderid的不同值。
4.使用IF EXISTS AND SELECT
IF EXISTS用于检查记录,对象等的存在。并且是一个方便的语句,用于提高查询的性能,其中一个只想检查表中记录的存在而不是在查询中使用该记录/行。
这样做的时候使用IF EXISTS(来自mytable的SELECT 1)而不是IF EXISTS(从mytable中选择*),因为我们感兴趣的只是检查记录/ s的存在。因此,如果查询返回1,则记录存在,否则不存在。无需返回所有列值。
5.避免将用户存储过程命名为sp_procedurename。
如果存储过程以sp_开头,则SQL Server首先在master数据库中搜索它,然后在当前用户数据库中搜索它。这可能会导致轻微的性能问题,而且如果master数据库中存在具有相同名称的存储过程,则可能导致错误的结果。
6.尽可能使用基于集合的查询。
T-SQL是一种基于集合的语言,因此循环在这里不能很好地工作。仅当基于集合的查询要么昂贵或无法制定时,才使用游标和while循环。
7.保持交易简短明快
事务越长,根据隔离级别保持锁定的时间越长。这可能会导致死锁和阻塞。打开一个新的查询窗口并执行以下查询
use AdventureWorks2014 GO BEGIN TRANSACTION SET TRANSACTION ISOLATION LEVEL SERIALIZABLE SELECT * FROM Sales.SalesOrderDetail
请注意查询的会话ID。打开一个新的查询窗口并执行以下查询。记下查询的会话ID。
begin tran Update Sales.SalesOrderDetail SET OrderQty=50 WHERE SalesOrderDetailID=1
以上更新查询将等待共享锁上的选择查询。让我们分析这两个会话的锁。
如上面的快照所示,会话58更新查询正在等待会话57采取的共享锁。
请遵循这些提示,让我知道它如何提高程序性能。将返回一些更多提示和最佳实践。
相关文章
- 在虚拟机安装 Linux Server (ubuntu-14.04-server)
- is-a,has-a,like-a是什么 sql server中,N''表示什么意思? 关于SQL SERVER的N前缀的理解
- SQL server 存储过程 C#调用Windows CMD命令并返回输出结果 Mysql删除重复数据保留最小的id C# 取字符串中间文本 取字符串左边 取字符串右边 C# JSON格式数据高级用法
- C#构造方法(函数) C#方法重载 C#字段和属性 MUI实现上拉加载和下拉刷新 SVN常用功能介绍(二) SVN常用功能介绍(一) ASP.NET常用内置对象之——Server sql server——子查询 C#接口 字符串的本质 AJAX原生JavaScript写法
- SQL Server如何存储特殊字符、上标、下标
- Sql Server 给表添加合计并统计金额的存储过程!(源代码分享)
- Sql Server A表汇总到B表存储过程(直接赋参数用,源码)
- Sql Server 删除数据表的存储过程,直接能用!(源码带说明)
- com.sun.xml.internal.ws.server.ServerRtException: Server Runtime Error: java.net.BindException: Cannot assign requested address: bind
- sql server 小技巧(4) Sql server 排序时让空值排在最后
- sql server 数据分析优化实战(一)——SQL语句优化
- Sql Server 优化 SQL 查询:如何写出高性能SQL语句
- SQL SERVER大话存储结构(4)_复合索引与包含索引
- Sql Server 优化----SQL语句的执行方式与锁以及阻塞的关系
- ArcGIS Server的Tile的松散型存储(Exploded)
- 人人都是 DBA(VIII)SQL Server 页存储结构
- SQL Server SSPI handshake failed报错,sql server修改windows账户密码的影响,sql server订阅发布客户端访问一直报错
- (4.51)sql server用 sp_getapplock 实现串行执行的存储过程
- sql server作业报错:从ICassFactory为CLSID为{17BCA6E8-A950-497E-B2F9-AF6AA475916F}的COM组件创建实例失败,原因是出现以下错误:c001f011.(Microsoft.Server.manageDTS)
- 【sql server复制】教你使用SQL SERVER复制
- sql server系统存储过程大全
- (3.7)sql server存储引擎--索引的结构与分类
- (3.6)sql server存储引擎--文件与数据页及数据行的结构
- (2.13)备份与还原--sql server数据库主要状态和切换路径
- SQL Server Profiler 跟踪某个数据库某张表sql语句