September 22, 2010 at 9:15 am
below is a query to select a data set. I added the sub query in the from clause and joined upon it. I expected to have to add a where clause in the sub query, but instead just had to add one to the overall where clause. I thought execution would be recursive. i.e. the sub query would retrieve each row and only if it was valid against the joins and against the overall where clause would it be used.
I expected this to take ages to run but it doesnt. Is there something in the way sql server deals with sub queries that im missing? Does it inferr any items in the overall where clause to the sub query?
select aoc.*
from Table1 aoc
join Table2 ao on aoc.Table2id = ao.Table2id
join
(
select sum(optionIntegerValue) as 'points',offer.TransactionCode,offer.ItemGroupID,Offer.Table2StatusCode,offer.ContractID
from Table1 choices join Table2 offer
on choices.Table2ID = offer.Table2ID
where offer.Table2statuscode = 'HISTORY'
and choices.TransactionDetailCode = 'TOTALS'
group by offer.TransactionCode,offer.ItemGroupID,offer.Table2StatusCode,offer.agreementid
)
as HistTot
on HistTot.TransactionCode = ao.TransactionCode
and HistTot.ItemGroupID = ao.ItemGroupID
where aoc.TransactionDetailCode = 'TOTALS'
and ao.Table2statuscode = 'ACTIVE'
and HistTot.Table2StatusCode = 'HISTORY'
and ao.Table2id in
(
--list of values here
)
and HistTot.ContractID = --ContractID here
September 22, 2010 at 9:23 am
The optimiser is much more intelligent than that.
SQL's a declarative language, so when you use a sub query or CTE, it doesn't just take you literally, it reads the entire statement and tries to generate the best plan it can.
Having said that, a general rule of thumb is to always tell it what you already know if you can. The more information and selectivity you put in the query, the better chance of a good plan.
Paul White's (among others) the resident guru about this - he did a good series about how SQL de-constructs your statement to arrive at a plan:
http://www.sqlservercentral.com/articles/SQL+Server+2008/71019/
September 22, 2010 at 9:45 am
Having said that, it's refreshing to get a post from someone saying their query's running too fast 😀
September 23, 2010 at 11:38 am
I agree with Howard. Do you require assistance in slowing it down?
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply