关于存储过程优化

存储过程编写经验和优化措施
阅读提示:本文介绍MS SQL的存储过程编写经验和优化措施
一、适合读者对象:数据库开发程序员,数据库的数据量很多,涉及到对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. 尽量避免使用游标,因为游标的效率较差,如果游标操作的数据超过万行,那么就应该改写;如果使用了游标,就要尽量避免在游标循环中再进行表连接的操作。
 iv. 注意where字句写法,必须考虑语句顺序,应该根据索引顺序、范围大小来确定条件子句的前后顺序,尽可能的让字段顺序与索引顺序相一致,范围从大到小。
 v. 不要在where子句中的“
=”左边进行函数、算术运算或其他表达式运算,否则系统将可能无法正确使用索引。
 vi. 尽量使用exists代替select
count(1)来判断是否存在记录,count函数只有在统计表中所有行数时使用,而且count(1)比count(*)更有效率。
 vii. 尽量使用“
>=”,不要使用“>”。
 viii. 注意一些or子句和union子句之间的替换
 ix. 注意表之间连接的数据类型,避免不同类型数据之间的连接。
 x. 注意存储过程中参数和数据类型的关系。
 xi. 注意insert、update操作的数据量,防止与其他应用冲突。如果数据量超过个数据页面(k),那么系统将会进行锁升级,页级锁会升级成表级锁。
b)索引的使用规范:
 i. 索引的创建要与应用结合考虑,建议大的OLTP表不要超过个索引。
 ii. 尽可能的使用索引字段作为查询条件,尤其是聚簇索引,必要时可以通过index index_name来强制指定索引
 iii. 避免对大表查询时进行table scan,必要时考虑新建索引。
 iv. 在使用索引字段作为条件时,如果该索引是联合索引,那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引,否则该索引将不会被使用。
 v. 要注意索引的维护,周期性重建索引,重新编译存储过程。
c)tempdb的使用规范:
 i. 尽量避免使用distinct、
order bygroup byhavingjoin、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 等。

