SQLServer存储过程开发与优化指南

一、前言：在长期的存储过程开发实践中，我总结了一些实用的经验和技巧，希望能对广大数据库开发人员有所帮助。本文主要针对SQL Server数据库，但其他数据库系统也可参考。

二、适用对象：数据库开发工程师，特别是那些处理大量数据且需要优化存储过程的项目开发人员，以及对数据库技术有浓厚兴趣的技术爱好者。

三、背景介绍：在数据库开发中，复杂的业务逻辑和频繁的数据库操作通常通过存储过程（SP）来实现。如果项目中的存储过程数量较多且缺乏规范，将导致系统维护困难和逻辑复杂。此外，对于大数据量或高性能要求的项目，存储过程的优化尤为重要。经过实践验证，优化后的存储过程性能可提升数百倍。

四、主要内容：

1. 跨库操作规范：如果需要访问其他数据库的表或视图，建议在当前数据库中创建视图来实现跨库操作，避免直接使用“database.dbo.table_name”。这样做可以方便地使用sp_depends工具检查存储过程依赖的表或视图。

2. 查询优化检查：在提交存储过程之前，开发人员应使用set showplan on分析查询计划，并进行必要的查询优化。

3. 提高运行效率的技巧：

a) SQL使用规范：

i. 尽量避免大事务操作，谨慎使用holdlock子句，以提高系统的并发能力。

ii. 避免重复访问同一张或几张表，特别是数据量较大的表。可以考虑先将数据提取到临时表中，再进行连接操作。

iii. 尽量避免使用游标，因为游标的效率较低。如果游标操作的数据超过1万行，应考虑改写。如果使用了游标，避免在游标循环中进行表连接操作。

iv. 注意where子句的写法，根据索引顺序和范围大小合理安排条件子句的顺序，使字段顺序与索引顺序一致，范围从大到小。

v. 不要在where子句的等号左侧进行函数、算术运算或其他表达式运算，这可能导致索引无法正确使用。

vi. 尽量使用exists代替select count(1)来判断记录是否存在。count函数仅在统计表中所有行数时使用，且count(1)比count(*)更高效。

vii. 尽量使用“>=”，而不是“>”。

viii. 注意or子句和union子句之间的替换。

ix. 注意表连接的数据类型，避免不同数据类型之间的连接。

x. 注意存储过程中参数和数据类型的关系。

xi. 注意insert、update操作的数据量，防止与其他应用冲突。如果数据量超过200个数据页面（400k），系统将进行锁升级，页级锁会升级为表级锁。

b) 索引使用规范：

i. 索引的创建应与应用需求相结合，建议大型OLTP表的索引不超过6个。

ii. 尽可能使用索引字段作为查询条件，尤其是聚簇索引。必要时可通过index index_name强制指定索引。

iii. 避免对大表进行全表扫描，必要时考虑新建索引。

iv. 使用联合索引时，必须使用到该索引的第一个字段作为条件，才能确保系统使用该索引。

v. 定期维护索引，周期性重建索引并重新编译存储过程。

c) tempdb使用规范：

i. 尽量避免使用distinct、order by、group by、having、join、compute等语句，这些操作会增加tempdb的负担。

ii. 避免频繁创建和删除临时表，减少系统表资源的消耗。

iii. 新建临时表时，如果一次性插入大量数据，建议使用select into代替create table，以减少日志开销并提高速度。如果数据量较小，建议先create table，然后insert。

iv. 如果临时表的数据量较大且需要建立索引，应将创建临时表和建立索引的过程放在单独的子存储过程中，以确保系统能有效利用临时表的索引。

v. 使用临时表后，在存储过程的最后显式删除所有临时表，先truncate table，然后drop table，以避免系统表长时间锁定。

vi. 谨慎使用大临时表与其他大表的连接查询和修改操作，以减轻系统表的负担，因为这种操作会在一条语句中多次使用tempdb的系统表。

d) 合理的算法选择：

结合实际应用和ASE Tuning手册中的SQL优化内容，采用多种算法进行比较，以获得资源消耗最少、效率最高的方法。具体可用ASE调优命令：set statistics io on, set statistics time on, set showplan on等。

来源：http://www.weste.net