sqlserver存储过程编写经验和优化 1.docx
- 文档编号:29537575
- 上传时间:2023-07-24
- 格式:DOCX
- 页数:4
- 大小:17.62KB
sqlserver存储过程编写经验和优化 1.docx
《sqlserver存储过程编写经验和优化 1.docx》由会员分享,可在线阅读,更多相关《sqlserver存储过程编写经验和优化 1.docx(4页珍藏版)》请在冰豆网上搜索。
sqlserver存储过程编写经验和优化1
SqlServer存储过程编写经验和优化
作者:
佚名发布时间:
2004-11-410:
07:
19|【字】
一、前言:
在经过一段时间的存储过程开发之后,写下了一些开发时候的小结和经验与大家共享,希望对大家有益,主要是针对Sybase和SQLServer数据库,但其它数据库应该有一些共性。
二、适合读者对象:
数据库开发程序员,数据库的数据量很多,涉及到对SP(存储过程)的优化的项目开发人员,对数据库有浓厚兴趣的人。
三、介绍:
在数据库的开发过程中,经常会遇到复杂的业务逻辑和对数据库的操作,这个时候就会用SP来封装数据库操作。
如果项目的SP较多,书写又没有一定的规范,将会影响以后的系统维护困难和大SP逻辑的难以理解,另外如果数据库的数据量大或者项目对SP的性能要求很,就会遇到优化的问题,否则速度有可能很慢,经过亲身经验,一个经过优化过的SP要比一个性能差的SP的效率甚至高几百倍。
四、 内容:
1、开发人员如果用到其他库的Table或View,务必在当前库中建立View来实现跨库操作,最好不要直接使用“databse.dbo.table_name”,因为sp_depends不能显示出该SP所使用的跨库table或view,不方便校验。
2、开发人员在提交SP前,必须已经使用setshowplanon分析过查询计划,做过自身的查询优化检查。
3、高程序运行效率,优化应用程序,在SP编写过程中应该注意以下几点:
a) SQL的使用规范:
i. 尽量避免大事务操作,慎用holdlock子句,提高系统并发能力。
ii. 尽量避免反复访问同一张或几张表,尤其是数据量较大的表,可以考虑先根据条件提取数据到临时表中,然后再做连接。
iii. 尽量避免使用游标,因为游标的效率较差,如果游标操作的数据超过1万行,那么就应该改写;如果使用了游标,就要尽量避免在游标循环中再进行表连接的操作。
iv. 注意where字句写法,必须考虑语句顺序,应该根据索引顺序、范围大小来确定条件子句的前后顺序,尽可能的让字段顺序与索引顺序相一致,范围从大到小。
v. 不要在where子句中的“=”左边进行函数、算术运算或其他表达式运算,否则系统将可能无法正确使用索引。
vi. 尽量使用exists代替selectcount
(1)来判断是否存在记录,count函数只有在统计表中所有行数时使用,而且count
(1)比count(*)更有效率。
vii. 尽量使用“>=”,不要使用“>”。
viii. 注意一些or子句和union子句之间的替换
ix. 注意表之间连接的数据类型,避免不同类型数据之间的连接。
x. 注意存储过程中参数和数据类型的关系。
xi. 注意insert、update操作的数据量,防止与其他应用冲突。
如果数据量超过200个数据页面(400k),那么系统将会进行锁升级,页级锁会升级成表级锁。
b) 索引的使用规范:
i. 索引的创建要与应用结合考虑,建议大的OLTP表不要超过6个索引。
ii. 尽可能的使用索引字段作为查询条件,尤其是聚簇索引,必要时可以通过indexindex_name来强制指定索引
iii. 避免对大表查询时进行tablescan,必要时考虑新建索引。
iv. 在使用索引字段作为条件时,如果该索引是联合索引,那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引,否则该索引将不会被使用。
v. 要注意索引的维护,周期性重建索引,重新编译存储过程。
c) tempdb的使用规范:
i. 尽量避免使用distinct、orderby、groupby、having、join、cumpute,因为这些语句会加重tempdb的负担。
ii. 避免频繁创建和删除临时表,减少系统表资源的消耗。
iii. 在新建临时表时,如果一次性插入数据量很大,那么可以使用selectinto代替createtable,避免log,提高速度;如果数据量不大,为了缓和系统表的资源,建议先createtable,然后insert。
iv. 如果临时表的数据量较大,需要建立索引,那么应该将创建临时表和建立索引的过程放在单独一个子存储过程中,这样才能保证系统能够很好的使用到该临时表的索引。
v. 如果使用到了临时表,在存储过程的最后务必将所有的临时表显式删除,先truncatetable,然后droptable,这样可以避免系统表的较长时间锁定。
vi. 慎用大的临时表与其他大表的连接查询和修改,减低系统表负担,因为这种操作会在一条语句中多次使用tempdb的系统表。
d) 合理的算法使用:
根据上面已提到的SQL优化技术和ASETuning手册中的SQL优化内容,结合实际应用,采用多种算法进行比较,以获得消耗资源最少、效率最高的方法。
具体可用ASE调优命令:
setstatisticsioon,setstatisticstimeon,setshowplanon等。
浅析Oracle和SqlServer存储过程的调试、出错处理
在大型数据库中,因为开发的需要,经常地需要调用Procedure,开发Procedure在Oracle和Sqlserver上因为语法不同,而有所区别,但是在调试上,都是比较的不容易,尤其是在一些错误处理上。
首先说调试:
1、对于Oracle的调试,可以借助于第三方的工具,比如Pl/SqlDeveloper,我在用的版本是6.0.5.926;首先对该Procedure右键处理,添加“adddebuginformation”,然后选择“test”,打开新的测试窗口,在下方对应的输入输出变量地方,添加相应的测试数据,注意:
这里的数据输入,不需要引号,输出参数不需要输入然后点击“startdebugger”,或者按F9,进行测试,可以选择测试的步骤如“stepinto”等,然后可以在下面的script窗口看到,中间变量;
2、对于SqlServer调试,我还没有找到比较好的第三方工具,目前是采用将中间的变量值或者sql语句插入到另一个表中,或者直接print出来的方式。
第一种需要借助exec('')方法,注意里面的取变量值的写法,可参考下面的例子:
exec('updateSupplyplanLacksetCompletedate=GetDate()
fromsupplyPlanLackawhere'@ssTmp'and
'@iCompleteQty'>=(selects.RequestQty*a.RationQtyasReqQty
fromBalancea,SupplyPlanswherea.Vehicle=s.Vehicle
anda.Part=s.Partanda.SupplyPlanNo=s.SupplyPlanNo
and'@ssTmp')')
在print中,需要注意类型的转换,一般是借用convert(varchar(11),@spNOTo)方法来实现,否则会提示类型转换错误!
3、错误处理上,对于Sqlserver可以采用开始自定义变量,然后根据不同判断,改变该值然后推出的方法来处理,见下例
set@exec_num=0
if(@spNOFrom=0or@spNOTo=0or@reuseUser=NULL)
begin
set@exec_num=1
gotothe_end
end
the_end:
return
或者是这种:
setnocounton
if(@propertyisnull)or(@property='')
begin
raiserror('Mustspecifyapropertyname.',-1,-1)
return
(1)
end
或者是对该错误全局变量@@error数值的判断上。
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- sqlserver存储过程编写经验和优化 sqlserver 存储 过程 编写 经验 优化
![提示](https://static.bdocx.com/images/bang_tan.gif)