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

1、如何写出高效的SQL脚本如何写出高效的脚本：如何写出高效的脚本二1. 设计如何满足形式的脚本 SARG的定义：用于限制搜索的一个操作，因为它通常是指一个特定的匹配，一个值得范围内的匹配或者两个以上条件的AND连接。可以理解为索引扫描形式如下： 列名 操作符 或 操作符列名 列名可以出现在操作符的一边，而常数或变量出现在操作符的另一边。如： Name=ATA 数量5000 50005000 如果一个表达式不能满足SARG的形式，那它就无法限制搜索的范围了，也就是SQL SERVER必须对每一行都判断它是否满足WHERE子句中的所有条件。所以一个索引对于不满足SARG形式的表达式来说是无用的 2.

2、 Like语句是否属于SARG取决于所使用的通配符的类型 如：name like ATA% ，这就属于SARG 而：name like %ATA ,就不属于SARG。 原因是通配符%在字符串的开头使得索引无法使用。 3. 和 or 会引起全表扫描 Name=ATA and 数量5000 符号SARG，而：Name=ATA or 数量5000 则不符合SARG。使用or和会引起全表扫描 4. 非操作符、函数引起的不满足SARG形式的语句 不满足SARG形式的语句最典型的情况就是包括非操作符的语句，如：NOT、!=、!、NOT EXISTS、NOT IN、NOT LIKE，is null, not

3、 null等，另外还有函数。下面就是几个不满足SARG形式的例子： ABS(数量)5000 SQL SERVER也会认为是SARG，SQL SERVER会将此式转化为： WHERE数量2500/2 不推荐这样使用，因为有时SQL SERVER不能保证这种转化与原始表达式是完全等价的。 5. 函数charindex()、前面加通配符%的LIKE，后面加%的效率比较 如果在LIKE前面加上通配符%，那么将会引起全表扫描，所以其执行效率是低下的。用函数charindex()来代替LIKE速度会有大的提升的说法不对的，测试如下： 1. select fcandidateid,fcandidatenam

4、e from tcandidate where fcandidatename like Tim% 用时：36秒，记录结果数：万 2. select fcandidateid,fcandidatename from tcandidate where charindex(Tim,fcandidatename)0 用时：47秒，记录结果数：万 3. select fcandidateid,fcandidatename from tcandidate where fcandidatename like %Tim% 用时：45秒，记录结果数：万 通过以上3个例子可以看出，再使用Like的时候，后面加“T

5、im%”符合SARG规则，用时明显少于后两种，后两种的性能基本上差不多 如果非的模糊，比如：substring(fcandidatename,1,1)=A,那么可以考虑这样：fcandidatename like A%来代替（因为这样用的是索引扫描，不是表扫描）如何写出高效的脚本二6. 字段提取要按照“需多少、提多少”的原则，避免“select *“下面我来做一个测试： a) select fcandidateid,fcandidatename from tcandidate where fcandidatename like Tim% 用时：35秒记录结果数：万 b) select fcan

6、didateid,fcandidatename,fCredentialsID,fbirthday from tcandidate where fcandidatename like Tim% 用时：51秒记录结果数：万 c) select fcandidateid from tcandidate where fcandidatename like Tim% 用时：23秒记录结果数：万 由此看来，我们每少提取一个字段，数据的提取速度就会有相应的提升，当然提升的速度还要看您舍弃的字段的大小来判断。 7. order by 的使用，用那些字段作为排序效率比较高 1. 使用具有族索引或者primary

7、 key的字段排序。 select fcandidateid,fCredentialsID,fcandidatename,fbirthday,fRegionID from tcandidate where fcandidatename like Tim% order by fcandidateid desc 用时：45秒 记录结果数：万 2. 使用数字的字段（既不是primary key,也不是族索引、foreign key） select fcandidateid,fCredentialsID,fcandidatename,fbirthday,fRegionID from tcandidat

8、e where fcandidatename like Tim% order by fRegionID desc 用时：1分15秒记录结果数：万 3. 使用字符串的字段数字字符串（既不是primary key,也不是族索引、foreign key） select fcandidateid,fCredentialsID,fcandidatename,fbirthday,fRegionID from tcandidate where fcandidatename like Tim% order by fCredentialsID desc 用时：1分22秒 记录结果数：万 4. 使用字符串字母组成

9、的字符串 select fcandidateid,fCredentialsID,fcandidatename,fbirthday,fRegionID from tcandidate where fcandidatename like Tim% order by fcandidatename desc 用时：1分34秒 记录结果数：万 5. 使用日期字段 select fcandidateid,fCredentialsID,fcandidatename,fbirthday,fRegionID from tcandidate where fcandidatename like Tim% order

