SQL Server 优化存储过程的方法

最新推荐文章于 2024-07-18 16:44:41 发布

Lisliefor

最新推荐文章于 2024-07-18 16:44:41 发布

阅读量4.9k

点赞数

分类专栏： Sql Server 文章标签： sql server 存储优化 table join microsoft

Sql Server 专栏收录该内容

11 篇文章 0 订阅

订阅专栏

博文出处：SQL Server 优化存储过程的七种方法

博主写的是优化存储过程的七种方法，看了之后，我觉得标题定为“SQL Server优化存储过程的七种规范”比较好，博主讲的大部分是从规范、或者从约定上避免一些额外的、不必要的性能损耗，比较有价值，同样，在转载完博主的文章后，我会在后面总结一些自己在sql性能调优方面的一些经验。

『原文』

优化存储过程有很多种方法，下面介绍最常用的7种。

1.使用SET NOCOUNT ON选项

我们使用SELECT语句时，除了返回对应的结果集外，还会返回相应的影响行数。使用SET NOCOUNT ON后，除了数据集就不会返回额外的信息了，减小网络流量。

2.使用确定的Schema

在使用表，存储过程，函数等等时，最好加上确定的Schema。这样可以使SQL Server直接找到对应目标，避免去计划缓存中搜索。而且搜索会导致编译锁定，最终影响性能。比如select * from dbo.TestTable比select * from TestTable要好。from TestTable会在当前Schema下搜索，如果没有，再去dbo下面搜索，影响性能。而且如果你的表是csdn.TestTable的话，那么select * from TestTable会直接报找不到表的错误。所以写上具体的Schema也是一个好习惯。

3.自定义存储过程不要以sp_开头

因为以sp_开头的存储过程默认为系统存储过程，所以首先会去master库中找，然后在当前数据库找。建议使用USP_或者其他标识开头。

4.使用sp_executesql替代exec

原因在Inside Microsoft SQL Server 2005 T-SQL Programming书中的第四章Dynamic SQL里面有具体描述。这里只是简单说明一下：sp_executesql可以使用参数化，从而可以重用执行计划。exec就是纯拼SQL语句。

5.少使用游标

可以参考Inside Microsoft SQL Server 2005 T-SQL Programming书中的第三章Cursors里面有具体描述。总体来说，SQL是个集合语言，对于集合运算具有较高的性能，而Cursors是过程运算。比如对一个100万行的数据进行查询，游标需要读表100万次，而不使用游标只需要少量几次读取。

6.事务越短越好

SQL Server支持并发操作。如果事务过多过长，或是隔离级别过高，都会造成并发操作的阻塞，死锁。此时现象是查询极慢，同时cup占用率极低。

7.使用try-catch来处理错误异常

SQL Server 2005及以上版本提供对try-catch的支持，语法为：

begin try
----your code
end try
begin catch
--error dispose
end catch

一般情况可以将try-catch同事务结合在一起使用。

begin try
    begin tran
        --select
        --update
        --delete
        --…………
    commit
end try
begin catch
    --if error
    rollback
end catch

====================== 分割线 =======================

『自己的一些调优经验』

1. 少使用游标是个很好的建议，为此，我自己也遇到过一些事故，是游标所造成的，由于，游标是逐行逐行操作的，当记录较多时，经常会遇到超时的情况。

2. 多表join做查询时，查询的字段尽量不要使用case when then else end的语法，或者使用用户函数，例如：

select (case when fType=1 then '是' else '否' end) as fTypeName, dbo.F_GetFullName(fID) as fFullName from Table1 inner join Table2……

当两个表的数据量非常大时，你可以在查询分析器中明显感觉到：直接查询fType和fID与查询上面两个字段的速度，很可能使用了一个case when then就导致超时。

针对这种情况，可以分两种做法：

第一，把一些简单的转换可以放在程序中完成。

第二，如果需要通过ID查询全名或者全称，类似的，可以创建好视图，直接查视图，或者，先把所有的fFullName查出来放到临时表中，直接join临时表（如果这个数据不是很多的话），获得fFullName。

3. 少使用一些嵌套的查询，用临时表缓存中间数据，例如：

select * from Table1

inner join (