MSSQL教程之存储历程编写履历和优化措施

提供用于管理、检查、优化数据库操作的管理工具。存储历程|优化
1、媒介：在经由一段工夫的存储历程开辟以后，写下了一些开辟时分的小结和履历与人人共享，但愿对人人无益，次要是针对Sybase和SQLServer数据库，但别的数据库应当有一些个性。

2、合适读者工具：数据库开辟程序员，数据库的数据量良多，触及到对SP（存储历程）的优化的项目开辟职员，对数据库有浓郁乐趣的人。

3、先容：在数据库的开辟过程当中，常常会碰到庞大的营业逻辑和对数据库的操纵，这个时分就会用SP来封装数据库操纵。假如项目标SP较多，誊写又没有必定的标准，将会影响今后的体系保护坚苦和年夜SP逻辑的难以了解，别的假如数据库的数据量年夜大概项目对SP的功能请求很，就会碰到优化的成绩，不然速率有大概很慢，经由亲自履历，一个经由优化过的SP要比一本性能差的SP的效力乃至高几百倍。

4、内容：

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、***pute，由于这些语句会减轻tempdb的包袱。

ii.制止频仍创立和删除一时表，削减体系表资本的损耗。

iii.在新建一时表时，假如一次性拔出数据量很年夜，那末可使用selectinto取代createtable，制止log，进步速率；假如数据量不年夜，为了和缓体系表的资本，倡议先createtable，然后insert。

iv.假如一时表的数据量较年夜，必要创建索引，那末应当将创立一时表和创建索引的历程放在独自一个子存储过程当中，如许才干包管体系可以很好的利用到该一时表的索引。

v.假如利用到了一时表，在存储历程的最初务势必一切的一时表显式删除，先truncatetable，然后droptable，如许能够制止体系表的较长工夫锁定。

vi.慎用年夜的一时表与其他年夜表的毗连查询和修正，减低体系表包袱，由于这类操纵会在一条语句中屡次利用tempdb的体系表。

d)公道的算法利用：

依据下面已提到的SQL优化手艺和ASETuning手册中的SQL优化内容,分离实践使用,接纳多种算法举行对照,以取得损耗资本起码、效力最高的办法。详细可用ASE调优命令：setstatisticsioon,setstatisticstimeon,setshowplanon等
因此，我们的第一个“米”，就是二进制日志binlog必须是rowbased的。在rowbase下，二进制日志binlog同时记录了更新前后的整行记录。

在select语句中可以使用groupby子句将行划分成较小的组，然后，使用聚组函数返回每一个组的汇总信息，另外，可以使用having子句限制返回的结果集。

原来的计算字段其实和虚拟字段很像。只是管理方面好了而已，性能方面提高不多。但是SQL2005提供了计算字段的持久化，这就提高了查询的性能，但是会加重insert和update的负担。OLTP慎用。OLAP可以大规模使用。

这就引发了对varchar和char效率讨论的老问题。到底如何分配varchar的数据，是否会出现大规模的碎片？

我是一个ERP初学者，对于前台运用基本熟悉，但对于后台SQLServer的运用一点也不懂，特想学习下相关资料。至少懂得一些基本的运用。希望各位能给于建议，小弟再谢过！

再开发调试阶段和OLAP环境中，外键是可以建立的。新版本中加入了SETNULL和SETDEFAULT属性，能够提供能好的级联设置。

这一点很好的加强了profiler的功能。但是提到profiler提醒大家注意一点。windows2003要安装sp1补丁才能启动profiler。否则点击没有反应。

大家注意一点。如下面的例子：