SQLServer存储过程编写经验和优化

1.前言:经过一段时间的存储过程开发，我写下了一些开发的总结和经验与大家分享，希望对你有所裨益，主要是针对Sybase和SQL Server数据库，但其他数据库应该也有一些共性。
二、适合读者:数据库开发程序员，参与SP(存储过程)优化的项目开发人员，对数据库有浓厚兴趣的人，数据库中有大量数据。

三。简介:在数据库开发过程中，经常会遇到复杂的业务逻辑和数据库操作。此时，SP将用于封装数据库操作。如果项目中有很多SP，没有一定的编写规范，会影响以后系统维护的难度和理解大型SP的逻辑的难度。另外，如果数据库的数据量很大或者项目对SP的性能要求很高，就会遇到优化的问题，否则速度可能会很慢。亲身体验后，一个优化后的SP的效率甚至比一个性能不佳的SP高上百倍。

四。内容:

1。如果开发者使用其他库的表或视图，必须在当前库中建立视图才能实现跨库操作，不要直接使用“database . dbo . table _ name”，因为SP_depends无法显示本SP使用的跨库表或视图，不方便验证。

2。在提交SP之前，开发人员必须已经使用set showplan on分析了查询计划，并且自己检查了查询优化。

3。为了提高程序运行效率，优化应用程序，在SP编写过程中需要注意以下几点:

a) SQL使用规范:

一、尽量避免大型事务操作，慎用holdlock子句，提高系统的并发性。

二。尽量避免重复访问同一个表或几个表，尤其是数据量大的表。可以考虑先根据条件将数据提取到临时表中，再进行连接。

三。尽量避免使用游标，因为游标的效率很差。游标操作的数据超过10000行就要重写；如果使用游标，必须避免游标循环中的表连接操作。

四。注意where句的写法。必须考虑句子的顺序。条件子句的顺序应根据索引顺序和范围大小确定，字段顺序应尽可能与索引顺序一致，范围从大到小。

五、不要在where子句中的“=”左侧执行函数、算术运算或其他表达式运算，否则系统可能无法正确使用索引。

六。尝试用exists代替select count(1)来判断是否有记录。count函数仅在统计表中有所有行时使用，count(1)比count(*)更有效。

七。尽量用“> =”代替“>”。

八。注意or子句和union子句之间的一些替换。

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

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

xi。注意插入和更新操作的数据量，防止与其他应用程序冲突。如果数据量超过200个数据页(400k)，系统将升级锁，页级锁将升级为表级锁。

b)索引的使用说明:

一、索引的创建要结合应用来考虑。建议大型OLTP表不要超过6个索引。

二。尽可能使用索引字段作为查询条件，尤其是聚集索引。如有必要，可以使用index index_name来强制指定索引。

三。查询大型表时避免表扫描，并考虑在必要时创建新索引。

四。将索引字段作为条件时，如果索引是联合索引，则必须将索引中的第一个字段作为条件，以保证系统使用该索引，否则不会使用该索引。

五、注意索引的维护，定期重建索引，重新编译存储过程。

c)tempdb的使用规范:

一、尽量避免使用distinct、order by、group by、having、join、cumpute，因为这些语句会增加tempdb的负担。

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

三。创建临时表时，如果一次插入大量数据，可以用select into代替create table，避免日志记录，加快速度；如果数据量不大，为了缓解系统表的资源，建议先创建表，然后插入。

四。如果临时表的数据量很大，需要进行索引，那么创建临时表和索引的过程应该放在一个单独的子存储过程中，这样才能保证系统能够很好地利用临时表的索引。

v .如果使用临时表，则必须在存储过程结束时显式删除所有临时表。首先截断表，然后删除表，这样可以避免系统表的长期锁定。

六。谨慎使用大型临时表与其他大型表的联接查询和修改，以减轻系统表的负担，因为此操作将在一条语句中多次使用tempdb的系统表。

d)合理算法使用:

根据前面提到的SQL优化技术和ASE调优手册中关于SQL优化的内容，结合实际应用，采用多种算法进行比较，从而得出消耗资源最少且高效的方法。可用的ASE调优命令:set statistics io on、set statistics time on、set show plan on等。