Wrong statistics and IO with CASE

Question

Post reply

Wrong statistics and IO with CASE

tony28

SSCertifiable

Points: 7112
More actions
November 8, 2013 at 9:07 pm

#300123

Hello,
it is possible that if I use column = CASE when etc.. will use wrong statistics and lot of logical reads ? I see first, because first time I have to use two case in where clausule... and problem is if I will have not specific variable A.COLUMN=A.COLUMN...
SELECT * FROM TB_WORK_ORDER_STEN1 WO
LEFT JOIN dbo.TB_WORK_ORDER_ENG01 (NOLOCK) ENG
ON WO.ORDER_DATE = ENG.ORDER_DATE AND WO.COMMIT_NO=ENG.COMMIT_NO
AND WO.BODY_NO = ENG.BODY_NO
WHERE WO.ORDER_DATE BETWEEN @p_START_DATE AND @p_END_DATE
AND WO.BODY_NO = CASE WHEN @p_BODY_NO = '' THEN WO.BODY_NO ELSE @p_BODY_NO END
AND ISNULL(WO.PROD_SDATE,'N') = CASE @p_WORK_FLAG
WHEN '*' THEN ISNULL(WO.PROD_SDATE,'N')
WHEN 'Y' THEN ISNULL(WO.PROD_SDATE,'')
WHEN 'N' THEN 'N'
END
ORDER BY WO.ORDER_DATE DESC, WO.COMMIT_NO DESC OPTION(RECOMPILE)
Table 'TB_WORK_ORDER_ENG01'. Scan count 0, logical reads 3723,
Table 'TB_WORK_ORDER_STEN1'. Scan count 1, logical reads 67, p
ANd in ex.plan shows wrong statistics , when I delete the one of the case
looks like this
Table 'Worktable'. Scan count 0, logical reads 0,
Table 'TB_WORK_ORDER_STEN1'. Scan count 1, logical reads 67,
Table 'TB_WORK_ORDER_ENG01'. Scan count 1, logical reads 69,
Did somebody see some like this ?? Or did u make some like this ?
thx

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Ville-Pekka Vahteala SSCrazy Points: 2458 More actions · Answer 1

Ville-Pekka Vahteala

SSCrazy

Points: 2458

November 9, 2013 at 7:40 am

#1665301

Can you post actual execution plans for both clauses.

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 2

tony28 (11/8/2013)
Hello,
it is possible that if I use column = CASE when etc.. will use wrong statistics and lot of logical reads ? I see first, because first time I have to use two case in where clausule... and problem is if I will have not specific variable A.COLUMN=A.COLUMN...
SELECT * FROM TB_WORK_ORDER_STEN1 WO
LEFT JOIN dbo.TB_WORK_ORDER_ENG01 (NOLOCK) ENG
ON WO.ORDER_DATE = ENG.ORDER_DATE AND WO.COMMIT_NO=ENG.COMMIT_NO
AND WO.BODY_NO = ENG.BODY_NO
WHERE WO.ORDER_DATE BETWEEN @p_START_DATE AND @p_END_DATE
AND WO.BODY_NO = CASE WHEN @p_BODY_NO = '' THEN WO.BODY_NO ELSE @p_BODY_NO END
AND ISNULL(WO.PROD_SDATE,'N') = CASE @p_WORK_FLAG
WHEN '*' THEN ISNULL(WO.PROD_SDATE,'N')
WHEN 'Y' THEN ISNULL(WO.PROD_SDATE,'')
WHEN 'N' THEN 'N'
END
ORDER BY WO.ORDER_DATE DESC, WO.COMMIT_NO DESC OPTION(RECOMPILE)
Table 'TB_WORK_ORDER_ENG01'. Scan count 0, logical reads 3723,
Table 'TB_WORK_ORDER_STEN1'. Scan count 1, logical reads 67, p
ANd in ex.plan shows wrong statistics , when I delete the one of the case
looks like this
Table 'Worktable'. Scan count 0, logical reads 0,
Table 'TB_WORK_ORDER_STEN1'. Scan count 1, logical reads 67,
Table 'TB_WORK_ORDER_ENG01'. Scan count 1, logical reads 69,
Did somebody see some like this ?? Or did u make some like this ?
thx

It's probably not the CASE. More likely, it's the ISNULL()'s that you're using on the columns bit inside and outside that particular case. They could be making it impossible to get an Index Seek or might even be forcing a CI scan (same as a table scan).

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)