如何写出高效的SQL脚本.docx
- 文档编号:5019702
- 上传时间:2022-12-12
- 格式:DOCX
- 页数:12
- 大小:23.08KB
如何写出高效的SQL脚本.docx
《如何写出高效的SQL脚本.docx》由会员分享,可在线阅读,更多相关《如何写出高效的SQL脚本.docx(12页珍藏版)》请在冰豆网上搜索。
如何写出高效的SQL脚本
如何写出高效的SQL脚本:
如何写出高效的SQL脚本《二》
1. 设计如何满足SARG形式的SQL脚本
SARG的定义:
用于限制搜索的一个操作,因为它通常是指一个特定的匹配,一个值得范围内的匹配或者两个以上条件的AND连接。
[可以理解为索引扫描]形式如下:
列名操作符<常数或变量>
或
<常数或变量>操作符列名
列名可以出现在操作符的一边,而常数或变量出现在操作符的另一边。
如:
Name=’ATA’
数量>5000
5000<数量
Name=’ATA’and数量>5000
如果一个表达式不能满足SARG的形式,那它就无法限制搜索的范围了,也就是SQLSERVER必须对每一行都判断它是否满足WHERE子句中的所有条件。
所以一个索引对于不满足SARG形式的表达式来说是无用的
2. Like
Like语句是否属于SARG取决于所使用的通配符的类型
如:
namelike‘ATA%’,这就属于SARG
而:
namelike‘%ATA’,就不属于SARG。
原因是通配符%在字符串的开头使得索引无法使用。
3. OR 和 IN
or会引起全表扫描
Name=’ATA’and数量>5000符号SARG,而:
Name=’ATA’or数量>5000则不符合SARG。
使用or和In会引起全表扫描
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秒,记录结果数:
200万
2. selectfcandidateid,fcandidatenamefromtcandidate
where charindex('Tim',fcandidatename)>0
用时:
47秒,记录结果数:
200万
3. selectfcandidateid,fcandidatenamefromtcandidate
where fcandidatenamelike'%Tim%'
用时:
45秒,记录结果数:
200万
通过以上3个例子可以看出,再使用Like的时候,后面加“Tim%”符合SARG规则,用时明显少于后两种,后两种的性能基本上差不多
如果非的模糊,比如:
substring(fcandidatename,1,1)=’A’,那么可以考虑这样:
fcandidatenamelike‘A%’来代替(因为这样用的是索引扫描,不是表扫描)
如何写出高效的SQL脚本《二》
6. 字段提取要按照“需多少、提多少”的原则,避免“select*“
下面我来做一个测试:
a) selectfcandidateid,fcandidatenamefromtcandidatewhere fcandidatenamelike'Tim%'
用时:
35秒 记录结果数:
200万
b) selectfcandidateid,fcandidatename,fCredentialsID,fbirthdayfromtcandidatewhere fcandidatenamelike'Tim%'
用时:
51秒记录结果数:
200万
c) selectfcandidateidfromtcandidatewhere fcandidatenamelike'Tim%'
用时:
23秒 记录结果数:
200万
由此看来,我们每少提取一个字段,数据的提取速度就会有相应的提升,当然提升的速度还要看您舍弃的字段的大小来判断。
7. orderby的使用,用那些字段作为排序效率比较高
1. 使用具有族索引或者primarykey的字段排序。
selectfcandidateid,fCredentialsID,fcandidatename,fbirthday,fRegionIDfromtcandidatewhere fcandidatenamelike'Tim%'orderbyfcandidateiddesc
用时:
45秒记录结果数:
200万
2. 使用数字的字段(既不是primarykey,也不是族索引、foreignkey)
selectfcandidateid,fCredentialsID,fcandidatename,fbirthday,fRegionIDfromtcandidatewhere fcandidatenamelike'Tim%'orderbyfRegionIDdesc
用时:
1分15秒 记录结果数:
200万
3. 使用字符串的字段[数字字符串](既不是primarykey,也不是族索引、foreignkey)
selectfcandidateid,fCredentialsID,fcandidatename,fbirthday,fRegionIDfromtcandidatewhere fcandidatenamelike'Tim%'orderbyfCredentialsIDdesc
用时:
1分22秒 记录结果数:
200万
4. 使用字符串[字母组成的字符串]
selectfcandidateid,fCredentialsID,fcandidatename,fbirthday,fRegionIDfromtcandidatewhere fcandidatenamelike'Tim%'orderbyfcandidatenamedesc
用时:
1分34秒 记录结果数:
200万
5. 使用日期字段
selectfcandidateid,fCredentialsID,fcandidatename,fbirthday,fRegionIDfromtcandidatewhere fcandidatenamelike'Tim%'orderbyfbirthdaydesc
用时:
1分9秒 记录结果数:
200万
从以上可以看出,使用族索引、Primarykey的字段进行排序,速度是比较快的,另外,如果表有foreignkey的字段,在排序的时候,可以优先考虑这些字段。
以上进行了数字、数字字符串、字母字符串、日期的排序,关于性能大家可以参看以上的测试结果,测试的结果日期字段的排序比数字的要高,这个可以在多测试一下类似的数据量,比较一下到底数字和日期字段到底哪个速度快些。
另外的几种情况,大家就一目了然了。
同时,按照某个字段进行排序的时候,无论是正序还是倒序,速度是基本相当的。
8. 插入大的二进制值到Image列需要注意的
如果要插入大的二进制值到Image列,使用存储过程,千万不要用内嵌INsert来插入。
因为这样应用程序首先将二进制值转换成字符串(尺寸是它的两倍),服务器收到字符后又将他转换成二进制值.
存储过程就没有这些动作:
在前台调用这个存储过程传入二进制参数,这样处理速度明显改善。
9. 高效的TOP
事实上,在查询和提取超大容量的数据集时,根据需要提取一次需要的数据,如果允许尽量使用top语句。
如:
selecttop50000*from(
selecttop2000000fcandidateid,fcandidatename,fbirthdayfromtcandidate
wherefcandidatenamelike'Tim%'
orderbyfcandidateiddesc)asa
用时:
1179毫秒
大家可以仔细看上面的查询语句,可以和以上的语句项比较,就可以知道top的效率有多高了
这条语句,从理论上讲,整条语句的执行时间应该比子句的执行时间长,但事实相反。
因为,子句执行后返回的是2000000条记录,而整条语句仅返回50000条语句,所以影响数据库响应时间最大的因素是物理I/O操作。
而限制物理I/O操作此处的最有效方法之一就是使用TOP关键词了。
TOP关键词是SQLSERVER中经过系统优化过的一个用来提取前几条或前几个百分比数据的词。
所以经过优化算法的TOP效率就很高了
10.如何使用SQL的函数注意的问题
1. SQL的函数在SQL脚本中不同的位置消耗的成本就不一样
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函数方的位置不一样,性能和消耗的成本也不一样,总体原则把SQL函数放到条件的右边性能消耗的成本等比较低。
3. 注意使用DISTINCT,在没有必要时不要用,它同UNION一样会使查询变慢,如果确认结果集中没有重复的记录,请不要轻易用distict
11. 表和临时表的用法
1. 一般情况下尽量使用表变量而不用临时表,为何推荐表变量,请看下面的解释:
● 表变量(如局部变量)具有明确定义的范围,在该范围结束时会自动清除这些表变量。
● 与临时表相比,表变量导致存储过程的重新编译更少。
● 涉及表变量的事务仅维持表变量上更新的持续时间。
因此,使用表变量时,需要锁定和记录资源的情况更少。
因为表变量具有有限的范围并且不是持久性数据库的一部分,所以事务回滚并不影响它们。
2. 什么时候使用表变量而不使用临时表
•插入到表中的行数。
•从中保存查询的重新编译的次数。
•查询类型及其对性能的指数和统计信息的依赖性。
3. 关于表变量的缺陷,大家可以到msdn上搜索一下(tablevariable)
如何写出高效的SQL脚本《三》
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字段在tcandidate表中是整型,那么如果第一种方式sql就有一个隐式的函数转换cast(‘1' as int),花销时间。
如果一个字段是fload类型的,比如有一个字段fscore是float类型,那么条件语句就应该这样写(假如:
是选出60分以上的学生),就因该这样写:
出fscore>60.0.而不是 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. 根据考生的身分证ID,查询该用户的其它的信息
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. 平均用时:
28秒
---------------------------------------------------------------------------------------------------------------
1. 通过登陆的flogonCode获得该用户的相关信息,并更新考生的部分信息
UPDATEtCandidate
SETfCandidateCode=fCandidateCode+'_U'
FROMtCandidateAST
JOINTUserInfoASU
ONT.fCandidateCode=U.fLogonCode
whereU.fLogonCode='tim1090123'
2. 通过考生登陆网站的logoncode获得考生的信息,并更新部分信息
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
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 如何 写出 高效 SQL 脚本