如何写出高效的SQL脚本.docx

如何写出高效的SQL脚本.docx

《如何写出高效的SQL脚本.docx》由会员分享，可在线阅读，更多相关《如何写出高效的SQL脚本.docx（12页珍藏版）》请在冰豆网上搜索。

如何写出高效的SQL脚本

如何写出高效的ＳＱＬ脚本：

如何写出高效的ＳＱＬ脚本《二》

1.        设计如何满足ＳＡＲＧ形式的ＳＱＬ脚本

SARG的定义：

用于限制搜索的一个操作，因为它通常是指一个特定的匹配，一个值得范围内的匹配或者两个以上条件的AND连接。

［可以理解为索引扫描］形式如下：

列名操作符<常数或变量>

或

<常数或变量>操作符列名

列名可以出现在操作符的一边，而常数或变量出现在操作符的另一边。

如：

Name=’ATA’

数量>5000

5000<数量

Name=’ATA’and数量>5000

如果一个表达式不能满足SARG的形式，那它就无法限制搜索的范围了，也就是SQLSERVER必须对每一行都判断它是否满足WHERE子句中的所有条件。

所以一个索引对于不满足SARG形式的表达式来说是无用的

2.        Ｌｉｋｅ

Like语句是否属于SARG取决于所使用的通配符的类型

如：

namelike‘ATA%’，这就属于SARG

而：

namelike‘%ATA’,就不属于SARG。

原因是通配符%在字符串的开头使得索引无法使用。

3.        ＯＲ　和　ＩＮ

or会引起全表扫描

Name=’ATA’and数量>5000符号SARG，而：

Name=’ATA’or数量>5000则不符合SARG。

使用or和Ｉｎ会引起全表扫描

4.     非操作符、函数引起的不满足SARG形式的语句

不满足SARG形式的语句最典型的情况就是包括非操作符的语句，如：

NOT、!

=、<>、!

<、!

>、NOTEXISTS、NOTIN、NOTLIKE，isnull,notnull等，另外还有函数。

下面就是几个不满足SARG形式的例子：

ABS（数量）<5000

Namelike‘%ATA’

有些表达式，如：

WHERE数量*2>5000

SQLSERVER也会认为是SARG，SQLSERVER会将此式转化为：

WHERE数量>2500/2

不推荐这样使用，因为有时SQLSERVER不能保证这种转化与原始表达式是完全等价的。

5.      函数charindex（）、前面加通配符%的LIKE，后面加%的效率比较

如果在LIKE前面加上通配符%，那么将会引起全表扫描，所以其执行效率是低下的。

用函数charindex（）来代替LIKE速度会有大的提升的说法不对的，测试如下：

1.     selectfcandidateid,fcandidatenamefromtcandidatewherefcandidatenamelike'Tim%'

用时：

36秒，记录结果数：

２００万

2.     selectfcandidateid,fcandidatenamefromtcandidate

where charindex（'Tim',fcandidatename）>0

用时：

47秒，记录结果数：

２００万

3.     selectfcandidateid,fcandidatenamefromtcandidate

where fcandidatenamelike'%Tim%'

用时：

45秒，记录结果数：

２００万

通过以上3个例子可以看出，再使用Like的时候，后面加“Tim%”符合SARG规则，用时明显少于后两种，后两种的性能基本上差不多

如果非的模糊，比如：

substring（fcandidatename,1,1）=’A’,那么可以考虑这样：

fcandidatenamelike‘A%’来代替（因为这样用的是索引扫描，不是表扫描）

如何写出高效的ＳＱＬ脚本《二》

6.      字段提取要按照“需多少、提多少”的原则，避免“select*“

下面我来做一个测试：

a）     selectfcandidateid,fcandidatenamefromtcandidatewhere fcandidatenamelike'Tim%'

用时：

35秒　记录结果数：

２００万

b）     selectfcandidateid,fcandidatename,fCredentialsID,fbirthdayfromtcandidatewhere fcandidatenamelike'Tim%'

用时：

51秒记录结果数：

２００万

c）     selectfcandidateidfromtcandidatewhere fcandidatenamelike'Tim%'

用时：

23秒　记录结果数：

２００万

由此看来，我们每少提取一个字段，数据的提取速度就会有相应的提升，当然提升的速度还要看您舍弃的字段的大小来判断。

7.     orderby的使用，用那些字段作为排序效率比较高

1.     使用具有族索引或者primarykey的字段排序。

selectfcandidateid,fCredentialsID,fcandidatename,fbirthday,fRegionIDfromtcandidatewhere fcandidatenamelike'Tim%'orderbyfcandidateiddesc

用时：

45秒记录结果数：

２００万

2.     使用数字的字段（既不是primarykey,也不是族索引、foreignkey）

selectfcandidateid,fCredentialsID,fcandidatename,fbirthday,fRegionIDfromtcandidatewhere fcandidatenamelike'Tim%'orderbyfRegionIDdesc

用时：

1分15秒　记录结果数：

２００万

3.      使用字符串的字段[数字字符串]（既不是primarykey,也不是族索引、foreignkey）

selectfcandidateid,fCredentialsID,fcandidatename,fbirthday,fRegionIDfromtcandidatewhere fcandidatenamelike'Tim%'orderbyfCredentialsIDdesc

用时：

1分22秒　记录结果数：

２００万

4.      使用字符串[字母组成的字符串]

selectfcandidateid,fCredentialsID,fcandidatename,fbirthday,fRegionIDfromtcandidatewhere fcandidatenamelike'Tim%'orderbyfcandidatenamedesc

用时：

1分34秒　记录结果数：

２００万

5.      使用日期字段

selectfcandidateid,fCredentialsID,fcandidatename,fbirthday,fRegionIDfromtcandidatewhere fcandidatenamelike'Tim%'orderbyfbirthdaydesc

用时：

1分9秒　记录结果数：

２００万

从以上可以看出，使用族索引、Primarykey的字段进行排序，速度是比较快的，另外，如果表有foreignkey的字段，在排序的时候，可以优先考虑这些字段。

以上进行了数字、数字字符串、字母字符串、日期的排序，关于性能大家可以参看以上的测试结果，测试的结果日期字段的排序比数字的要高，这个可以在多测试一下类似的数据量，比较一下到底数字和日期字段到底哪个速度快些。

另外的几种情况，大家就一目了然了。

同时，按照某个字段进行排序的时候，无论是正序还是倒序，速度是基本相当的。

8.    插入大的二进制值到Image列需要注意的

如果要插入大的二进制值到Image列，使用存储过程，千万不要用内嵌INsert来插入。

因为这样应用程序首先将二进制值转换成字符串（尺寸是它的两倍），服务器收到字符后又将他转换成二进制值.

存储过程就没有这些动作:

在前台调用这个存储过程传入二进制参数，这样处理速度明显改善。

9.      高效的TOＰ

事实上，在查询和提取超大容量的数据集时，根据需要提取一次需要的数据，如果允许尽量使用ｔｏｐ语句。

如：

selecttop50000*from（

selecttop2000000fcandidateid,fcandidatename,fbirthdayfromtcandidate

wherefcandidatenamelike'Tim%'

orderbyfcandidateiddesc）asa

用时：

1179毫秒

大家可以仔细看上面的查询语句，可以和以上的语句项比较，就可以知道top的效率有多高了

这条语句，从理论上讲，整条语句的执行时间应该比子句的执行时间长，但事实相反。

因为，子句执行后返回的是2000000条记录，而整条语句仅返回50000条语句，所以影响数据库响应时间最大的因素是物理I/O操作。

而限制物理I/O操作此处的最有效方法之一就是使用TOP关键词了。

TOP关键词是SQLSERVER中经过系统优化过的一个用来提取前几条或前几个百分比数据的词。

所以经过优化算法的ＴＯＰ效率就很高了

10.如何使用ＳＱＬ的函数注意的问题

1.      ＳＱＬ的函数在ＳＱＬ脚本中不同的位置消耗的成本就不一样

selectfcandidateid,fCredentialsID,fcandidatename,fbirthday,fRegionID

fromtcandidatewhere dateadd（year,5,fbirthday）='1981/09/08'

CPU:

2079 用时：

25317毫秒 记录：

1000000

2.     selectfcandidateid,fCredentialsID,fcandidatename,fbirthday,fRegionIDfromtcandidatewhere fbirthday=dateadd（year,-5,'1981/09/08'）

CPU:

1219 用时：

21666毫秒记录结果：

1000000

所以从以上可以看出，不同的SQL函数方的位置不一样，性能和消耗的成本也不一样，总体原则把ＳＱＬ函数放到条件的右边性能消耗的成本等比较低。

3.     注意使用DISTINCT，在没有必要时不要用，它同UNION一样会使查询变慢，如果确认结果集中没有重复的记录，请不要轻易用ｄｉｓｔｉｃｔ

11.    表和临时表的用法

1.        一般情况下尽量使用表变量而不用临时表，为何推荐表变量，请看下面的解释：

●        表变量（如局部变量）具有明确定义的范围，在该范围结束时会自动清除这些表变量。

●        与临时表相比，表变量导致存储过程的重新编译更少。

●        涉及表变量的事务仅维持表变量上更新的持续时间。

因此，使用表变量时，需要锁定和记录资源的情况更少。

因为表变量具有有限的范围并且不是持久性数据库的一部分，所以事务回滚并不影响它们。

2.        什么时候使用表变量而不使用临时表

•插入到表中的行数。

•从中保存查询的重新编译的次数。

•查询类型及其对性能的指数和统计信息的依赖性。

3.        关于表变量的缺陷，大家可以到msdn上搜索一下（tablevariable）

如何写出高效的ＳＱＬ脚本《三》

12.    视图

尽量少用视图，它的效率相对比较低。

对视图操作比直接对表操作慢,可以用storedprocedure来代替她。

特别的是不要用视图嵌套,嵌套视图增加了寻找原始资料的难度。

我们看视图的本质：

它是存放在服务器上的被优化好了的已经产生了查询规划的SQL。

对单个表检索数据时，不要使用指向多个表的视图，直接从表检索或者仅仅包含这个表的视图上读，否则增加了不必要的开销,查询受到干扰.为了加快视图的查询，MsSQL增加了视图索引的功能

如果建立的视图数据量比较大，操作比较频繁，则推荐使用索引的视图.

13.    GROUPBY   Having

一般在GROUPBY个HAVING字句之前就能剔除多余的行，所以尽量不要用它们来做剔除行的工作。

他们的执行顺序应该如下最优：

select的Where字句选择所有合适的行，GroupBy用来分组个统计行，Having字句用来剔除多余的分组。

这样GroupBy个Having的开销小，查询快.对于大的数据行进行分组和Having十分消耗资源。

如果GroupBY的目的不包括计算，只是分组，那么用Distinct更快

14.    存储过程中的参数定义的数据类型尽量和条件中的数据类型一致

1）     selectfcandidateIDfromtcandidatewherefcredentialstype='1'

2）     select fcandidateIDfromtcandidatewhere fnation=1

Fcredentialstype字段在ｔｃａｎｄｉｄａｔｅ表中是整型，那么如果第一种方式ｓｑｌ就有一个隐式的函数转换ｃａｓｔ（‘１＇　ａｓ　ｉｎｔ），花销时间。

如果一个字段是ｆｌｏａｄ类型的，比如有一个字段ｆｓｃｏｒｅ是ｆｌｏａｔ类型，那么条件语句就应该这样写（假如：

是选出６０分以上的学生），就因该这样写：

出fscore＞６０．０.而不是　fscore>60

这个有一个隐式的转换。

虽然这些都是小事，也许如果每一步都注意的话，增提性能就会提高的　：

）

15.    测试结果如下：

1.        多表选择Tcandidate和Tcandidatedetail表各有数据3040000条记录

selectt.fcandidateID,t.fcandidatename,d.fhealth,d.faddress

fromtcandidateast

innerjointcandidatedetailasd

ont.fcandidateid=d.fcandidateid

andt.fcandidatename='tim1000322'

2.        根据姓名，查询该用户的其它的信息

3.        查询结果平均用时：

19秒

---------------------------------------------------------------------------------------------------------------

1.        　多表选择Tcandidate和Tcandidatedetail表各有数据3040000条记录

selectt.fcandidateID,t.fcandidatename,d.fhealth,d.faddress

fromtcandidateast

innerjointcandidatedetailasd

ont.fcandidateid=d.fcandidateid

andt.fCredentialsID='630103197107037030'

2.        根据考生的身分证ＩＤ，查询该用户的其它的信息

3.        查询结果平均用时：

19秒

1.        Tcandidate和Tcandidatedetail表，TUserInfo各有数据3040000多记录

selectt.fcandidateID,t.fcandidatename,d.fhealth,d.faddress

fromtcandidateast

innerjointcandidatedetailasd

ont.fcandidateid=d.fcandidateid

innerjointuserinfoasu

onu.fLogonCode=t.fcandidatename

whereu.flogoncode='tim1090122'

2.        登陆考生通过TUserInfo表中的flogonCode得自己的详细信息

3.        平均用时：

２８秒

---------------------------------------------------------------------------------------------------------------

1.        通过登陆的flogonCode获得该用户的相关信息，并更新考生的部分信息

UPDATEtCandidate

SETfCandidateCode=fCandidateCode+'_U'

FROMtCandidateAST

JOINTUserInfoASU

ONT.fCandidateCode=U.fLogonCode

whereU.fLogonCode='tim1090123'

2.        通过考生登陆网站的ｌｏｇｏｎｃｏｄｅ获得考生的信息，并更新部分信息

3.        平均时间：

28秒

---------------------------------------------------------------------------------------------------------------

1.     根据tCandidate表中的fcandidatecode删除TuserInfo表中的某条记录

DELETEFROMtUserInfo

FromTUserInfoasU

INNERJOINtCandidateasT

      onT.fCandidateCode=U.fLogonCode

WHERET.fCandidateCode='tim1096133'

2.     删除记录

3.     平均时间20秒

---------------------------------------------------------------------------------------------------------------

1.     更新考生记录

UPDATEtCandidateDetail

SETfaddress=faddress+'beijingroad'

FROMtCandidateDetailAST

JOINtCandidateASD

ONT.fCandidateID=D.fCandidateID

whereD.fCandidateName='tim1090168'

2.     根据考生的姓名更新考生的详细信息

3.     平均时间20秒

添加评论 |固定链接 |引用通告（0） |写入日志

DesignGuidelinesforApplicationPerformance

添加评论 |固定链接 |引用通告（0） |写入日志

howtorestoreandbackupdatabasebyusingstoreprocedure

ifexists（

select*fromsysobjects

whereandxtype='p'

）

begin

dropprocpr_backup_db

end

go

/*备份数据库*/

createprocpr_backup_db

@flagvarchar（10）out,

@backup_db_namevarchar（128）,

@filenamevarchar（1000）--路径＋文件名字

as

declare@sqlnvarchar（4000）,@parnvarchar（1000）

select@par='@filenamevarchar（1000）'

select@sql='BACKUPDATABASE'+@backup_db_name+'todisk=@filenamewithinit'

executesp_executesql@sql,@par,@filename

select@flag='ok'

go

ifexists（

select*fromsysobjects

whereandxtype='fn'

）

begin

dropfunctionfn_GetFilePath

end

go

/*创建函数,得到文件得路径*/

createfunctionfn_GetFilePath（@filenamenvarchar（260））

returnsnvarchar（260）

as

begin

declare@file_pathnvarchar（260）

declare@filename_reversenvarchar（260）

select@filename_reverse=reverse（@filename）

select@file_path=substring（@filename,1,len（@filename）+1-charindex（'\',@filename_reverse））

return@file_path

end

go

ifexists（

select*fromsysobjects

whereandxtype='p'

）

begin

dropprocpr_restore_db

end

go

createprocpr_restore_db/*恢复数据库*/

@flagvarchar（20）out,/*过程运行的状态标志,是输入参数*/

@restore_db_namenvarchar（128）,/*要恢复的数据名字*/

@filenamenvarchar（260）/*备份文件存放的路径+备份文件名字*/

as

declare@proc_resulttinyint/*返回系统存储过程xp_cmdshell运行结果*/

declare@loop_timesmallint/*循环次数*/

declare@max_id