SQL Server存储历程编写和优化办法[MSSQL防范]

　1、合适读者对象:数据库开辟程序员,数据库的数据量很多,触及到对SP（存储历程）的优化的项目开辟人员,对数据库有稠密爱好的人. 　

　　2、介绍:在数据库的开辟历程中,常常会碰到复杂的业务逻辑和对数据库的操作,这个时刻就会用SP来封装数据库操作.假如项目的SP较多,书写又没有一定的标准,将会影响今后的系统保护艰难和大SP逻辑的难以理解,别的假如数据库的数据量大大概项目对SP的性能要求很,就会碰到优化的问题,不然速度有大概很慢,经过亲身经验,一个经过优化过的SP要比一本性能差的SP的效率乃至高几百倍. 　

　　3、内容: 　

　　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 等.
　　　　　 数码佳构,购物零风险 　建站_花小钱做大事业
　　　　   更小投入满意更多需求    DC新品,震震有礼