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

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

image

 

 

再单独执行,代码1用了3毫秒,从Messages中看到只有两条信息,也就是只操作了两个结果表对象;

image

image

 

代码2用了63毫秒,从Messages中看到只有两条信息,也就是只操作了六个结果表对象;

image

image

 

代码3用了3毫秒,从Messages中看到只有两条信息,也就是只操作了两个结果表对象;

image

image

 

从以上的对比结果来看,用with和用子查询的方式性能差不多,但是子查询的写法代码很难维护,用WITH的方式逻辑明确,代码重用高,易维护,性能优越;用临时表的方式逻辑也明确,但是性能差。用WITH和用子查询的方式都可以用到SQL的同时执行,用临时表就只能是按顺序执行了。

当你要对SQL进行性能调优时,除了考虑索引、统计信息、分区表、硬件升级和归档数据外,也建议考虑评估SQL的写法。

 

原创,转载请注明出处:http://www.cnblogs.com/YIYUMENG

posted @ 2012-05-15 21:57  谢堂文(Darren Xie)  阅读(1971)  评论(4编辑  收藏  举报