存储过程的安全及性能优化 存储过程分类  系统存储过程  自定义存储过程  SQL Server使用者编写的存储过程  扩展存储过程  动态链接库(DLL)函数的调用看,主要用于客户端和服务器端之间进行通信  exec master..xp_cmdshell 'dir *.exe' -- 执行目录命令查询[sql2005\sql2008]  exec master..xp_fixeddrives --列出硬盘分区各自可用空间  xp_regwrite根键,子键,值名,值类型,值【sql2008拒绝访问】  写入注册表,例如:  exec master..db.xp_regwrite 'HKEY_LOCAL_MACHINE','SOFTWARE\Microsoft\Windows\CurrentVersion\run','TestValueName','reg_sz','hello'  xp_regdeletevalue 根键,子键,值名【sql2008拒绝访问】  删除注册表某个值  xp_regdeletekey键,值【sql2008拒绝访问】  删除该键下包括的所有值 xp_cmdshell语法  xp_cmdshell {'command_string'} [,no_output]  command_string是在操作系统命令行解释器上执行的命令字符串。command_string数据类型为varchar(255)或者nvarchar(4000),没有默认值  no_output为可选参数,可以控制是否想客户端返回信息  该存储过程一般情况下被禁用的,需要手动开启使用,如下:  exec sp_configure 'show advanced options',1  go   reconfigure  go   exec sp_configure 'xp_cmdshell',1—1表示启用,0表示禁用  go   reconfigure  go 删除xp_cmdshell SQL SERVER200删除xp_cmdshell use master exec sp_dropextendedproc 'xp_cmdshell' go SQL SERVER2005以上禁用xp_cmdshell,但不能删除掉 exec sp_configure 'xp_cmdshell',0 —1表示启用,0表示禁用  go reconfigure --让sp_configurre立即生效  go  exec sp_configure 'show advanced options',0  go   reconfigure  go --注意:SQL SERVER2008考虑安全性很多存储过程直接被拒绝访问 恢复/启用扩展存储过程 SQLServer2000 use master exec sp_addextendedproc xp_cmdshell,'xplog70.dll' go SQL Server2005或SQL Server2008启用xp_cmdshell  exec sp_configure 'show advanced options',1  go   reconfigure  go   exec sp_configure 'xp_cmdshell',1—1表示启用,0表示禁用  go   reconfigure  go 扩展存储过程的定义  扩展存储过程是SQL Server中的另一类存储过程,它是以其它语言编写的外部程序,是以动态链接库(DLL)形式存储在服务器上,最终SQLServer就可以动态加载并执行它们  编写好后使用SQLServer的固定角色sysadmin注册该扩展存储过程,并将执行权限授予其它用户,这个扩展存储过程只能添加到master数据库。  在编写扩展存储过程中可能要用到某些系统存储过程,这些系统存储过程如下: 利用OLE自动化存储过程调用dll 1.创建类库程序集 namespace PB_ExtendProcedure { public class ExtendProcedure { public string SayHi() { return "hello world"; } } } 2.生成动态链接库并注册到系统中 2.1.生成动态链接库使用VS2010命令行工具 使用sn命令生成一个强命名文件: sn -k helpkey.snk 使用csc生成dll csc /t:library /keyfile:helperkey.snk ExtendProcedure.cs 向系统注册这个dll regasm /tlb:ExtendProcedure.tlb ExtendProcedure.dll /codebase 2.2.在SQL Server中编写扩展存储过程 --sp_OACreate --sp_OAMethod --sp_OADestroy --sp_OAGetErrorInfo 流程: DECLARE @object int --返回创建的对象 DECLARE @hr int --过程返回值 DECLARE @return varchar(255) --dll方法的返回值 DECLARE @src varchar(255),@desc varchar(255) ---过程的错误原因、描述 --1.创建对象 EXEC @hr = sp_OACreate 'PB_ExtendProcedure.ExtendProcedure',@object out IF @hr0 BEGIN EXEC sp_OAGetErrorInfo @object,@src,@desc out select hr = convert(varchar(4),@hr),Source=@src,Description=@desc return END --2.调用方法 EXEC @hr = sp_OAMethod @object,'SayHi',@return out IF @hr0 BEGIN EXEC sp_OAGetErrorInfo @object,@src,@desc out select hr = convert(varchar(4),@hr),Source=@src,Description=@desc return END print @return --3.销毁对象实例 EXEC @hr = sp_OADestroy @object IF @hr0 BEGIN EXEC sp_OAGetErrorInfo @object,@src,@desc out select hr = convert(varchar(4),@hr),Source=@src,Description=@desc return END 注意:默认情况sqlserver2008是禁止调用ole自动化存储过程的,解决方法如下: sp_configure 'show advanced options',1 go reconfigure go sp_configure 'ole automation procedures',1 go reconfiugre go 加密存储过程 创建加密存储过程语法 CREATE PROCEDURE WITH ENCRYPTION AS 存储过程执行机制 语法阶段:指创建存储过程时,系统检查其创建语句语法正确性的过程。若语法检查通过则系统将该存储过程存储在当前数据库的sys.sql_modules目录视图当中 解析阶段:指某个存储过程首次执行时,查询处理器从sys.sql_modules目录视图中读取该存储过程的文本并且检查该过程引用的对象是否存在的过程。 编译阶段:指分析存储过程和生成存储过程执行计划的过程。执行计划是来描述存储过程执行过程的。查询优化器是在分析完存储过程之后将生成的执行计划存储存储过程高速缓冲存储器中,此后每次调用已经创建的存储过程时将直接执行不再需要编译,这样就可以提高程序的运行性能。 执行阶段:指执行驻留在存储过程高速缓冲存储区中的存储过程执行计划的过程。 了解SQL Server Profiler SQL Server Profiler是图形化实时监视工具 能帮助系统管理员监视数据库和服务器行为,比如死锁数量、致命错误 跟踪T-SQL语句和存储过程 通常使用Profiler监视重要事件: 登录连接的失败 成功或断开连接 delete、insert、update命令 存储过程开始或结束 存储过程中的每一条语句 写入sql server错误日志的错误 打开游标 向数据对象添加或释放锁 Profiler事件 SQL Server Profiler里Standard模板的事件类 数据库引擎优化顾问介绍 引擎优化顾问提供数据库系统的优化建议,经过管理员修改达到优化目的 引擎优化顾问分析数据库的工作负荷【Sql Server Profiler的跟踪文件或者表】和物理实现 引擎优化顾问提出合理的物理设计结构以降低工作负荷的开销 数据库引擎优化顾问介绍 引擎优化顾问提供数据库系统的优化建议,经过管理员修改达到优化目的 引擎优化顾问分析数据库的工作负荷【Sql Server Profiler的跟踪文件或者表】和物理实现 引擎优化顾问提出合理的物理设计结构以降低工作负荷的开销 提高存储过程性能方法 存储过程里面包含很多语句,我们真正要提高性能的是存储过程里面这些语句  默认情况存储过程会返回每条语句执行后的影响行数,如果不需要这些信息的话,可以使用SET NOCOUNT ON来终止这种返回行为,这样可以避免客户端和服务器之间多次进行信息传递的过程。  减少可选参数:频繁使用可选参数是很容易降低性能的。  优化SQL语句  避免频繁访问同一张或多张表,尤其是数据量大的表  尽量避免大事务操作  尽量避免使用游标,游标效率很差,如果使用游标就不要在游标的循环中使用表连接操作  注意where语句的写法,应该根据索引的顺序、范围的顺序、范围的大小来确定条件子句的前后顺序,尽量让字段顺序与索引顺序保持一致  尽量使用exists代替select count()判断是否存在记录  注意表之间连接的数据类型  先写DDL,再写DML: 当DML先于DDL执行的时候,SQL Server会重新编译存储过程,因为DML引用了DDL中的一些内容,也就是所谓的一些表。这个时候SqlServer需要统计由DDL定义的一些对象的变化。以此来创建DML的执行计划,如果将DDL放在前面那么这种编译只需要以此就可以了。  合理使用索引: 根据实际查询需求来创建索引 尽量使用索引字段做查询条件 尽量避免对大数据量表进行全表扫描,可考虑新建索引  合理使用tempdb系统表 尽量避免使用distinct、order by、group by、having、join语句--这些语句加重tempdb的负担  避免频繁创建和删除临时表  临时表中插入数据过大,可使用select into 代替 create table  使用了临时表,要在存储过程最后显式删除  避免使用大临时表与其他大数据量表的连接查询和修改
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值