快捷搜索:

SQL Server存储过程编写和优化措施 - 存储过程编写

一、得当读者工具:数据库开拓法度榜样员,数据库的数据量很多,涉及到对SP(存储历程)的优化的项目开拓职员,对数据库有浓厚兴趣的人。

二、先容:在数据库的开拓历程中,常常会碰到繁杂的营业逻辑和对数据库的操作,这个时刻就会用SP来封装数据库操作。假如项目的SP较多,书写又没有必然的规范,将会影响今后的系统掩护艰苦和大年夜SP逻辑的难以理解,别的假如数据库的数据量大年夜或者项目对SP的机能要求很,就会碰到优化的问题,否则速率有可能很慢,颠末切身履历,一个颠末优化过的SP要比一个机能差的SP的效率以致高几百倍。

三、内容:

1、开拓职员假如用到其他库的Table或View,务必在当前库中建立View来实现跨库操作,最好不要直接应用“databse.dbo.table_name”,由于sp_depends不能显示出该SP所应用的跨库table或view,未方便校验。

2、开拓职员在提交SP前,必须已经应用set showplan on阐发过查询计划,做过自身的查询优化反省。

3、高法度榜样运行效率,优化利用法度榜样,在SP编写历程中应该留意以下几点:

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. 避免对大年夜表查询时进行table scan,需要时斟酌新建索引。

iv. 在应用索引字段作为前提时,假如该索引是联合索引,那么必须应用到该索引中的第一个字段作为前提时才能包管系统应用该索引,否则该索引将不会被应用。

v. 要留意索引的掩护,周期性重修索引,从新编译存储历程。

c)tempdb的应用规范:

i. 只管即便避免应用distinct、order by、group by、having、join、cumpute,由于这些语句会加重tempdb的包袱。

ii. 避免频繁创建和删除临时表,削减系统表资本的耗损。

iii. 在新建临时表时,假如一次性插入数据量很大年夜,那么可以应用select into代替create table,避免log,前进速率;假如数据量不大年夜,为了缓和系统表的资本,建议先create table,然后insert。

iv. 假如临时表的数据量较大年夜,必要建立索引,那么应该将创建临时表和建立索引的历程放在零丁一个子存储历程中,这样才能包管系统能够很好的应用到该临时表的索引。

v. 假如应用到了临时表,在存储历程的着末务必将所有的临时表显式删除,先truncate table,然后drop table,这样可以避免系统表的较长光阴锁定。

vi. 慎用大年夜的临时表与其他大年夜表的连接查询和改动,减低系统表包袱,由于这种操作会在一条语句中多次应用tempdb的系统表。

d)合理的算法应用:

根据上面已提到的SQL优化技巧和ASE Tuning手册中的SQL优化内容,结合实际利用,采纳多种算法进行对照,以得到耗损资本起码、效率最高的措施。详细可用ASE调优敕令:set statistics io on, set statistics time on , set showplan on 等。

您可能还会对下面的文章感兴趣: