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

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

注：测试记录：每个表记录数：3040000。
如何写出高效的ＳＱＬ脚本：

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

SARG的定义：用于限制搜索的一个操作，因为它通常是指一个特定的匹配，一个值得范围内的匹配或者两个以上条件的AND连接。［可以理解为索引扫描］形式如下：

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

或

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

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

Name=’ATA’

数量>5000

5000<数量

Name=’ATA’ and 数量>5000

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

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 null等，另外还有函数。下面就是几个不满足SARG形式的例子：

ABS(数量)<5000

Name like ‘%ATA’

有些表达式，如：

WHERE 数量*2>5000

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

WHERE数量>2500/2

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

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

如果在LIKE前面加上通配符%，那么将会引起全表扫描，所以其执行效率是低下的。用函数charindex()来代替LIKE速度会有大的提升的说法不对的，测试如下：

1.      select fcandidateid,fcandidatename 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的时候，后面加“Tim%”符合SARG规则，用时明显少于后两种，后两种的性能基本上差不多

如果非的模糊，比如：substring(fcandidatename,1,1)=’A’,那么可以考虑这样：fcandidatename like ‘A%’来代替（因为这样用的是索引扫描，不是表扫描）

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

下面我来做一个测试：

a)      select fcandidateid,fcandidatename from tcandidate where  fcandidatename like 'Tim%'

用时：35秒　记录结果数：２００万

b)      select fcandidateid,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 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 tcandidate 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.       使用字符串[字母组成的字符串]

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 by fbirthday desc

用时：1分9秒 　记录结果数：２００万

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

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

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

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

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

如果要插入大的二进制值到Image列，使用存储过程，千万不要用内嵌INsert来插入。因为这样应用程序首先将二进制值转换成字符串（尺寸是它的两倍），服务器收到字符后又将他转换成二进制值.

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

9.       高效的TOＰ

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

 

select top 50000 * from (

select top 2000000 fcandidateid,fcandidatename,fbirthday from tcandidate

where fcandidatename like 'Tim%'

order by fcandidateid desc) as a

 

用时：1179毫秒

 

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

这条语句，从理论上讲，整条语句的执行时间应该比子句的执行时间长，但事实相反。因为，子句执行后返回的是2000000条记录，而整条语句仅返回50000条语句，所以影响数据库响应时间最大的因素是物理I/O操作。而限制物理I/O操作此处的最有效方法之一就是使用TOP关键词了。TOP关键词是SQL SERVER中经过系统优化过的一个用来提取前几条或前几个百分比数据的词。所以经过优化算法的ＴＯＰ效率就很高了

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 where  fbirthday=dateadd(year,-5,'1981/09/08')

CPU:1219  用时：21666毫秒 记录结果：1000000

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

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

 

11.     表和临时表的用法

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

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

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

l         涉及表变量的事务仅维持表变量上更新的持续时间。因此，使用表变量时，需要锁定和记录资源的情况更少。因为表变量具有有限的范围并且不是持久性数据库的一部分，所以事务回滚并不影响它们。

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

? 插入到表中的行数。

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

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

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


如何写出高效的ＳＱＬ脚本《三》
12.     视图

 

尽量少用视图，它的效率相对比较低。对视图操作比直接对表操作慢,可以用stored procedure来代替她。特别的是不要用视图嵌套,嵌套视图增加了寻找原始资料的难度。我们看视图的本质：它是存放在服务器上的被优化好了的已经产生了查询规划的SQL。对单个表检索数据时，不要使用指向多个表的视图，直接从表检索或者仅仅包含这个表的视图上读，否则增加了不必要的开销,查询受到干扰.为了加快视图的查询，MsSQL增加了视图索引的功能

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

 

13.     GROUP BY    Having

一般在GROUP BY 个HAVING字句之前就能剔除多余的行，所以尽量不要用它们来做剔除行的工作。他们的执行顺序应该如下最优：select 的Where字句选择所有合适的行，Group 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字段在ｔｃａｎｄｉｄａｔｅ表中是整型，那么如果第一种方式ｓｑｌ就有一个隐式的函数转换ｃａｓｔ（‘１＇　ａｓ　ｉｎｔ），花销时间。

如果一个字段是ｆｌｏａｄ类型的，比如有一个字段ｆｓｃｏｒｅ是ｆｌｏａｔ类型，那么条件语句就应该这样写（假如：是选出６０分以上的学生），就因该这样写：

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

这个有一个隐式的转换。

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

 

15.     测试结果如下：

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

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

from tcandidate as t

inner 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 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.fcandidateid = 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 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 ='tim1096133'

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 Application Performance
http://msdn.microsoft.com/SQL/2000/learn/perf/default.aspx?pull=/library/en-us/dnpag/html/scalenetchapt03.asp
添加评论 | 固定链接 | 引用通告 (0) | 写入日志
how to restore and backup database by using store procedure

 

if exists(
select * from sysobjects
where and xtype='p'
)
begin
drop proc pr_backup_db
end

go

/*备份数据库*/
create proc pr_backup_db
@flag varchar(10) out,
@backup_db_name varchar(128),
@filename varchar(1000) --路径＋文件名字
as
declare @sql nvarchar(4000),@par nvarchar(1000)
select @par='@filename varchar(1000)'
select @sql='BACKUP DATABASE '+@backup_db_name+' to disk=@filename with init'
execute sp_executesql @sql,@par,@filename
select @flag='ok'
go

if exists(
select * from sysobjects
where and xtype='fn'
)
begin
drop function fn_GetFilePath
end
go

/*创建函数,得到文件得路径*/
create function fn_GetFilePath(@filename nvarchar(260))
returns nvarchar(260)
as
begin
declare @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_path
end

go


if exists(
select * from sysobjects
where and xtype='p'
)
begin
drop proc pr_restore_db
end
go

create proc pr_restore_db /*恢复数据库*/
@flag varchar(20) out, /*过程运行的状态标志,是输入参数*/
@restore_db_name nvarchar(128), /*要恢复的数据名字*/
@filename nvarchar(260) /*备份文件存放的路径+备份文件名字*/
as
declare @proc_result tinyint /*返回系统存储过程xp_cmdshell运行结果*/
declare @loop_time smallint /*循环次数*/
declare @max_ids smallint /*@tem表的ids列最大数*/
declare @file_bak_path nvarchar(260) /*原数据库存放路径*/
declare @flag_file bit /*文件存放标志*/
declare @master_path nvarchar(260) /*数据库master文件路径*/
declare @sql nvarchar(4000),@par nvarchar(1000)
declare @sql_sub nvarchar(4000)
declare @sql_cmd nvarchar(4000)
/*
判断参数@filename文件格式合法性，以防止用户输入类似d: 或者 c:/a/ 等非法文件名
参数@filename里面必须有'/'并且不以'/'结尾
*/
if right(@filename,1)<>'/' and charindex('/',@filename)<>0
begin
select @sql_cmd='dir '+@filename
EXEC @proc_result = master..xp_cmdshell @sql_cmd,no_output
IF (@proc_result<>0) /*系统存储过程xp_cmdshell返回代码值:0(成功）或1（失败）*/
begin
select @flag='not exist' /*备份文件不存在*/
return /*退出过程*/
end
/*创建临时表,保存由备份集内包含的数据库和日志文件列表组成的结果集*/
create table #tem(
LogicalName nvarchar(128), /*文件的逻辑名称*/
PhysicalName nvarchar(260) , /*文件的物理名称或操作系统名称*/
Type char(1), /*数据文件 (D) 或日志文件 (L)*/
FileGroupName nvarchar(128), /*包含文件的文件组名称*/
[Size] numeric(20,0), /*当前大小（以字节为单位）*/
[MaxSize] numeric(20,0) /*允许的最大大小（以字节为单位）*/
)
/*
创建表变量，表结构与临时表基本一样
就是多了两列,
列ids（自增编号列）,
列file_path,存放文件的路径
*/
declare @tem table(
ids smallint identity, /*自增编号列*/
LogicalName nvarchar(128),
PhysicalName nvarchar(260),
File_path nvarchar(260),
Type char(1),
FileGroupName nvarchar(128)
)
insert into #tem
execute('restore filelistonly from disk='''+@filename+'''')
/*将临时表导入表变量中,并且计算出相应得路径*/
insert into @tem(LogicalName,PhysicalName,File_path,Type,FileGroupName)
select LogicalName,PhysicalName,dbo.fn_GetFilePath(PhysicalName),Type,FileGroupName
from #tem
if @@rowcount>0
begin
drop table #tem
end
select @loop_time=1
select @max_ids=max(ids) /*@tem表的ids列最大数*/
from @tem
while @loop_time<=@max_ids
begin
select @file_bak_path=file_path
from @tem where ids=@loop_time
select @sql_cmd='dir '+@file_bak_path
EXEC @proc_result = master..xp_cmdshell @sql_cmd,no_output
/*系统存储过程xp_cmdshell返回代码值:0(成功）或1（失败）*/
IF (@proc_result<>0)
select @loop_time=@loop_time+1
else
BREAK /*没有找到备份前数据文件原有存放路径，退出循环*/
end
select @master_path=''
if @loop_time>@max_ids
select @flag_file=1 /*备份前数据文件原有存放路径存在*/
else
begin
select @flag_file=0 /*备份前数据文件原有存放路径不存在*/
select @master_path=dbo.fn_GetFilePath(filename)
from master..sysdatabases where
end
select @sql_sub=''
/*type='d'是数据文件,type='l'是日志文件 */
/*@flag_file=1时新的数据库文件还是存放在原来路径，否则存放路径和master数据库路径一样*/
select @sql_sub=@sql_sub+'move '''+LogicalName+''' to '''
+case type
when 'd' then case @flag_file
when 1 then File_path
else @master_path
end
when 'l' then case @flag_file
when 1 then File_path
else @master_path
end
end
+case type
when 'd' then @restore_db_name+'_'+LogicalName+'_data.mdf'','
when 'l' then @restore_db_name+'_'+LogicalName+'_log.ldf'','
end
from @tem
select @sql='RESTORE DATABASE @db_name FROM DISK=@filename with '
select @sql=@sql+@sql_sub+'replace'
select @par='@db_name nvarchar(128),@filename nvarchar(260)'
print @sql
execute sp_executesql @sql,@par,@db_name=@restore_db_name,@filename=@filename
select @flag='ok' /*操作成功*/
end
else
begin
SELECT @flag='file type error' /*参数@filename输入格式错误*/
end


--备份数据库test_database
declare @fl varchar(10)
execute pr_backup_db @fl out,'test_database','c:/test_database.bak'
select @fl

--恢复数据库,输入的参数错误
declare @fl varchar(20)
exec pr_restore_db @fl out,'sa','c:/'
select @fl


--恢复数据库,即创建数据库test_database的复本test_db
declare @fl varchar(20)
exec pr_restore_db @fl out,'test_db','c:/test_database.bak'
select @fl