两个SQL结果雷同性能差别10000倍

2013-09-05

两个SQL结果相同性能差别10000倍SQL1是两个SQL语句,SQL2是一个SQL语句，系统某些限制只能用第二种模式，SQL2

两个SQL结果相同性能差别10000倍
SQL1是两个SQL语句,SQL2是一个SQL语句，系统某些限制只能用第二种模式，SQL2是否有改进的空间？

rt_TestData_SWG 170GB(数据和索引总计)
rt_TestRlt      17GB(数据和索引总计)
rt_prdSNTrace   12GB(数据和索引总计)

SQL1(执行时间1秒):
declare @table table(PrdID varchar(50)) insert into  @table  select PrdID  from rt_prdsntrace with(nolock) where sbr='SBRE11s-4T'
SELECT PrdSN as SERIAL_NUM,paraSpecial1 AS hdPos,inDTime AS testTime,
ISNULL(MAX(CASE WHEN ParamName='EVENT_STATUS' THEN ParamValue ELSE NULL END),'NULL') AS EVENT_STATUS
FROM (SELECT rt_prdSNTrace.PrdSN,
rt_TestRlt.inDTime,rt_TestData.paraSpecial1,
rt_TestData.ParamName,rt_TestData.ParamValue
FROM rt_prdSNTrace WITH(NOLOCK)
join @table xx on xx.PrdID=rt_prdSNTrace.PrdID
JOIN rt_TestRlt WITH(NOLOCK) ON rt_prdSNTrace.PrdID = rt_TestRlt.PrdID
JOIN rt_TestData_SWG AS rt_TestData WITH(NOLOCK) ON rt_TestRlt.TestTraceID = rt_TestData.TestID
WHERE rt_TestRlt.ProcessSiteNo='SWG'
) AS TABLE_TMP
GROUP BY PrdSN,paraSpecial1,inDTime

SQL2(执行时间13分钟):
SELECT PrdSN as SERIAL_NUM,paraSpecial1 AS hdPos,inDTime AS testTime,
ISNULL(MAX(CASE WHEN ParamName='EVENT_STATUS' THEN ParamValue ELSE NULL END),'NULL') AS EVENT_STATUS
FROM (SELECT rt_prdSNTrace.PrdSN,rt_TestRlt.inDTime,rt_TestData.paraSpecial1,rt_TestData.ParamName,rt_TestData.ParamValue
FROM rt_prdSNTrace WITH(NOLOCK)
JOIN rt_TestRlt WITH(NOLOCK) ON rt_prdSNTrace.PrdID = rt_TestRlt.PrdID
JOIN rt_TestData_SWG AS rt_TestData WITH(NOLOCK) ON rt_TestRlt.TestTraceID = rt_TestData.TestID
WHERE rt_TestRlt.ProcessSiteNo='SWG' and sbr='SBRE11s-4T'
) AS TABLE_TMP
GROUP BY PrdSN,paraSpecial1,inDTime

两个SQL结果雷同性能差别10000倍
[解决办法]
把需要返回的字段列出来,再不行的话LZ可以私信找我,远程帮你看看..


SELECT PrdSN as SERIAL_NUM,
       paraSpecial1 AS hdPos,
       inDTime AS testTime,
       ISNULL(MAX(CASE WHEN ParamName='EVENT_STATUS' THEN ParamValue ELSE NULL END),'NULL') AS EVENT_STATUS
FROM 
(SELECT rt_prdSNTrace.PrdSN,rt_TestRlt.inDTime,rt_TestData.paraSpecial1,
        rt_TestData.ParamName,rt_TestData.ParamValue
 FROM (select [字段列表] from rt_prdSNTrace WITH(NOLOCK) where sbr='SBRE11s-4T') rt_prdSNTrace --> 这里仅将需要的字段返回即可.
 JOIN rt_TestRlt WITH(NOLOCK) ON rt_prdSNTrace.PrdID = rt_TestRlt.PrdID
 JOIN rt_TestData_SWG AS rt_TestData WITH(NOLOCK) ON rt_TestRlt.TestTraceID = rt_TestData.TestID
 WHERE rt_TestRlt.ProcessSiteNo='SWG'
) AS TABLE_TMP
GROUP BY PrdSN,paraSpecial1,inDTime
option (force order)

[解决办法]

引用:

非常感谢大家的回复！
输出结果3000行左右
回复2楼，区别是SQL1执行只要1秒和SQL2执行需要13分钟
回复4楼的办法已经测试过，没有明显的改善，
回复3楼预测完全正确，但系统使用环境限制了只能用select，那么用单个SQL语句如何解决这个问题呢？

--#1.封装个存储过程最方便
--#2.得用参数化查询+参数嗅探
DECLARE @sbr VARCHAR(100)
SET @sbr = 'SBRE11s-4T'

SELECT  PrdSN AS SERIAL_NUM ,
        paraSpecial1 AS hdPos ,
        inDTime AS testTime ,
        ISNULL(MAX(CASE WHEN ParamName = 'EVENT_STATUS' THEN ParamValue
                        ELSE NULL
                   END), 'NULL') AS EVENT_STATUS
FROM    ( SELECT    rt_prdSNTrace.PrdSN ,
                    rt_TestRlt.inDTime ,
                    rt_TestData.paraSpecial1 ,
                    rt_TestData.ParamName ,
                    rt_TestData.ParamValue

          FROM      rt_prdSNTrace WITH ( NOLOCK )
                    JOIN rt_TestRlt WITH ( NOLOCK ) ON rt_prdSNTrace.PrdID = rt_TestRlt.PrdID
                    JOIN rt_TestData_SWG AS rt_TestData WITH ( NOLOCK ) ON rt_TestRlt.TestTraceID = rt_TestData.TestID
          WHERE     rt_TestRlt.ProcessSiteNo = 'SWG'
                    AND sbr = @sbr
        ) AS TABLE_TMP
GROUP BY PrdSN ,
        paraSpecial1 ,
        inDTime
OPTION (OPTIMIZE FOR ( @sbr = '数据量最小的那个sbr值' ))

热点排行

Bad Request.

SQL Server

两个SQL结果雷同性能差别10000倍