MAX() vs. TOP 1 (fastfirstrow) DESC?
-
How does the performance of the function "MAX(primary_key)" and the SQL statement "SELECT TOP 1 primary_key FROM dbo.tbl (fastfirstrow) ORDER BY primary_key DESC" compare (Especially when the column "primary_key" is numeric is the only primary key)?
-
Still no time to open BOL?
_____________
Code for TallyGenerator -
I discovered that the table hint "fastfirstrow" is being deprecated in future versions of SQL Server, but did not find any documentation comparing the feature with similiar features.
For my rather sarcastic reply to Sergiy's original post "Did you try BOL?", see:
-
Sergiy, if you're not going to say anything useful, why bother posting at all?
I didn't check BoL, but I tested it out on a large table that I have:
SELECT
max(DateChanged) FROM LargeTable
SELECT
TOP 1 Datechanged FROM LargeTable(fastfirstrow) ORDER BY Datechanged desc
Large table has around 50 million rows and is clustered on the datechanged column.
The select max took 4 ms, according to statistics time, did 5 logical reads and took 51 % of the query cost according to the execution plan. It did a clustered index scan, a top and a stream aggregate.
The select top 1 tok 2ms, did 5 logical reads and took 49% of the query cost. It did a clustered index scan and a top.
I was curious, so did a select top 1 without the hint. It took 1 ms, did 5 reads and had the same execution plan as the top 1 with the hint.
Hope that helps
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Are you sure your investigation is really useful?
Gaining 2ms, o-oh, sorry, 3 ms on 50 mil rows table - do you beleive it really worth the efforts?
What kind of question - that kind of answer. There is no useful answer for the question of the toipic. So, why not just have fun?
Glad, the author returned my sarcasm. So, there is still hope.
_____________
Code for TallyGenerator -
No, the gain is not really worth anything.
Except the question was how does the performance of the two compare. Answer, almost the same, no significant difference
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
What do you mean "no significant difference"?
3 or 4 times faster (according to your own tests) is not significant difference for you?
Your answer is definetely wrong.
Another question - why to even try to improve performance of the query if response time is less than Windows timer tick duration even on huge tables?
Right answer is not "no significant difference" but "it does not matter".
_____________
Code for TallyGenerator -
In my experience, picky programmers are good programmers, even when their points can seem agitating.
-
> 3 or 4 times faster (according to your own tests) is not significant difference for you?
As you said, is 3 ms worth the effort. If it was 3 min vs 1 min then it's significant difference. A difference of 3 ms, which may have been caused by machine load, is not significant.
> Your answer is definetely wrong.
Really, do tell.... </sarcasm>
Significant: Having or likely to have a major effect, Fairly large in amount or quantity
> Another question - why to even try to improve performance of the query if response time is less than Windows timer tick duration even on huge tables?
I wouldn't. It's a waste of time better spent on other queries, especially since on a busy machine query times can vary by several ms just due to load.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Thanks for sharing the results of your test, Gail...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Ack! Not enough coffee! Took me a bit to figure out what the heck Serqiy and Eric were yakking about... took a look at Eric's previous post... I get it... thanks, guys.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply