今天再次改老系统，看着前边的师兄留下的SQL，就想起了MS SQL 7.5和SQL2000。可能很多人都还没有习惯新版本（05版之后）的SQL语法，还是用以前开发MS SQL2000的方式实现功能，但是多少人会明白，换一种写法后，性能却相差十倍百倍呢！
我用来做例子的是我们MES系统用到的一段简单功能T－SQL，都是使用同一个库、相同的表、相同的逻辑：
开始前先记录时间，计算完成后看用了多少时间。
我先用推荐的语法来写的方式，代码1是好的写法，由于是在我们系统的情景下描述的，看管不可照抄，只说明性能的差别。
代码1：
用WITH，把各个逻辑分开处理，再终合出最后结果，推荐！
1: declare @P INT,@T DATETIME;
2: SELECT @P=1,@T=GETDATE();
3: ;WITH R0 --
4: AS (
5: SELECT TOP 1 M.WPCODE,t.RID,MACNO,LEFT(WFCNO,CHARINDEX('-',WFCNO)-1) WO FROM MES_WFCIO M,MES_WFCIODTL t
6: WHERE M.RID=t.RID AND WFCNO='JSAC00334D-033-001'
7: ORDER BY t.RID DESC
8: )
9: ,R1
10: AS (
11: SELECT TOP 1 M.WPCODE,t.RID,M.MACNO,LEFT(WFCNO,CHARINDEX('-',WFCNO)-1) WO FROM MES_WFCIO M,MES_WFCIODTL t,R0
12: WHERE M.RID=t.RID
13: AND M.MACNO=R0.MACNO
14: AND M.RID<>R0.RID
15: AND M.WPCODE=R0.WPCODE
16: ORDER BY t.RID DESC
17: )
18: ,R2
19: AS (
20: SELECT WO FROM R0
21: UNION
22: SELECT WO FROM R1
23: )
24: ,R3
25: AS (
26: SELECT DISTINCT MASTERNUM FROM R2,WO WHERE R2.WO=WO.WONUM
27: )
28: SELECT @P=COUNT(*) FROM R3;
29: select @P AS [計算結果1];
30: SELECT DATEDIFF(ms,@T,getdate()) as [方法1用時];
代码2：
这是2000的时候多数人用的方式，计算过程多用临时表，不推荐！
1: declare @P INT,@T DATETIME;
2: SELECT @P=1,@T=GETDATE();
3: SELECT TOP 1 M.WPCODE,t.RID,MACNO,LEFT(WFCNO,CHARINDEX('-',WFCNO)-1) WO
4: INTO #R0
5: FROM MES_WFCIO M,MES_WFCIODTL t
6: WHERE M.RID=t.RID AND WFCNO='JSAC00334D-033-001'
7: ORDER BY t.RID DESC;
8:
9: SELECT TOP 1 M.WPCODE,t.RID,M.MACNO,LEFT(WFCNO,CHARINDEX('-',WFCNO)-1) WO
10: INTO #R1
11: FROM MES_WFCIO M,MES_WFCIODTL t,#R0 R0
12: WHERE M.RID=t.RID
13: AND M.MACNO=R0.MACNO
14: AND M.RID<>R0.RID
15: AND M.WPCODE=R0.WPCODE
16: ORDER BY t.RID DESC;
17:
18: SELECT WO INTO #R2 FROM #R0
19: UNION
20: SELECT WO FROM #R1;
21:
22: SELECT DISTINCT MASTERNUM
23: INTO #R3
24: FROM #R2 R2,WO
25: WHERE R2.WO=WO.WONUM;
26:
27: SELECT @P=COUNT(*) FROM #R3;
28: select @P AS [計算結果2];
29: SELECT DATEDIFF(ms,@T,getdate()) as [方法2用時];
30:
31: drop table #R0,#R1,#R2,#R3;
代码3：
强逻辑人员的写法，子查询方式，不推荐！
1: declare @P INT,@T DATETIME;
2: SELECT @P=1,@T=GETDATE();
3: SELECT @P=COUNT(*)
4: FROM (SELECT DISTINCT MASTERNUM
5: FROM (SELECT WO
6: FROM (SELECT TOP 1 M.WPCODE,t.RID,MACNO,LEFT(WFCNO,CHARINDEX('-',WFCNO)-1) WO
7: FROM MES_WFCIO M,MES_WFCIODTL t
8: WHERE M.RID=t.RID AND WFCNO='JSAC00334D-033-001'
9: ORDER BY t.RID DESC) x
10: UNION
11: SELECT WO FROM (SELECT TOP 1 M.WPCODE,t.RID,M.MACNO,LEFT(WFCNO,CHARINDEX('-',WFCNO)-1) WO
12: FROM MES_WFCIO M,MES_WFCIODTL t,(SELECT TOP 1 M.WPCODE,t.RID,MACNO,LEFT(WFCNO,CHARINDEX('-',WFCNO)-1) WO
13: FROM MES_WFCIO M,MES_WFCIODTL t
14: WHERE M.RID=t.RID AND WFCNO='JSAC00334D-033-001'
15: ORDER BY t.RID DESC) R0
16: WHERE M.RID=t.RID
17: AND M.MACNO=R0.MACNO
18: AND M.RID<>R0.RID
19: AND M.WPCODE=R0.WPCODE
20: ORDER BY t.RID DESC)R1) R2,WO
21: WHERE R2.WO=WO.WONUM)R3;
22: select @P AS [計算結果3];
23: SELECT DATEDIFF(ms,@T,getdate()) as [方法3用時];
看看执行的结果对比：
第一种写法用时不到1毫秒；
第二种写法用时56毫秒；
第三种写法用时不到1毫秒；
再单独执行，代码1用了3毫秒，从Messages中看到只有两条信息，也就是只操作了两个结果表对象；
代码2用了63毫秒，从Messages中看到只有两条信息，也就是只操作了六个结果表对象；
代码3用了3毫秒，从Messages中看到只有两条信息，也就是只操作了两个结果表对象；
从以上的对比结果来看，用with和用子查询的方式性能差不多，但是子查询的写法代码很难维护，用WITH的方式逻辑明确，代码重用高，易维护，性能优越；用临时表的方式逻辑也明确，但是性能差。用WITH和用子查询的方式都可以用到SQL的同时执行，用临时表就只能是按顺序执行了。
当你要对SQL进行性能调优时，除了考虑索引、统计信息、分区表、硬件升级和归档数据外，也建议考虑评估SQL的写法。
原创，转载请注明出处：http://www.cnblogs.com/YIYUMENG
此内容由惯性聚合(RSS阅读器)自动聚合整理，仅供阅读参考。 原文来自 — 版权归原作者所有。