求教关于执行速度的问题 - SQL Server论坛

楼主

问题点数：0 回复次数：1

求教关于执行速度的问题

求教一下，以下两段SQL代码，执行的结果都是一样，但是，第一段1秒就可以了，但是第二段至少20多秒，为什么会差那么多啊？

程序代码：

DECLARE @I INT
SET @I=12

IF Object_id('tempdb..#T2') IS NOT NULL 
DROP TABLE #T2 

SELECT LEFT(TG003,6) MON,CAST(SUM((LP-TH018)*TH015) AS DECIMAL(16,2)) num INTO #T2
FROM (
SELECT TG003,TH015,TH018*TG008 TH018,
ISNULL((
SELECT TOP(1)TH.TH018*TG.TG008
FROM puri..PURTG TG,puri..PURTH TH
WHERE TG.TG013='Y' AND TG.TG001=TH.TH001 AND TG.TG002=TH.TH002 AND TH.TH004=H.TH004 AND H.TH008=TH.TH008
AND TH.TH015>0 AND TH.TH018>0
AND TG.TG003<G.TG003
AND TH.TH011<>H.TH011 AND TH.TH012<>H.TH012 AND TH.TH013<>H.TH013
ORDER BY TG.TG003 DESC
),0) LP
FROM puri..PURTG G
INNER JOIN puri..PURTH H ON TG001=TH001 AND TG002=TH002 AND TH015>0 AND TH018>0
WHERE TG013='Y'
AND CASE WHEN ISDATE(TG003)=1 THEN DATEDIFF(MM,TG003,GETDATE()) ELSE -1 END>=0 
AND CASE WHEN ISDATE(TG003)=1 THEN DATEDIFF(MM,TG003,GETDATE()) ELSE -1 END<@I*2 
-- AND (SELECT TC011 FROM puri..PURTC WHERE TC001=TH011 AND TC002=TH012)=''
) T
WHERE LP>0
GROUP BY LEFT(TG003,6)

SELECT CASE WHEN ISDATE(D.date+'01')=1 THEN DATEDIFF(MM,GETDATE(),D.date+'01') ELSE 9999 END+@I-1 row,
D.date,ISNULL(num,0) num
FROM getDateTable(1,GETDATE(),@I*2) D
LEFT JOIN #T2 L ON L.MON COLLATE Chinese_PRC_CI_AS=D.date

程序代码：

DECLARE @I INT
SET @I=12

SELECT CASE WHEN ISDATE(D.date+'01')=1 THEN DATEDIFF(MM,GETDATE(),D.date+'01') ELSE 9999 END+@I-1 row,
D.date,ISNULL(num,0) num 
FROM getDateTable(1,GETDATE(),@I*2) D
LEFT JOIN (
SELECT LEFT(TG003,6) MON,CAST(SUM((LP-TH018)*TH015) AS DECIMAL(16,2)) num
FROM (
SELECT TG003,TH015,TH018*TG008 TH018,
ISNULL((
SELECT TOP(1)TH.TH018*TG.TG008
FROM puri..PURTG TG,puri..PURTH TH
WHERE TG.TG013='Y' AND TG.TG001=TH.TH001 AND TG.TG002=TH.TH002 AND TH.TH004=H.TH004 AND H.TH008=TH.TH008
AND TH.TH015>0 AND TH.TH018>0
AND TG.TG003<G.TG003
AND TH.TH011<>H.TH011 AND TH.TH012<>H.TH012 AND TH.TH013<>H.TH013
ORDER BY TG.TG003 DESC
),0) LP
FROM puri..PURTG G
INNER JOIN puri..PURTH H ON TG001=TH001 AND TG002=TH002 AND TH015>0 AND TH018>0
WHERE TG013='Y'
AND CASE WHEN ISDATE(TG003)=1 THEN DATEDIFF(MM,TG003,GETDATE()) ELSE -1 END>=0 
AND CASE WHEN ISDATE(TG003)=1 THEN DATEDIFF(MM,TG003,GETDATE()) ELSE -1 END<@I*2 
-- AND (SELECT TC011 FROM puri..PURTC WHERE TC001=TH011 AND TC002=TH012)=''
) T
WHERE LP>0
GROUP BY LEFT(TG003,6)
) L ON L.MON COLLATE Chinese_PRC_CI_AS=D.date

搜索更多相关主题的帖子: color　