[转载]SQL调优日记之发挥SQL性能与你的写法有关-Mikel

[转载]SQL调优日记之发挥SQL性能与你的写法有关－－对比三种方式实现相同功能 – 谢堂文(Darren Xie) – 博客园.

今天再次改老系统，看着前边的师兄留下的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&lt;&gt;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&lt;&gt;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&lt;&gt;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毫秒；