sql语句对比

对比一:
  UPDATE A
     SET A.GAS_TOTAL_SALE = B.GAS_TOTAL_SALE
    FROM #SST_TEMP_GAS_PACK_RPT A, (SELECT GAS_ID, SUM(DAY_SALE_QTY) AS GAS_TOTAL_SALE
                                      FROM SST_DAY_TOTAL
                                     WHERE GAS_ID IN (SELECT GAS_ID FROM #GASTEMPTABLE)
                                       AND RPT_DATE BETWEEN CONVERT(VARCHAR(10),@DATE,120) AND  CONVERT(VARCHAR(10),@EDATE,120)
                                     GROUP BY GAS_ID
                                   ) B
   WHERE A.GAS_ID = B.GAS_ID; 
和如下语句对比:  
  SELECT GAS_ID, SUM(DAY_SALE_QTY) AS GAS_TOTAL_SALE INTO #TEMP1
    FROM SST_DAY_TOTAL
   WHERE GAS_ID IN (SELECT GAS_ID FROM #GASTEMPTABLE)
     AND RPT_DATE BETWEEN CONVERT(VARCHAR(10),@DATE,120) AND  CONVERT(VARCHAR(10),@EDATE,120)
   GROUP BY GAS_ID;
 
   UPDATE A
     SET A.GAS_TOTAL_SALE = B.GAS_TOTAL_SALE
    FROM #SST_TEMP_GAS_PACK_RPT A, #TEMP1 B
   WHERE A.GAS_ID = B.GAS_ID

   DROP TABLE #TEMP1;

可以看出: 第二个语句只是用到了临时表,由于sst_day_total 表的数据量非常大,所以对两中写法进行了测试对比
经过测试得出:
   查询一年的数据:
        第一个语句执行的时间为:0:03:31
        第二个语句执行的时间为:0:03:00
 

posted @ 2008-06-05 14:15  bella  阅读(473)  评论(1编辑  收藏  举报