有效地MSSQL查询参数SARG(myBased)
SARG的定义：用于限制搜索的一个操作，因为它通常是指一个特定的匹配，一个值得范围内的匹配或者两个以上条件的AND连接。形式如下：
SARG的定义：用于限制搜索的一个操作，因为它通常是指一个特定的匹配，一个值得范围内的匹配或者两个以上条件的AND连接。形式如下：
- 函数和Like:是否属于SARG取决于所使用的通配符的类型 如：name like ‘张%’ ，这就属于SARG 而：name like ‘%张’ , 就不属于SARG
用函数charindex()和前面加通配符%的LIKE执行效率一样
前面，我们谈到，如果在LIKE前面加上通配符%，那么将会引起全表扫描，所以其执行效率是低下的。但有的资料介绍说，用函数charindex()来代替LIKE速度会有大的提升，经我试验，发现这种说明也是错误的：
select gid,title,fariqi,reader from tgongwen where charindex(''刑侦支队'',reader)>0 and fariqi>''2004-5-5''
用时 7秒 另,扫描计数 4 逻辑读 7155 次 物理读 0 次预读 0 次
select gid,title,fariqi,reader from tgongwen where reader like ''%'' + ''刑侦支队'' + ''%'' and fariqi>''2004-5-5''
用时7秒,另,扫描计数 4,逻辑读 7155 物理读 0 预读 0 - IN 的作用相当与OR 是一样的，都会引起全表扫描，如果tid上有索引，其索引也会失效(但是我的试验，索引并没有失效，而且还用上了PKID的聚集索引)
select * from et_order where contace_name in ('王新', '刘维敏')
--1.解析出来其实是contace_name= '王新'or contace_name= '刘维敏'
--2.居然有Nested loop 和 Clustered Index --重新select * into et_order_test from et_order问题就没了，估计是缓存
|--Nested Loops(Inner Join, OUTER REFERENCES:([PtnIds1003]) PARTITION ID:([PtnIds1003]))
|--Clustered Index Scan(OBJECT:([ETMC].[dbo].[ET_Order].[PK_ET_Order]), WHERE:([ETMC].[dbo].[ET_Order].[Contace_Name]='刘维敏' OR [ETMC].[dbo].[ET_Order].[Contace_Name]='王新') PARTITION ID:([PtnIds1003])) - exists 和 in 的执行效率是一样的 mssql oracle已经很智能了,但是not 的实行计划还是不一样原因是对于null的处理 http://www.cnblogs.com/perfectdesign/archive/2008/08/06/sql_exists_in.html
很多资料上都显示说，exists要比in的执行效率要高，同时应尽可能的用not exists来代替not in。但事实上，我试验了一下，发现二者无论是前面带不带not，二者之间的执行效率都是一样的。因为涉及子查询，我们试验这次用SQL SERVER自带的pubs数据库。运行前我们可以把SQL SERVER的statistics I/O状态打开：
select title,price from titles where title_id in (select title_id from sales where qty>30) 该句的执行结果为：
表 ''sales''。扫描计数 18，逻辑读 56 次，物理读 0 次，预读 0 次。
表 ''titles''。扫描计数 1，逻辑读 2 次，物理读 0 次，预读 0 次。
select title,price from titles where exists (select * from sales where sales.title_id=titles.title_id and qty>30) 第二句的执行结果为：
表 ''sales''。扫描计数 18，逻辑读 56 次，物理读 0 次，预读 0 次。
表 ''titles''。扫描计数 1，逻辑读 2 次，物理读 0 次，预读 0 次。 - 慎用游标
数据库一般的操作是集合操作，也就是对由WHERE子句和选择列确定的结果集作集合操作，游标是提供的一个非集合操作的途径。一般情况下，游标实现的功能往往相当于客户端的一个循环实现的功能，所以，大部分情况下，我们把游标功能搬到客户端。游标是把结果集放在服务器内存，并通过循环一条一条处理记录，对数据库资源（特别是内存和锁资源）的消耗是非常大的，所以，我们应该只有在没有其他方法的情况下才使用游标。另外，我们可以用SQL SERVER的一些特性来代替游标，达到提高速度的目的。
A、字符串连接的例子:这是论坛经常有的例子，就是把一个表符合条件的记录的某个字符串字段连接成一个变量。比如需要把JOB_ID=10的EMPLOYEE的FNAME连接在一起，用逗号连接，可能最容易想到的是用游标：
DECLARE @NAME VARCHAR(20) DECLARE @NAME VARCHAR(1000) DECLARE NAME_CURSOR CURSOR FOR SELECT FNAME FROM EMPLOYEE WHERE JOB_ID=10 ORDER BY EMP_ID OPEN NAME_CURSOR FETCH NEXT FROM RNAME_CURSOR INTO @NAME WHILE @@FETCH_STATUS = 0 BEGIN SET @NAMES = ISNULL(@NAMES+’,’,’’)+@NAME FETCH NEXT FROM NAME_CURSOR INTO @NAME END CLOSE NAME_CURSOR DEALLOCATE NAME_CURSOR 可以如下修改，功能相同： DECLARE @NAME VARCHAR(1000) SELECT @NAMES = ISNULL(@NAMES+’,’,’’)+FNAME FROM EMPLOYEE WHERE JOB_ID=10 ORDER BY EMP_ID
B、 用CASE WHEN 实现转换的例子很多使用游标的原因是因为有些处理需要根据记录的各种情况需要作不同的处理，实际上这种情况，我们可以用CASE WHEN语句进行必要的判断处理，而且CASE WHEN是可以嵌套的。比如:
C、 变量参与的UPDATE语句的例子SQL ERVER的语句比较灵活，变量参与的UPDATE语句可以实现一些游标一样的功能，比如：在表结构:
CREATE TABLE 料件表(
料号 VARCHAR(30),
名称 VARCHAR(100),
主单位 VARCHAR(20),
单位1 VARCHAR(20),
单位1参数 NUMERIC(18,4),
单位2 VARCHAR(20),
单位2参数 NUMERIC(18,4)
)
CREATE TABLE 入库表(
时间 DATETIME,
料号 VARCHAR(30),
单位 INT,
入库数量 NUMERIC(18,4),
损坏数量 NUMERIC(18,4)
)
--其中，单位字段可以是0，1，2，分别代表主单位,单位1,单位2,很多计算需要统一单位,统一单位可以用游标实现
DECLARE @料号 VARCHAR(30),
@单位 INT,
@参数 NUMERIC(18,4),
DECLARE CUR CURSOR FOR
SELECT 料号,单位 FROM 入库表 WHERE 单位 <>0
OPEN CUR
FETCH NEXT FROM CUR INTO @料号,@单位
WHILE @@FETCH_STATUS<>-1
BEGIN
IF @单位=1
BEGIN
SET @参数=(SELECT 单位1参数 FROM 料件表 WHERE 料号 =@料号)
UPDATE 入库表 SET 数量=数量*@参数,损坏数量=损坏数量*@参数,单位=1 WHERE CURRENT OF CUR
END
IF @单位=2
BEGIN
SET @参数=(SELECT 单位1参数 FROM 料件表 WHERE 料号 =@料号)
UPDATE 入库表 SET 数量=数量*@参数,损坏数量=损坏数量*@参数,单位=1 WHERE CURRENT OF CUR
END
FETCH NEXT FROM CUR INTO @料号,@单位
END
CLOSE CUR
DEALLOCATE CUR
--可以改写成：
UPDATE A SET
数量=CASE A.单位 WHEN 1 THEN A.数量*B. 单位1参数
WHEN 2 THEN A.数量*B. 单位2参数
ELSE A.数量
END,
损坏数量= CASE A.单位 WHEN 1 THEN A. 损坏数量*B. 单位1参数
WHEN 2 THEN A. 损坏数量*B. 单位2参数
ELSE A. 损坏数量
END,
单位=1
FROM入库表 A, 料件表 B
WHERE A.单位<>1 AND
A.料号=B.料号
SELECT A,B,C,CAST(NULL AS INT) AS 序号 INTO #T FROM 表 ORDER BY A ,NEWID()
--产生临时表后，已经按照A字段排序，但是在A相同的情况下是乱序的，这时如果需要更改序号字段为
按照A字段分组的记录序号,就只有游标和变量参与的UPDATE语句可以实现了,这个变量参与的UPDATE语句如下
DECLARE @A INT
DECLARE @序号 INT
UPDATE #T SET
@序号=CASE WHEN A=@A THEN @序号+1 ELSE 1 END,
@A=A,
序号=@序号
D、如果必须使用游标，注意选择游标的类型，如果只是循环取数据，那就应该用只进游标（选项FAST_FORWARD），一般只需要静态游标（选项STATIC）。
E、 注意动态游标的不确定性，动态游标查询的记录集数据如果被修改，会自动刷新游标，这样使得动态游标有了不确定性，因为在多用户环境下，如果其他进程或者本身更改了纪录，就可能刷新游标的记录集。 - union代替
or，不同字段性能变高，相同字段性能一样
我们前面已经谈到了在where子句中使用or会引起全表扫描，一般的，我所见过的资料都是推荐这里用union来代替or。事实证明，这种说法对于大部分都是适用的。
select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi=''2004-9-16'' or gid>9990000
用时：68秒。扫描计 数 1，逻辑读 404008 次，物理读 283 次，预读 392163 次。
select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi=''2004-9-16''
union
select gid,fariqi,neibuyonghu,reader,title from Tgongwen where gid>9990000
用时：9秒。扫描计数 8，逻辑读 67489 次，物理读 216 次，预读 7499 次。 --看来，用union在通常情况下比用or的效率要高的多。
但经过试验 发现如果or两边的查询列是一样的话，那么用union则反倒和用or的执行速度差很多，虽然这里union扫描的是索引，而or扫描的是全表。
select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi=''2004-9-16'' or fariqi=''2004-2-5''
用时：6423毫秒。扫描计数 2，逻辑读 14726 次，物理读 1 次，预读 7176 次。
select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi=''2004-9-16''
union
select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi=''2004-2-5''
用时：11640毫秒。扫描计数 8，逻辑读 14806 次，物理读 108 次，预读 1144 次。 - 只返回需要的数据,返回数据到客户端至少需要数据库提取数据、网络传输数据、客户端接收数据以及客户端处理数据等环节，如果返回不需要的数据，就会增加服务器、网络和客户端的无效劳动，其害处是显而易见的，避免这类事件需要注意：
A、不要写SELECT *的语句
B、不要写没有WHERE的SQL语句
C、注意SELECT INTO后的WHERE子句，因为SELECT INTO把数据插入到临时表，这个过程会锁定一些系统表，如果这个WHERE子句返回的数据过多或者速度太慢，会
成系统表长期锁定，诸塞其他进程。
D、对于聚合查询，可以用HAVING子句进一步限定返回的行。 - count(*)不比count(字段)慢
- order by按聚集索引列排序效率最高 (这个是放屁，看我的例子)
select top 10000 * from et_order
SQL Server 执行时间:
CPU 时间 = 157 毫秒，占用时间 = 3480 毫秒。
select top 10000 * from et_order order by pkid desc
SQL Server 执行时间:
CPU 时间 = 18624 毫秒，占用时间 = 144605 毫秒。 - 高效的TOP (分页的时候用，现在没用到,还有一个分页的存储过程在)事实上，在查询和提取超大容量的数据集时，影响数据库响应时间的最大因素不是数据查找，而是物理的I/0操作。如：select top 10 * from (select top 10000 gid,fariqi,title from tgongwen where neibuyonghu=''办公室'' order by gid desc) as a order by gid asc
这条语句，从理论上讲，整条语句的执行时间应该比子句的执行时间长，但事实相反。因为，子句执行后返回的是10000条记录，而整条语句仅返回 10条语句，所以影响数据库响应时间最大的因素是物理I/O操作。而限制物理I/O操作此处的最有效方法之一就是使用TOP关键词了。TOP关键词是 SQL SERVER中经过系统优化过的一个用来提取前几条或前几个百分比数据的词。经笔者在实践中的应用，发现TOP确实很好用，效率也很高。但这个词在另外一 个大型数据库ORACLE中却没有，这不能说不是一个遗憾，虽然在ORACLE中可以用其他方法（如：rownumber）来解决。在以后的关于“实现千 万级数据的分页显示存储过程”的讨论中，我们就将用到TOP这个关键词。
到此为止，我们上面讨论了如何实现从大容量的数据库中快速地查询出您所需要的数据方法。当然，我们介绍的这些方法都是“软”方法，在实践中，我们还要考虑各种“硬”因素，如：网络性能、服务器的性能、操作系统的性能，甚至网卡、交换机等。 - 注意事务和锁 事务是数据库应用中和重要的工具，它有原子性、一致性、隔离性、持久性这四个属性，很多操作我们都需要利用事务来保证数据的正确性。在使用事务中我们需要做到尽量避免死锁、尽量减少阻塞。具体以下方面需要特别注意：
A、事务操作过程要尽量小，能拆分的事务要拆分开来。
B、事务操作过程不应该有交互，因为交互等待的时候，事务并未结束，可能锁定了很多资源。
C、事务操作过程要按同一顺序访问对象。
D、提高事务中每个语句的效率，利用索引和其他方法提高每个语句的效率可以有效地减少整个事务的执行时间。
E、尽量不要指定锁类型和索引，SQL SERVER允许我们自己指定语句使用的锁类型和索引，但是一般情况下，SQL SERVER优化器选择的锁类型和索引是在当前数据量和查询条件下是最优的，我们指定的可能只是在目前情况下更有，但是数据量和数据分布在将来是会变化的。
F、查询时可以用较低的隔离级别，特别是报表查询的时候，可以选择最低的隔离级别（未提交读）。 - 注意临时表和表变量的用法,在复杂系统中，临时表和表变量很难避免，关于临时表和表变量的用法，需要注意：
A、如果语句很复杂，连接太多，可以考虑用临时表和表变量分步完成。
B、如果需要多次用到一个大表的同一部分数据，考虑用临时表和表变量暂存这部分数据。
C、如果需要综合多个表的数据，形成一个结果，可以考虑用临时表和表变量分步汇总这多个表的数据。
D、其他情况下，应该控制临时表和表变量的使用。
E、关于临时表和表变量的选择，很多说法是表变量在内存，速度快，应该首选表变量，但是在实际使用中发现，这个选择主要考虑需要放在临时表的数据量，在数据量较多的情况下，临时表的速度反而更快。
F、关于临时表产生使用SELECT INTO和CREATE TABLE + INSERT INTO的选择，我们做过测试，一般情况下，SELECT INTO会比CREATE TABLE + INSERT INTO的方法快很多，但是SELECT INTO会锁定TEMPDB的系统表SYSOBJECTS、SYSINDEXES、SYSCOLUMNS，在多用户并发环境下，容易阻塞其他进程，所以我的建议是，在并发系统中，尽量使用CREATE TABLE + INSERT INTO，而大数据量的单个语句使用中，使用SELECT INTO。
G、注意排序规则，用CREATE TABLE建立的临时表，如果不指定字段的排序规则，会选择TEMPDB的默认排序规则，而不是当前数据库的排序规则。如果当前数据库的排序规则和TEMPDB的排序规则不同，连接的时候就会出现排序规则的冲突错误。一般可以在CREATE TABLE建立临时表时指定字段的排序规则为DATABASE_DEFAULT来避免上述问题。
作者：Buro#79xxd
出处：http://www.cnblogs.com/buro79xxd/
文章版权归本人所有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接，否则保留追究法律责任的权利。
