How the query is being executed if WHERE returns FALSE?

Question

How the query is being executed if WHERE returns FALSE?

JMI

SSChampion

Points: 11831
More actions
October 8, 2009 at 2:21 pm

#210756

Question.
If one of the criterias in my WHERE clause returns false
does it mean that the time spent on executing the whole SQL should be close to 1 seconds?
SELECT
@HIERARCHYID AS HierarchyID,
vv.COBDate,
TB.RootParent AS BusinessEntityShortName,
V.RiskMeasureShortDescr,
V.RiskIdShortName,
V.TimeBucketDays,
SUM(vv.Value * FX.Rate) AS Value,
FX.Cur AS VarCurrency
FROM
[Var] V
INNER JOIN HistVarValue vv (NOLOCK) ON V.VarID = vv.VarID
INNER JOIN xrefSourceTableHierarchy X (NOLOCK) ON vv.SourceID = X.SourceID
INNER JOIN #tmpBESNList TB ON V.BusinessEntityShortName = TB.BusinessEntityShortName
INNER JOIN #FXRates FX ON vv.COBDate = FX.COBDate AND vv.VarCurrency = FX.Cur
WHERE
vv.COBDate IN (@COBDATE1, @COBDATE2)
AND vv.COBDate <= GETDATE() - 7
ANDV.RiskMeasureShortDescr IN ('IRDlt', 'IRGma')
ANDV.RiskIdShortName IN (@P_RISKID)--IN ('CAD+CSW','USD+USW')--= @PARAMRISKID
AND(V.CurveRefShortDescr = @PARAMCURVE OR @PARAMCURVE IS NULL) -- i.e. if null, don't filter
ANDX.HierarchyID = 1
GROUP BY
vv.COBDate,
TB.RootParent,
V.RiskMeasureShortDescr,
V.RiskIdShortName,
V.TimeBucketDays,
FX.Cur
this returns false but the query still takes about 4 sec to run.
Why?
WHERE
vv.COBDate IN (@COBDATE1, @COBDATE2)

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

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

Garadin One Orange Chip Points: 29613 More actions · Answer 1

Just because your query doesn't return any rows, it doesn't mean that the server didn't have to read through a large amount of data to come to the conclusion that nothing matched your criteria. It does not know beforehand that nothing will match.

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

Dhimant Trivedi SSC Enthusiast Points: 126 More actions · Answer 2

Garadin (10/8/2009)
Just because your query doesn't return any rows, it doesn't mean that the server didn't have to read through a large amount of data to come to the conclusion that nothing matched your criteria. It does not know beforehand that nothing will match.

Furthermore, looking at the actual execution plan will prove this fact. You can locate and press the "Include Actual Execution Plan" button somewhere in the "SQL Editor" toolbar, if you are using Management Studio to run the query.

Thanks,
Dhimant