August 8, 2011 at 10:31 pm
Hi,
I need some help!
I have a table with assets that have multiple Hubo Meters and multiple reading per Hubo meter.
What I need to do is select the top 15 readings per Hubo meter per asset.
Some assets can and will have more than 15 readings and some will have less than 15.
here is my script to extract all hubo meters and all readings
select
at.AssetID,
at.CustomerIdentifier as Customer,
mhr.Reading,
mhr.WhenOccurred,
aa.AnythingName as HuboMeter
from Asset.Asset at (nolock)
join core.Relationship r (nolock) on at.AssetID = r.ObjectA
join core.RelationshipType rt (nolock) on r.RelationshipTypeID = rt.RelationshipTypeID
join ruc.ManualHubodometerReading mhr (nolock) on r.RelationshipID = mhr.RelationshipID
join core.Anything a (nolock) on mhr.ManualHubodometerReadingID = a.AnythingID
join core.Anything aa (nolock) on r.ObjectB = aa.AnythingID
where rt.RelationshipType = 'Asset_Hubodometer'
and a.IsDeleted = '0'
--and at.CustomerIdentifier = 'P0081'
order by at.CustomerIdentifier
Thanks,
Regards
Greg
August 8, 2011 at 11:00 pm
Use ROW_NUMBER() function and partition by AssetID and HuboMeter. ORDER BY according to your business needs.
Then do a SELECT from the query above and use the value from the row_number function in the outer WHERE clause, like this
SELECT *
FROM cteAbove
WHERE rn <= 15
N 56°04'39.16"
E 12°55'05.25"
August 9, 2011 at 4:49 pm
Thanks for this. It looks like it will work for me
Regards
Greg
August 9, 2011 at 4:55 pm
Actually the real problem with your query is the nolock hint.
use it only if you don't care about the accuracy of the results and the connection no getting whacked by the server...
http://sqlblog.com/blogs/andrew_kelly/archive/2009/04/10/how-dirty-are-your-reads.aspx
Hey Peso, long time no "c".
August 10, 2011 at 3:11 pm
Sometimes I find that using a CROSS APPLY works out better than row numbering performance wise.
Here's your code with a CROSS APPLY to the ManualHubodometerReading instead of a JOIN:
select
at.AssetID,
at.CustomerIdentifier as Customer,
mhr.Reading,
mhr.WhenOccurred,
aa.AnythingName as HuboMeter
from Asset.Asset at (nolock)
join core.Relationship r (nolock) on at.AssetID = r.ObjectA
join core.RelationshipType rt (nolock) on r.RelationshipTypeID = rt.RelationshipTypeID
--join ruc.ManualHubodometerReading mhr (nolock) on r.RelationshipID = mhr.RelationshipID
CROSS APPLY
(SELECT TOP 15 M.Reading, M.WhenOccurred, M.ManualHubodometerReadingID
FROM ruc.ManualHubodometerReading M
WHERE M.RelationshipID = R.RelationshipID
ORDER BY M.Reading DESC
) AS MHR
join core.Anything a (nolock) on mhr.ManualHubodometerReadingID = a.AnythingID
join core.Anything aa (nolock) on r.ObjectB = aa.AnythingID
where rt.RelationshipType = 'Asset_Hubodometer'
and a.IsDeleted = '0'
--and at.CustomerIdentifier = 'P0081'
order by at.CustomerIdentifier
You would have to try both methods to see what performed better. For this type of thing I usually prefer the CROSS APPLY method.
Todd Fifield
August 11, 2011 at 3:47 pm
Hmm, I'd never thought of using a CROSS APPLY in that fashion. Good thinking there!
August 12, 2011 at 12:09 pm
Bruce W Cassidy (8/11/2011)
Hmm, I'd never thought of using a CROSS APPLY in that fashion. Good thinking there!
Bruce,
If you think about it, ROW_NUMBER() probably has to touch each row in order to number it. I haven't actually completely examined query plans where it was used and then limited to just a few in the WHERE clause, but I would think it would kind of have to. It could be that the optimizer is smart enough to stop after row 15 is processed, but I kind of doubt it.
The CROSS APPLY may not have to touch all rows in the join if the ORDER BY column is part of the same index used to join the tables. It will probably stop after the first 15 have been found. If the ORDER BY column is not part of the index, then it would probably have to do the same work - i.e. sort all the matches and weed out all but 15.
I'll have to devise some tests that will bench mark the 2 versions and examine the actual query plans involved - estimated against actual and see if there is a difference. Also the reads and CPU time involved in them. Maybe I'll do that and write an article on the results.
Todd Fifield
August 12, 2011 at 2:04 pm
All you need to do is answer how can the optimzer stop at only 15 rows knowing that sorting is a blocking operator (nothing else can run untill that is 100% complete).
August 12, 2011 at 3:06 pm
Ninja's_RGR'us (8/12/2011)
All you need to do is answer how can the optimzer stop at only 15 rows knowing that sorting is a blocking operator (nothing else can run untill that is 100% complete).
If it's got a pre-sorted index it can use, then it shouldn't need to do a sort.
August 12, 2011 at 3:46 pm
Bruce W Cassidy (8/12/2011)
Ninja's_RGR'us (8/12/2011)
All you need to do is answer how can the optimzer stop at only 15 rows knowing that sorting is a blocking operator (nothing else can run untill that is 100% complete).If it's got a pre-sorted index it can use, then it shouldn't need to do a sort.
Depends, only if that index is also the best way to filter and access the data. 😉
August 12, 2011 at 7:14 pm
Ninja's_RGR'us (8/12/2011)
All you need to do is answer how can the optimzer stop at only 15 rows knowing that sorting is a blocking operator (nothing else can run untill that is 100% complete).
Good catch Remi
Todd Fifield
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply