Query Running Slow due to IS NULL in Where Clause.

Question

Post reply

Query Running Slow due to IS NULL in Where Clause.

Satnam Singh

Hall of Fame

Points: 3542
More actions
October 8, 2011 at 6:06 am

#244337

I have a T-SQL query which has 3 conditions in the WHERE clause seperated by a AND operator.
If i comment the condition in the where clause having IS NULL the query executes fine within 2 minutes but when i execute the entire query it takes more than 2 hrs to execute.IS NULL in the where clause kills the performance.
Please can somebody help me in this.

Viewing 6 posts - 1 through 5 (of 5 total)

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Reexecute them and save the actual execution plans and post then here... sh could be an easy fix.

1 point if you can guess it by now!

Satnam Singh Hall of Fame Points: 3542 More actions · Answer 2

I will upload the execution plan in short while, meanwhile the query is as follows:

Select wooBillingAmount_Cp,worIsBillable,bcoUserValue1,bcoID,sctFullName_Cp,cstAddress1,cstAddress2,cstCity,convert(varchar(4000), worCstComment) as worCstComment,rptCstCountryShortDesc,rptCstCountryId,cstCompanyName,cstNum,rptCstStateShortDesc,rptCstStateId,cstPostCode,convert(datetime,convert(nvarchar, worFoundWhen, 107)) as worFoundWhen,worDiscussed,worEstTimeForRepairQty,dateadd(mi, -(IsNull((select tzlBiasInMinutes from vewShatimezoneLookup where tzlBeginwhen < rptFulfillingWorkorderCreateDate and tzlEndWhen >= rptFulfillingWorkorderCreateDate and tzlTimezone_stzIdFk = 0),0)),rptFulfillingWorkorderCreateDate) as rptFulfillingWorkorderCreateDate,FulfillingWorkorderCurrency,FulfillingWorkorderCurrencyId,FulfillingWorkorderCurrencySymbol,FulfillingWorkorderNum_Cp,rptFulfillingWorkorderQuoteNum,wotUserValue1,wotID,empFullName_Cp,empNumber,worPerformed,worLocation,convert(varchar(4000), worNote) as worNote,worPrice,worQuoteNum,wrsUserValue1,wrsID,wrtUserValue1,wrtID,worID,sitAddress1,sitAddress2,sitCity,rptSitCountryShortDesc,rptSitCountryId,sitName,sitNum,wooCallPriorExt,wooCallPriorData,SalesPerson,rptSitSalespersonNumber,rptSitStateShortDesc,rptSitStateId,sitPostCode,rptWooCurUserValue4,rptWooCurId,rptWooCurUserValue2,wooNum,wosUserValue1,wosID,dateadd(mi, -(IsNull((select tzlBiasInMinutes from vewShatimezoneLookup

where tzlBeginwhen < wooStatusChangeWhen and tzlEndWhen >= wooStatusChangeWhen and tzlTimezone_stzIdFk = 0),0)),wooStatusChangeWhen) as wooStatusChangeWhen,wooNumOrdinalPos,0 as Group1,0 as Group2,0 as Group3,0 as Group4,0 as Group5,0 as Group6,0 as Group7 from vewRptWooRepairProposalV4 where wooCompletedWhen between '9/1/2011' and '10/6/2011 11:59:59 PM' and ZoneID in (0,27947,27995,28195,28295,28645,28995,29046,29145,29395,29495,29546,29595,29596,29645,29697,29795,29995,30095,30446,30895,30945,30995,31345,31346,31347,31445,31647,31895,32145,32496,32645,32745,33245,33345,33995,34295,34545,34546,34595,34596,34645,34648,34745,34795,35395,36695,36745,37545,37695,38045,38145,38495,38695,38995,39445,39545,39595,39696,39745,39795,39895,40295,40545,40695,41045,41295,41595,41845,41895,41945,41995,43145,43196,43995,44145,44995,45295,46145,46395,46795,47595,47746,47747,47795,47796,47895,48595,48645,48945,49345,51695,51845,52195,53795,54195,55295,55395,55445,56295,56595,56995,57845,58545,58795,58845,59545,60545,61045,61595,61995,62145,62295,62595,62695,62745,62845,62945,63395,63445,63745,63895,64395,64945,65545,65546,65596,67245,67295,67345,67445,67495,67496,67545,67595,67596,67597,67645,67695,68145,69045,69896,70395,70795,71645,71695,72345,72495,72895,72995,73045,73445,74895,75295,75395,76145,78195,78545,78946,79095,0) and (FulFillingWorkorderId_Cp is null)

Lowell SSC Guru Points: 323518 More actions · Answer 3

reformatted for readability:

SELECT wooBillingAmount_Cp,

worIsBillable,

bcoUserValue1,

bcoID,

sctFullName_Cp,

cstAddress1,

cstAddress2,

cstCity,

CONVERT(VARCHAR(4000), worCstComment) AS worCstComment,

rptCstCountryShortDesc,

rptCstCountryId,

cstCompanyName,

cstNum,

rptCstStateShortDesc,

rptCstStateId,

cstPostCode,

CONVERT(DATETIME, CONVERT(NVARCHAR, worFoundWhen, 107)) AS worFoundWhen,

worDiscussed,

worEstTimeForRepairQty,

Dateadd(mi, -( Isnull((SELECT tzlBiasInMinutes

FROM vewShatimezoneLookup

WHERE tzlBeginwhen < rptFulfillingWorkorderCreateDate

AND tzlEndWhen >= rptFulfillingWorkorderCreateDate

AND tzlTimezone_stzIdFk = 0), 0) ), rptFulfillingWorkorderCreateDate) AS rptFulfillingWorkorderCreateDate,

FulfillingWorkorderCurrency,

FulfillingWorkorderCurrencyId,

FulfillingWorkorderCurrencySymbol,

FulfillingWorkorderNum_Cp,

rptFulfillingWorkorderQuoteNum,

wotUserValue1,

wotID,

empFullName_Cp,

empNumber,

worPerformed,

worLocation,

CONVERT(VARCHAR(4000), worNote) AS worNote,

worPrice,

worQuoteNum,

wrsUserValue1,

wrsID,

wrtUserValue1,

wrtID,

worID,

sitAddress1,

sitAddress2,

sitCity,

rptSitCountryShortDesc,

rptSitCountryId,

sitName,

sitNum,

wooCallPriorExt,

wooCallPriorData,

SalesPerson,

rptSitSalespersonNumber,

rptSitStateShortDesc,

rptSitStateId,

sitPostCode,

rptWooCurUserValue4,

rptWooCurId,

rptWooCurUserValue2,

wooNum,

wosUserValue1,

wosID,

Dateadd(mi, -( Isnull((SELECT tzlBiasInMinutes

FROM vewShatimezoneLookup

WHERE tzlBeginwhen < wooStatusChangeWhen

AND tzlEndWhen >= wooStatusChangeWhen

AND tzlTimezone_stzIdFk = 0), 0) ), wooStatusChangeWhen) AS wooStatusChangeWhen,

wooNumOrdinalPos,

0 AS Group1,

0 AS Group2,

0 AS Group3,

0 AS Group4,

0 AS Group5,

0 AS Group6,

0 AS Group7

FROM vewRptWooRepairProposalV4

WHERE wooCompletedWhen BETWEEN '9/1/2011' AND '10/6/2011 11:59:59 PM'

AND ZoneID IN ( 0, 27947, 27995, 28195,

28295, 28645, 28995, 29046,

29145, 29395, 29495, 29546,

29595, 29596, 29645, 29697,

29795, 29995, 30095, 30446,

30895, 30945, 30995, 31345,

31346, 31347, 31445, 31647,

31895, 32145, 32496, 32645,

32745, 33245, 33345, 33995,

34295, 34545, 34546, 34595,

34596, 34645, 34648, 34745,

34795, 35395, 36695, 36745,

37545, 37695, 38045, 38145,

38495, 38695, 38995, 39445,

39545, 39595, 39696, 39745,

39795, 39895, 40295, 40545,

40695, 41045, 41295, 41595,

41845, 41895, 41945, 41995,

43145, 43196, 43995, 44145,

44995, 45295, 46145, 46395,

46795, 47595, 47746, 47747,

47795, 47796, 47895, 48595,

48645, 48945, 49345, 51695,

51845, 52195, 53795, 54195,

55295, 55395, 55445, 56295,

56595, 56995, 57845, 58545,

58795, 58845, 59545, 60545,

61045, 61595, 61995, 62145,

62295, 62595, 62695, 62745,

62845, 62945, 63395, 63445,

63745, 63895, 64395, 64945,

65545, 65546, 65596, 67245,

67295, 67345, 67445, 67495,

67496, 67545, 67595, 67596,

67597, 67645, 67695, 68145,

69045, 69896, 70395, 70795,

71645, 71695, 72345, 72495,

72895, 72995, 73045, 73445,

74895, 75295, 75395, 76145,

78195, 78545, 78946, 79095, 0 )

AND ( FulFillingWorkorderId_Cp IS NULL )

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

Which ISNULL are you talking about???

I'm starting to think that the subquery might be the real perf hog here compared to that last where is null.

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 5

Satnam Singh (10/8/2011)
I will upload the execution plan in short while, meanwhile the query is as follows:
Select wooBillingAmount_Cp,worIsBillable,bcoUserValue1,bcoID,sctFullName_Cp,cstAddress1,cstAddress2,cstCity,convert(varchar(4000), worCstComment) as worCstComment,rptCstCountryShortDesc,rptCstCountryId,cstCompanyName,cstNum,rptCstStateShortDesc,rptCstStateId,cstPostCode,convert(datetime,convert(nvarchar, worFoundWhen, 107)) as worFoundWhen,worDiscussed,worEstTimeForRepairQty,dateadd(mi, -(IsNull((select tzlBiasInMinutes from vewShatimezoneLookup where tzlBeginwhen < rptFulfillingWorkorderCreateDate and tzlEndWhen >= rptFulfillingWorkorderCreateDate and tzlTimezone_stzIdFk = 0),0)),rptFulfillingWorkorderCreateDate) as rptFulfillingWorkorderCreateDate,FulfillingWorkorderCurrency,FulfillingWorkorderCurrencyId,FulfillingWorkorderCurrencySymbol,FulfillingWorkorderNum_Cp,rptFulfillingWorkorderQuoteNum,wotUserValue1,wotID,empFullName_Cp,empNumber,worPerformed,worLocation,convert(varchar(4000), worNote) as worNote,worPrice,worQuoteNum,wrsUserValue1,wrsID,wrtUserValue1,wrtID,worID,sitAddress1,sitAddress2,sitCity,rptSitCountryShortDesc,rptSitCountryId,sitName,sitNum,wooCallPriorExt,wooCallPriorData,SalesPerson,rptSitSalespersonNumber,rptSitStateShortDesc,rptSitStateId,sitPostCode,rptWooCurUserValue4,rptWooCurId,rptWooCurUserValue2,wooNum,wosUserValue1,wosID,dateadd(mi, -(IsNull((select tzlBiasInMinutes from vewShatimezoneLookup
where tzlBeginwhen < wooStatusChangeWhen and tzlEndWhen >= wooStatusChangeWhen and tzlTimezone_stzIdFk = 0),0)),wooStatusChangeWhen) as wooStatusChangeWhen,wooNumOrdinalPos,0 as Group1,0 as Group2,0 as Group3,0 as Group4,0 as Group5,0 as Group6,0 as Group7 from vewRptWooRepairProposalV4 where wooCompletedWhen between '9/1/2011' and '10/6/2011 11:59:59 PM' and ZoneID in (0,27947,27995,28195,28295,28645,28995,29046,29145,29395,29495,29546,29595,29596,29645,29697,29795,29995,30095,30446,30895,30945,30995,31345,31346,31347,31445,31647,31895,32145,32496,32645,32745,33245,33345,33995,34295,34545,34546,34595,34596,34645,34648,34745,34795,35395,36695,36745,37545,37695,38045,38145,38495,38695,38995,39445,39545,39595,39696,39745,39795,39895,40295,40545,40695,41045,41295,41595,41845,41895,41945,41995,43145,43196,43995,44145,44995,45295,46145,46395,46795,47595,47746,47747,47795,47796,47895,48595,48645,48945,49345,51695,51845,52195,53795,54195,55295,55395,55445,56295,56595,56995,57845,58545,58795,58845,59545,60545,61045,61595,61995,62145,62295,62595,62695,62745,62845,62945,63395,63445,63745,63895,64395,64945,65545,65546,65596,67245,67295,67345,67445,67495,67496,67545,67595,67596,67597,67645,67695,68145,69045,69896,70395,70795,71645,71695,72345,72495,72895,72995,73045,73445,74895,75295,75395,76145,78195,78545,78946,79095,0) and (FulFillingWorkorderId_Cp is null)

Please see the "Pet Peeves" section of the following article...

http://www.sqlservercentral.com/articles/Best+Practices/61537/

--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)