September 15, 2009 at 7:03 pm
This page intentionally left blank.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
September 15, 2009 at 9:03 pm
Bob Hovious (9/15/2009)
I can't argue with "It depends."Lynn, I need a judgment call from an official:
Is the above a more polite way of saying "I agree" or will I be carded?
I'm doubling and redoubling Adventureworks to scale up the test. I'll have another look at the execution plans at a million rows plus.
I'm curious, Seth: Did you capture any times or stats for your million row test?
No card here Bob, you're good on this one.
September 16, 2009 at 2:03 am
Jonathan (9/15/2009)
Dave Ballantyne (9/15/2009)
This is the output i get from set statistics io on .Non Cte
Table 'SalesOrderDetail'. Scan count 31466, logical reads 103081, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
CTE Version
Table 'SalesOrderDetail'. Scan count 1, logical reads 1241, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
And your point is? The table is the table. Both queries cache the same data, so the above is not particularly relevant to the performance. Try taking actual timings; I always get the IN query as faster than the CTE query.
My only issue with a high number of logical reads is that , rightly or wrongly, i do attach some value to it in terms of , "What is poorly performing on my system".
I would have concerns that, If i were to use the subquery in a production stored procedure, then i would mentally associate all 'excessive' logical reads to that statement and not even consider that another query within the same proc had a high number of logical reads that had the potential to be physical reads under certain circumstances.
I know its a far from perfect indicator, but it is an indicator.
September 16, 2009 at 9:53 am
A) Its a single query (hardly long)
B) This doesnt work for multiple vehicles
C) What if there is a tie in duehours ?
Just curious. Why do you say it doesn't work for multiple vehicles? It returns the same result given the sample data provided.
Also, what if there IS a tie in due hours? It depends on the business rules. If I am doing maintenance on a vehicle and two procedures are due at the same time, I would like to know about it. I realize the poster asked for a single row for each vehicle, but why let that get in the way of a good discussion? 🙂 Using the RANK function instead of ROW_NUMBER would return the ties, if that was desired.
John
John Deupree
September 16, 2009 at 10:26 am
Fair question. The problem is that the sample data included no ties, so you never see a difference in behavior. Check out the example below. The CTE solution honors the OP's requirement to mean return only one row per vehicle. RANK() would certainly preserve ties, as do both of the subquery solutions, as you and Howard pointed out.
I agree with you that issues like that should be raised and let the discussion roll. The discussions are where most of the value comes from. Certainly nothing got in the way of THIS discussion. 🙂
declare @sample table (ID int, Vehicle int, DueHours numeric(5,1), DueName varchar(30))
insert into @sample
select 1, 55, 654.7, 'Engine Overhaul' union all
select 2, 55, 543.6, 'Tyre Balance' union all
select 3, 55, 987.4, 'Timing Belt' union all
select 4, 63, 843.5, 'Engine Overhaul' union all
select 5, 63, 432.2, 'Tyre Balance' union all
select 6, 63, 99, 'Same Due Hours #1' union all
select 7, 63, 99, 'Same Due Hours #2' union all
select 8, 63, 1098.3, 'Timing Belt'
-- cte
;with cte as (select *,ROW_NUMBER() over(partition by vehicle order by dueHours) as seqID from @sample )
select * from cte where seqID = 1
-- subquery1
select * from @sample A
where duehours= (select min(duehours) from @sample where vehicle= A.vehicle)
-- subquery2
SELECT ID, Vehicle, DueHours, DueName
FROM @sample s
WHERE DueHours IN
(SELECT MIN(DueHours)
FROM @sample
WHERE Vehicle = s.Vehicle);
__________________________________________________
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 5 posts - 46 through 49 (of 49 total)
You must be logged in to reply to this topic. Login to reply