10、 by fbirthday desc 用时：1分9秒 记录结果数：万 从以上可以看出，使用族索引、Primary key的字段进行排序，速度是比较快的，另外，如果表有foreign key的字段，在排序的时候，可以优先考虑这些字段。 以上进行了数字、数字字符串、字母字符串、日期的排序，关于性能大家可以参看以上的测试结果，测试的结果日期字段的排序比数字的要高，这个可以在多测试一下类似的数据量，比较一下到底数字和日期字段到底哪个速度快些。 另外的几种情况，大家就一目了然了。 同时，按照某个字段进行排序的时候，无论是正序还是倒序，速度是基本相当的。 8. 插入大的二进制值到Image列需要注意的 如

11、果要插入大的二进制值到Image列，使用存储过程，千万不要用内嵌INsert来插入。因为这样应用程序首先将二进制值转换成字符串（尺寸是它的两倍），服务器收到字符后又将他转换成二进制值. 存储过程就没有这些动作: 在前台调用这个存储过程传入二进制参数，这样处理速度明显改善。 9. 高效的TO 事实上，在查询和提取超大容量的数据集时，根据需要提取一次需要的数据，如果允许尽量使用语句。如： select top 50000 * from ( select top 2000000 fcandidateid,fcandidatename,fbirthday from tcandidate where f

12、candidatename like Tim% order by fcandidateid desc) as a 用时：1179毫秒 大家可以仔细看上面的查询语句，可以和以上的语句项比较，就可以知道top的效率有多高了 这条语句，从理论上讲，整条语句的执行时间应该比子句的执行时间长，但事实相反。因为，子句执行后返回的是2000000条记录，而整条语句仅返回50000条语句，所以影响数据库响应时间最大的因素是物理I/O操作。而限制物理I/O操作此处的最有效方法之一就是使用TOP关键词了。TOP关键词是SQL SERVER中经过系统优化过的一个用来提取前几条或前几个百分比数据的词。所以经过优化算法

13、的效率就很高了 10. 如何使用的函数注意的问题 1. 的函数在脚本中不同的位置消耗的成本就不一样 select fcandidateid,fCredentialsID,fcandidatename,fbirthday,fRegionID from tcandidate where dateadd(year,5,fbirthday)=1981/09/08 CPU:2079 用时：25317 毫秒 记录：1000000 2. select fcandidateid,fCredentialsID,fcandidatename,fbirthday,fRegionID from tcandidate

14、where fbirthday=dateadd(year,-5,1981/09/08) CPU:1219 用时：21666毫秒 记录结果：1000000 所以从以上可以看出，不同的SQL函数方的位置不一样，性能和消耗的成本也不一样，总体原则把函数放到条件的右边性能消耗的成本等比较低。 3. 注意使用DISTINCT，在没有必要时不要用，它同UNION一样会使查询变慢，如果确认结果集中没有重复的记录，请不要轻易用 11. 表和临时表的用法 1. 一般情况下尽量使用表变量而不用临时表，为何推荐表变量，请看下面的解释： 表变量（如局部变量）具有明确定义的范围，在该范围结束时会自动清除这些表变量。 与

15、临时表相比，表变量导致存储过程的重新编译更少。 涉及表变量的事务仅维持表变量上更新的持续时间。因此，使用表变量时，需要锁定和记录资源的情况更少。因为表变量具有有限的范围并且不是持久性数据库的一部分，所以事务回滚并不影响它们。 2. 什么时候使用表变量而不使用临时表 插入到表中的行数。 从中保存查询的重新编译的次数。 查询类型及其对性能的指数和统计信息的依赖性。 3. 关于表变量的缺陷，大家可以到msdn上搜索一下（table variable）如何写出高效的脚本三12. 视图 尽量少用视图，它的效率相对比较低。对视图操作比直接对表操作慢,可以用stored procedure来代替她。特别的是

16、不要用视图嵌套,嵌套视图增加了寻找原始资料的难度。我们看视图的本质：它是存放在服务器上的被优化好了的已经产生了查询规划的SQL。对单个表检索数据时，不要使用指向多个表的视图，直接从表检索或者仅仅包含这个表的视图上读，否则增加了不必要的开销,查询受到干扰.为了加快视图的查询，MsSQL增加了视图索引的功能 如果建立的视图数据量比较大，操作比较频繁，则推荐使用索引的视图. 13. GROUP BY Having 一般在GROUP BY 个HAVING字句之前就能剔除多余的行，所以尽量不要用它们来做剔除行的工作。他们的执行顺序应该如下最优：select 的Where字句选择所有合适的行，Group

17、By用来分组个统计行，Having字句用来剔除多余的分组。这样Group By 个Having的开销小，查询快.对于大的数据行进行分组和Having十分消耗资源。如果Group BY的目的不包括计算，只是分组，那么用Distinct更快 14. 存储过程中的参数定义的数据类型尽量和条件中的数据类型一致 1) select fcandidateID from tcandidate where fcredentialstype=1 2) select fcandidateID from tcandidate where fnation=1 Fcredentialstype字段在表中是整型，那么如果

18、第一种方式就有一个隐式的函数转换（），花销时间。 如果一个字段是类型的，比如有一个字段是类型，那么条件语句就应该这样写（假如：是选出分以上的学生），就因该这样写： 出fscore. 而不是fscore60 这个有一个隐式的转换。 虽然这些都是小事，也许如果每一步都注意的话，增提性能就会提高的：） 15. 测试结果如下： 1. 多表选择Tcandidate 和Tcandidatedetail 表 各有数据 3040000条记录 select t.fcandidateID,t.fcandidatename,d.fhealth,d.faddress from tcandidate as t inne

19、r join tcandidatedetail as d on t.fcandidateid = d.fcandidateid and t.fcandidatename=tim1000322 2. 根据姓名，查询该用户的其它的信息 3. 查询结果平均用时：19秒 - 1. 多表选择Tcandidate 和Tcandidatedetail 表 各有数据 3040000条记录 select t.fcandidateID,t.fcandidatename,d.fhealth,d.faddress from tcandidate as t inner join tcandidatedetail as

20、d on t.fcandidateid = d.fcandidateid and t.fCredentialsID=630103197107037030 2. 根据考生的身分证，查询该用户的其它的信息 3. 查询结果平均用时：19秒1. Tcandidate 和Tcandidatedetail 表，TUserInfo 各有数据 3040000多记录 select t.fcandidateID,t.fcandidatename,d.fhealth,d.faddress from tcandidate as t inner join tcandidatedetail as d on t.fcand

21、idateid = d.fcandidateid inner join tuserinfo as u on u.fLogonCode = t.fcandidatename where u.flogoncode=tim1090122 2. 登陆考生通过TUserInfo表中的flogonCode得自己的详细信息 3. 平均用时：秒 - 1. 通过登陆的flogonCode获得该用户的相关信息，并更新考生的部分信息 UPDATE tCandidate SET fCandidateCode = fCandidateCode+_U FROM tCandidate AS T JOIN TUserInfo

22、 AS U ON T.fCandidateCode = U.fLogonCode where U.fLogonCode = tim1090123 2. 通过考生登陆网站的获得考生的信息，并更新部分信息 3. 平均时间：28秒 - 1. 根据tCandidate表中的fcandidatecode删除TuserInfo表中的某条记录 DELETE FROM tUserInfo From TUserInfo as U INNER JOIN tCandidate as T on T.fCandidateCode =U.fLogonCode WHERE T.fCandidateCode =tim1096

23、133 2. 删除记录 3. 平均时间20秒 - 1. 更新考生记录 UPDATE tCandidateDetail SET faddress = faddress+ beijing road FROM tCandidateDetail AS T JOIN tCandidate AS D ON T.fCandidateID = D.fCandidateID where D.fCandidateName = tim1090168 2. 根据考生的姓名更新考生的详细信息 3. 平均时间20秒 添加评论| 固定链接| 引用通告 (0)| 写入日志Design Guidelines for Appli

24、cation Performance添加评论| 固定链接| 引用通告 (0)| 写入日志how to restore and backup database by using store procedure if exists(select * from sysobjectswhere and xtype=p)begindrop proc pr_backup_dbendgo/*备份数据库*/create proc pr_backup_dbflag varchar(10) out,backup_db_name varchar(128),filename varchar(1000) -路径文件名字

25、asdeclare sql nvarchar(4000),par nvarchar(1000)select par=filename varchar(1000)select sql=BACKUP DATABASE +backup_db_name+ to disk=filename with initexecute sp_executesql sql,par,filenameselect flag=okgo if exists(select * from sysobjectswhere and xtype=fn)begindrop function fn_GetFilePathendgo/*创建

26、函数,得到文件得路径*/create function fn_GetFilePath(filename nvarchar(260)returns nvarchar(260) asbegindeclare file_path nvarchar(260)declare filename_reverse nvarchar(260)select filename_reverse=reverse(filename)select file_path=substring(filename,1,len(filename)+1-charindex(,filename_reverse)return file_pa

27、thend go if exists(select * from sysobjectswhere and xtype=p)begindrop proc pr_restore_dbendgocreate proc pr_restore_db /*恢复数据库*/flag varchar(20) out, /*过程运行的状态标志,是输入参数*/ restore_db_name nvarchar(128), /*要恢复的数据名字*/filename nvarchar(260) /*备份文件存放的路径+备份文件名字*/asdeclare proc_result tinyint /*返回系统存储过程xp_cmdshell运行结果*/declare loop_time smallint /*循环次数*/declare max_id