I had to review some code, and came across something that someone did, and can't think of a reason why my way is better and it probably isn't, so, which is better/safer/more efficient?
SELECT MAX(a_date) FROM a_table WHERE a_primary_key = 5 GROUP BY event_id
OR
SELECT TOP 1 a_date FROM a_table WHERE a_primary_key = 5 ORDER BY a_date
I would have gone with the 2nd option, but I'm not sure why, and if that's right.
Select Max® Herbicide with Inside Technology™ With adjuvant and tank mix flexibility, Select Max is a postemergence herbicide that offers total performance including the fastest, most powerful way to control volunteer corn and other grasses in many row and specialty crops.
SELECT date(`date`), (max(value) - min(value)) as value, FROM `sub_meter_data` where date(sub_meter_data. date) > '2012-10-01' and sub_meterID in('58984','58985','58986','58987') group by date(`date`);
1) When there is a clustered index on the table and the column to be queried, both the MAX()
operator and the query SELECT TOP 1
will have almost identical performance.
2) When there is no clustered index on the table and the column to be queried, the MAX()
operator offers the better performance.
Reference: http://www.johnsansom.com/performance-comparison-of-select-top-1-verses-max/
Performance is generally similar, if your table is indexed.
Worth considering though: Top
usually only makes sense if you're ordering your results (otherwise, top
of what?)
Ordering a result requires more processing.
Min doesn't always require ordering. (Just depends, but often you don't need order by or group by, etc.)
In your two examples, I'd expect speed / x-plan to be very similar. You can always turn to your stats to make sure, but I doubt the difference would be significant.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With