SQL调优日记之发挥SQL性能与你的写法有关－－对比三种方式实现相同功能

今天再次改老系统，看着前边的师兄留下的SQL，就想起了MS SQL 7.5和SQL2000。可能很多人都还没有习惯新版本（05版之后）的SQL语法，还是用以前开发MS SQL2000的方式实现功能，但是多少人会明白，换一种写法后，性能却相差十倍百倍呢！

我用来做例子的是我们MES系统用到的一段简单功能T－SQL，都是使用同一个库、相同的表、相同的逻辑：

开始前先记录时间，计算完成后看用了多少时间。

我先用推荐的语法来写的方式，代码1是好的写法，由于是在我们系统的情景下描述的，看管不可照抄，只说明性能的差别。

 

代码1：

用WITH，把各个逻辑分开处理，再终合出最后结果，推荐！

declare @P INT,@T DATETIME;
SELECT @P=1,@T=GETDATE();
;WITH R0 --
    AS (
        SELECT TOP 1 M.WPCODE,t.RID,MACNO,LEFT(WFCNO,CHARINDEX('-',WFCNO)-1) WO FROM MES_WFCIO M,MES_WFCIODTL t
            WHERE M.RID=t.RID AND WFCNO='JSAC00334D-033-001'
            ORDER BY t.RID DESC
    )
    ,R1
    AS (
        SELECT TOP 1 M.WPCODE,t.RID,M.MACNO,LEFT(WFCNO,CHARINDEX('-',WFCNO)-1) WO FROM MES_WFCIO M,MES_WFCIODTL t,R0
            WHERE M.RID=t.RID
            AND M.MACNO=R0.MACNO
            AND M.RID<>R0.RID
            AND M.WPCODE=R0.WPCODE
            ORDER BY t.RID DESC
    )
    ,R2
    AS (
        SELECT WO FROM R0
        UNION
        SELECT WO FROM R1
    )
    ,R3
    AS (
        SELECT DISTINCT MASTERNUM FROM R2,WO WHERE R2.WO=WO.WONUM
    )
    SELECT @P=COUNT(*) FROM R3;
    select @P AS [計算結果1];
SELECT DATEDIFF(ms,@T,getdate()) as [方法1用時];

 

代码2：

这是2000的时候多数人用的方式，计算过程多用临时表，不推荐！

declare @P INT,@T DATETIME;
SELECT @P=1,@T=GETDATE();
SELECT TOP 1 M.WPCODE,t.RID,MACNO,LEFT(WFCNO,CHARINDEX('-',WFCNO)-1) WO
    INTO #R0
    FROM MES_WFCIO M,MES_WFCIODTL t
    WHERE M.RID=t.RID AND WFCNO='JSAC00334D-033-001'
    ORDER BY t.RID DESC;

SELECT TOP 1 M.WPCODE,t.RID,M.MACNO,LEFT(WFCNO,CHARINDEX('-',WFCNO)-1) WO
    INTO #R1
    FROM MES_WFCIO M,MES_WFCIODTL t,#R0 R0
    WHERE M.RID=t.RID
    AND M.MACNO=R0.MACNO
    AND M.RID<>R0.RID
    AND M.WPCODE=R0.WPCODE
    ORDER BY t.RID DESC;

SELECT WO INTO #R2 FROM #R0
UNION
SELECT WO FROM #R1;

SELECT DISTINCT MASTERNUM
    INTO #R3
     FROM #R2 R2,WO
    WHERE R2.WO=WO.WONUM;

SELECT @P=COUNT(*) FROM #R3;
select @P AS [計算結果2];
SELECT DATEDIFF(ms,@T,getdate()) as [方法2用時];

drop table #R0,#R1,#R2,#R3;

 

代码3：

强逻辑人员的写法，子查询方式，不推荐！

declare @P INT,@T DATETIME;
SELECT @P=1,@T=GETDATE();
SELECT @P=COUNT(*)
    FROM (SELECT DISTINCT MASTERNUM
        FROM (SELECT WO
                FROM (SELECT TOP 1 M.WPCODE,t.RID,MACNO,LEFT(WFCNO,CHARINDEX('-',WFCNO)-1) WO
                        FROM MES_WFCIO M,MES_WFCIODTL t
                        WHERE M.RID=t.RID AND WFCNO='JSAC00334D-033-001'
                        ORDER BY t.RID DESC) x
                    UNION
                    SELECT WO FROM (SELECT TOP 1 M.WPCODE,t.RID,M.MACNO,LEFT(WFCNO,CHARINDEX('-',WFCNO)-1) WO
            FROM MES_WFCIO M,MES_WFCIODTL t,(SELECT TOP 1 M.WPCODE,t.RID,MACNO,LEFT(WFCNO,CHARINDEX('-',WFCNO)-1) WO
                FROM MES_WFCIO M,MES_WFCIODTL t
                WHERE M.RID=t.RID AND WFCNO='JSAC00334D-033-001'
                ORDER BY t.RID DESC) R0
            WHERE M.RID=t.RID
            AND M.MACNO=R0.MACNO
            AND M.RID<>R0.RID
            AND M.WPCODE=R0.WPCODE
            ORDER BY t.RID DESC)R1) R2,WO
        WHERE R2.WO=WO.WONUM)R3;
select @P AS [計算結果3];
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