)
BEGIN
CREATE NONCLUSTERED INDEX NCIX_C1 ON CHECK4_T1(C1) --加上非聚集索引
SELECT @ID
--而第二种则将缓存计划浪费了，导致缓存很快被占满，这种做法是相当不可取的
SELECT A.ID,A.C1,B.C1 FROM CHECK3_T1 A
DBCC FREEPROCCACHE
DBCC FREEPROCCACHE

WHERE ID NOT IN (SELECT ID FROM CHECK3_T2 WHERE C1='C1') --400行


INNER JOIN CHECK3_T2 B ON A.ID=B.ID WHERE B.C1='C1'

DECLARE @S INT=1
(
ELSE statement_end_offset
ID INT,
IF @I%100000 =0
WHILE @N<=1000
END - statement_start_offset) / 2) + 1) '语句'
---测试二:2s

total_elapsed_time / execution_count '平均时间',

FROM sys.dm_exec_cached_plans a
SELECT ID,C1 FROM CHECK1_T2 --1W条数据
SELECT ID,C1 FROM CHECK1_T1 --1W条数据
---测试二：（0s）执行计划： 索引查找
--4、 Not Exists 代替 Not In
WHILE @I<=10000
--总结：测试一中的union 排序和去重合并是相当耗时的，如果不要此功能，大数据时最好加上ALL
DECLARE @I INT
END
SET @N=@N+1
--总结：这里有一个比较有趣的地方，若主表和关联表数据差别很大时，走的执行计划走的另一条路
@ID
SET @N=1


ID INT,
END
、Insert into Table（*）,指定具体的列名
--总结：能用INNER JOIN 尽量用它，SQL SERVER在查询时会将关联表进行优化
SELECT A.ID,A.C1 FROM CHECK3_T1 A

DECLARE @ID INT
@ID INT
--=====================================
WHILE @I<=500000
----测试一: （7s） 执行计划：表扫描-> 流聚合-> 计算矢量
SELECT * FROM CHECK5_T1
--补充测试：（1s）执行计划：表扫描-> 并行度 -> 位图 -> 排序 -> 合并联接 -> 并行度
、检查语句是否需要Distinct. 执行计划：表扫描-> 哈希匹配-> 并行度-> 排序
--10、 考虑是否适合用CASE语句


--测试一： （3s）执行计划：表扫描 -> 哈希匹配
SET NOCOUNT ON
LEFT JOIN CHECK3_T2 B ON A.ID=B.ID AND B.C1='C1'
--5、 避免在条件列上使用任何函数

INSERT INTO CHECK3_T1 SELECT @I,'C1'

BEGIN

--=====================================

SET @I=1

INNER JOIN CHECK3_T1 B ON A.ID=B.ID WHERE A.C1='C1' --2W行
IF @COUNT>0
SET @I=1
、嵌套子查询，加上查询条件，确保子查询的结果集最小
select * from CHECK4_T1
CROSS apply sys.dm_exec_sql_text(b.sql_handle) d
BEGIN
)
WHILE @N<=1000
SELECT * FROM CHECK4_T1 WHERE C1='C2'
SELECT ID,C1 FROM CHECK1_T1 --1W条数据
INNER JOIN CHECK3_T1 B ON A.ID=B.ID WHERE B.C1='C1'


--查看缓存计划
SET NOCOUNT ON
SELECT A.ID,A.C1 FROM CHECK3_T1 A --2W行

EXEC sp_executesql @sql,
BEGIN
、Isnull,没有必要的时候不要对字段使用isnull，同样会产生无法有效利用索引的问题，
--测试二：（4s） 执行计划：表扫描-> 哈希匹配
BEGIN
---测试一:瞬时

CREATE TABLE CHECK1_T2
--总结：where条件里对索引字段使用了函数，会使索引查找变成索引扫描，从而查询效率大幅下降
BEGIN
SELECT * FROM CHECK5_T1

CROSS apply sys.dm_exec_query_plan(a.plan_handle) c,
BEGIN
--测试二：（0s）执行计划：表扫描-> 前几行
SET @I=1

(
C1 CHAR(8000)
SELECT @ID=ID FROM CHECK1_T1 WHERE C1='C1'
DECLARE @I INT
WHERE C1=(CASE @S WHEN 1 THEN C1 ELSE 'C2' END)
--测试三 执行计划：表扫描 -> 哈希匹配
--=====================================
IF @I%2=0
SELECT A.ID,A.C1 FROM CHECK3_T2 A
--总结：判断是否存在，用Exist即可，没必要用COUNT(*)将表的所有记录统计出来，扫描一次
--3、 IN（Select COL1 From Table）的代替方式
@ID INT
INNER JOIN CHECK3_T2 B ON A.ID=B.ID AND A.C1='C1'
SET @I=@I+1
ELSE
--总结：尽量不使用NOT IN ，因为会调用嵌套循环，建议使用NOT EXISTS代替NOT IN
BEGIN
DBCC FREEPROCCACHE


PRINT @count

ID INT,
CREATE TABLE CHECK4_T1

--总结：给变量赋值最好都加上TOP 1，一从查询效率上增强，二为了准确性，若表CHECK1_T1有多个值，则会取最后一条记录赋给@ID
--=====================================
usecounts '重用次数',

DBCC DROPCLEANBUFFERS

SET @I=@I+1
(
BEGIN
SELECT ID,C1 FROM CHECK3_T1 --2W行
--测试二
SELECT A.ID,A.C1 FROM CHECK3_T1 A
CREATE TABLE CHECK2_T1

/*--=====================================
--=====================================

--7、 Left Join 的替代法
--测试三：（3s）执行计划：表扫描-> 哈希匹配

AS
SET @sql = 'DECLARE @count INT;SELECT @count=count(*) FROM CHECK5_T1 WHERE ID = ' + CAST(@ID AS VARCHAR(10)) + ';PRINT @count'
SELECT @COUNT=COUNT(*) FROM CHECK2_T1 WHERE C1='C1' --1W条数据

--1、 Union all 代替 Union

ID INT,
DECLARE @I INT
SELECT * FROM CHECK4_T1 WHERE RTRIM(C1)='C2'
SELECT @ID
SET @I=@I+1
DECLARE @N INT
C1 CHAR(7000)


(
IF EXISTS(SELECT 1 FROM CHECK2_T1 WHERE C1='C1') --1W条数据
(
CREATE TABLE CHECK3_T1
END
@sql NVARCHAR(4000)

--总结：三条语句，在执行计划上完全一样，都是走的INNER JOIN的计划，
和避免在筛选列上使用函数同样的原理。
)
SET @sql = 'SELECT @count=count(*) FROM CHECK5_T1 WHERE ID = @ID'
RIGHT JOIN CHECK3_T2 B ON A.ID=B.ID WHERE a.C1='C1'

DECLARE @count INT,
ORDER BY total_elapsed_time / execution_count DESC;


C1 CHAR(8000)


C1 CHAR(500),

--测试一

--=====================================
END
INSERT INTO CHECK2_T1 SELECT @I,'C1'

UNION
C1 CHAR(8000)

--测试二 执行计划：表扫描 -> 哈希匹配
CREATE TABLE CHECK5_T1


WHERE @S=1 OR C1='C2'
SET @I=1

PRINT 'S'
DECLARE @sql NVARCHAR(4000)
)
CREATE NONCLUSTERED INDEX NCIX_C1 ON CHECK4_T1(C1)
@count OUTPUT,
ID INT,
END
INSERT INTO CHECK2_T1 VALUES(10001,'C2')
SELECT ID,C1 FROM CHECK1_T2 --1W条数据
EXEC UP_CHECK5_T2 @N
BEGIN

END
)
)
DBCC DROPCLEANBUFFERS
LEFT JOIN CHECK3_T2 B ON A.ID=B.ID WHERE B.C1='C1' --400行
*/
SELECT A.ID,A.C1 FROM CHECK3_T1 A
--8、 ON(a.id=b.id AND a.tag=3)


SET @I=@I+1
PRINT 'S'
INSERT INTO CHECK5_T1 SELECT @I,'C1'


--测试二： (4s) 执行计划：表扫描->表扫描串联

WHILE @I<=10000
(
BEGIN
C1 CHAR(10),

/*
BEGIN
DECLARE @COUNT INT
N'@count INT OUTPUT, @ID int',


DBCC DROPCLEANBUFFERS
BEGIN
--=====================================
--测试二


CREATE CLUSTERED INDEX CIX_ID ON CHECK5_T1(ID)

INSERT INTO CHECK3_T2 SELECT @I+50000,'C2'
DBCC DROPCLEANBUFFERS
--=====================================
----测试二: （0s） 执行计划：常量扫描/表扫描-> 嵌套循环-> 计算标量
--6、 用sp_executesql执行动态sql
END
SELECT A.ID,A.C1 FROM CHECK3_T1 A
EXEC(@sql)
SET @I=@I+1

SELECT A.ID,A.C1,B.C1 FROM CHECK3_T1 A
--测试一：（3s） 执行计划：表扫描
SELECT ID,C1 FROM CHECK3_T2 --400行
WHILE @I<=20000

SUBSTRING(d.text, (statement_start_offset / 2) + 1, ((CASE statement_end_offset
END
INSERT INTO CHECK1_T1 SELECT @I,'C1'


WHERE NOT EXISTS (SELECT 1 FROM CHECK3_T2 B WHERE B.ID=A.ID AND B.C1='C1')
SELECT a.size_in_bytes '占用字节数',

--2、 Exists 代替 Count(*)

--注意：准备数据（可略过，非常耗时）
)
INSERT INTO CHECK4_T1 SELECT @I,'C1'
SELECT A.ID,A.C1 FROM CHECK3_T1 A
WHERE ID IN (SELECT ID FROM CHECK3_T1 WHERE C1='C1') --2W行
ELSE
DROP TABLE CHECK4_T1
CREATE TABLE CHECK1_T1
CREATE TABLE CHECK3_T2
SET @N=1

INNER JOIN CHECK3_T2 B ON A.ID=B.ID WHERE A.C1='C1'

UNION ALL
--总结：通过执行下面缓存计划可以看出，第一种完全使用了缓存计划，查询达到了很好的效果；
INSERT INTO CHECK3_T2 SELECT @I,'C1'
IF @I%100=0

复制代码 代码如下: