July 31, 2013 at 12:37 am
Hello guys,
I have performance question or what is better for server
I have select
SELECT TOP 5 CONVERT(DATETIME,WOT.ORDER_DATE) ORDER_DATE, WOT.COMMIT_NO, WOT.BODY_NO, WOT.CAR_CODE, WOT.PROD_FLAG
FROM (
SELECT TOP 2 WO.ORDER_DATE, WO.COMMIT_NO, WO.BODY_NO, WO.CAR_CODE + ' ' + WO.DRIVE_TYPE CAR_CODE , 'T' PROD_FLAG
FROM TB_WORK_ORDER_TRSB1 WO
LEFT JOIN TB_TRACKING_TRSB1 TR
ON WO.ORDER_DATE = TR.ORDER_DATE AND WO.COMMIT_NO = TR.COMMIT_NO
WHERE WO.ORDER_DATE <= @vLAST_ORDER_DATE
AND WO.ORDER_DATE + WO.COMMIT_NO <= @vLAST_ORDERCOMMIT
AND DATA_TYPE <> 'SD' ORDER BY WO.ORDER_DATE DESC, WO.COMMIT_NO DESC
UNION ALL
SELECT WO.ORDER_DATE, WO.COMMIT_NO, WO.BODY_NO, WO.CAR_CODE + ' ' + WO.DRIVE_TYPE CAR_CODE
, CASE ISNULL(TR.WORK_COMPLETE,'')
WHEN '' THEN 'F'
WHEN 'F' THEN 'Y'
WHEN 'T' THEN 'G'
END PROD_FLAG
FROM TB_WORK_ORDER_TRSB1 WO
LEFT JOIN TB_TRACKING_TRSB1 TR
ON WO.ORDER_DATE = TR.ORDER_DATE AND WO.COMMIT_NO = TR.COMMIT_NO
WHERE WO.ORDER_DATE >= @vLAST_ORDER_DATE
AND WO.ORDER_DATE + WO.COMMIT_NO > @vLAST_ORDERCOMMIT
AND DATA_TYPE <> 'SD'
) WOT
And what do you think what is it better .. to each clausule WHERE add query below or OR add behind *TEMP* table WOT on end of query to WHERE ?
AND WO.ORDER_DATE+WO.COMMIT_NO IN
( SELECT distinct ORDER_DATE+COMMIT_NO FROM TB_MASTER_TRSB1
WHERE ORDER_DATE >= @vLAST_ORDER_DATE AND STATION_ID = @rSTATION_ID
)
Here are result from statistics
--- NEW with MASTER in each union
CPU time = 0 ms, elapsed time = 0 ms.
Table 'TB_TRACKING_TRSB1'. Scan count 2, logical reads 66, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 1, logical reads 1153, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TB_WORK_ORDER_TRSB1'. Scan count 2, logical reads 34, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TB_MASTER_TRSB1'. Scan count 2, logical reads 364, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 31 ms, elapsed time = 26 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 31 ms, elapsed time = 26 ms.
--- NEW with MASTER in TEMP WOT
Table 'Worktable'. Scan count 1, logical reads 1678, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TB_MASTER_TRSB1'. Scan count 2, logical reads 364, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TB_WORK_ORDER_TRSB1'. Scan count 2, logical reads 31, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TB_TRACKING_TRSB1'. Scan count 2, logical reads 7, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 31 ms, elapsed time = 25 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 31 ms, elapsed time = 26 ms.
What do you think about it ?
July 31, 2013 at 12:38 am
or now I tried left join with MASTER table
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TB_MASTER_TRSB1'. Scan count 753, logical reads 2775, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TB_WORK_ORDER_TRSB1'. Scan count 2, logical reads 54, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TB_TRACKING_TRSB1'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
July 31, 2013 at 1:51 am
Have a look at the two queries you are UNIONing. The WHERE clauses are the exact opposite (bar a small oversight, WO.ORDER_DATE >= @vLAST_ORDER_DATE should be WO.ORDER_DATE > @vLAST_ORDER_DATE). That means, without TOP (2), the UNION query would return the whole set, as if there were no WHERE clause at all. So why not figure out how to identify TOP (2) without splitting the set and reading it twice? Something like this:
SELECT
rn = ROW_NUMBER() OVER(PARTITION BY x.TOPfilter ORDER BY WO.ORDER_DATE DESC, WO.COMMIT_NO DESC),
x.TOPfilter,
WO.ORDER_DATE,
WO.COMMIT_NO,
WO.BODY_NO,
CAR_CODE = WO.CAR_CODE + ' ' + WO.DRIVE_TYPE,
PROD_FLAG = CASE
WHEN x.TOPfilter = 0 THEN 'T'
WHEN TR.WORK_COMPLETE = '' OR TR.WORK_COMPLETE IS NULL THEN 'F'
WHEN TR.WORK_COMPLETE = 'F' THEN 'Y'
WHEN TR.WORK_COMPLETE = 'T' THEN 'G'
ELSE NULL END
FROM TB_WORK_ORDER_TRSB1 WO
LEFT JOIN TB_TRACKING_TRSB1 TR
ON WO.ORDER_DATE = TR.ORDER_DATE
AND WO.COMMIT_NO = TR.COMMIT_NO
CROSS APPLY (
SELECT TOPfilter = CASE
WHEN WO.ORDER_DATE <= @vLAST_ORDER_DATE
AND WO.ORDER_DATE + WO.COMMIT_NO <= @vLAST_ORDERCOMMIT
THEN 0 ELSE 1 END
) x
WHERE DATA_TYPE <> 'SD'
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply