May 21, 2012 at 9:20 pm
Well, I have run several tests of both over a heap table with 1,000,000 rows of data and both run around 2.5 seconds with no clear advantage to either one. As long as your average run time is less than 24 hours, both work just as well.
May 21, 2012 at 9:24 pm
Lynn Pettis (5/21/2012)
I have run several tests of both over a heap table with 1,000,000 rows of data
You're starting to sound like Jeff!
That's a compliment. More people should take the time to thoroughly test proposed solutions like this.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
May 21, 2012 at 9:33 pm
dwain.c (5/21/2012)
Lynn Pettis (5/21/2012)
I have run several tests of both over a heap table with 1,000,000 rows of dataYou're starting to sound like Jeff!
That's a compliment. More people should take the time to thoroughly test proposed solutions like this.
When I run tests, where do you think I got it? Jeff has pounded it to me, and I find myself telling others when they claim their code is good enough, "does it pass the million row test?"
May 21, 2012 at 9:37 pm
I will need to take note of that 🙂
Thanks again for the help!
May 21, 2012 at 9:40 pm
Lynn Pettis (5/21/2012)
dwain.c (5/21/2012)
Lynn Pettis (5/21/2012)
I have run several tests of both over a heap table with 1,000,000 rows of dataYou're starting to sound like Jeff!
That's a compliment. More people should take the time to thoroughly test proposed solutions like this.
When I run tests, where do you think I got it? Jeff has pounded it to me, and I find myself telling others when they claim their code is good enough, "does it pass the million row test?"
Yeah, he's been pounding me lately (well deservedly I might add) for my recursive CTE solutions.
His forum handle should be "Performance Policeman"
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
May 22, 2012 at 12:12 am
The really cool part is that both of you rose to the occasion. 🙂
Shifting gears a bit... Lynn wrote...
I have learned to stay away from scalar functions.
Be careful, ol' friend. I believe Dwaine will back me on this because he was a part of the thread but I took on all comers with a Scalar UDF that had a While Loop in it... and won. 😉 Not by just a little, either.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 22, 2012 at 12:29 am
Jeff Moden (5/22/2012)
The really cool part is that both of you rose to the occasion. 🙂Shifting gears a bit... Lynn wrote...
I have learned to stay away from scalar functions.
Be careful, ol' friend. I believe Dwaine will back me on this because he was a part of the thread but I took on all comers with a Scalar UDF that had a While Loop in it... and won. 😉 Not by just a little, either.
And then, iirc, I may have said something to the effect that you need to test. Yes, I remember that thread being mentioned. For the most part, scalar functions can be a form of hidden RBAR.
May 22, 2012 at 4:52 am
Jeff Moden (5/22/2012)
The really cool part is that both of you rose to the occasion. 🙂Shifting gears a bit... Lynn wrote...
I have learned to stay away from scalar functions.
Be careful, ol' friend. I believe Dwaine will back me on this because he was a part of the thread but I took on all comers with a Scalar UDF that had a While Loop in it... and won. 😉 Not by just a little, either.
I'll back you 100% on the condition that you start spelling my name right (no e)! 🙂
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
May 22, 2012 at 7:43 am
dwain.c (5/22/2012)
Jeff Moden (5/22/2012)
The really cool part is that both of you rose to the occasion. 🙂Shifting gears a bit... Lynn wrote...
I have learned to stay away from scalar functions.
Be careful, ol' friend. I believe Dwaine will back me on this because he was a part of the thread but I took on all comers with a Scalar UDF that had a While Loop in it... and won. 😉 Not by just a little, either.
I'll back you 100% on the condition that you start spelling my name right (no e)! 🙂
If I remember right, wasn't that a Gaps and Islands thread? My google-fu is definately worse than my SQL-fu, as I can't seem to find it.
May 22, 2012 at 6:36 pm
Lynn - I believe it was a parsing opportunity and that this is the thread to which Jeff refers: http://www.sqlservercentral.com/Forums/Topic1300365-392-2.aspx
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply