January 1, 2014 at 9:03 pm
j-1064772 (1/19/2012)
How do the newfangled Analytics functions compare with the Quirky Update method used for - among other things - creating running totals in term of performance ?
I haven't tested the full blown running totals yet, but here's a comparison against QU for a 12 month running total.
Calculating Values within a Rolling Window in Transact SQL [/url]
I do believe Wayne did a test (using the window frame facility) of the running totals problem on Denali in his blog. http://blog.waynesheffield.com/wayne/archive/2011/08/running-totals-in-denali-ctp3/
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
January 2, 2014 at 3:09 am
Hi Dwain,
Thanks for your kind remark. And thanks again for responding to these questions.
dwain.c (1/1/2014)
j-1064772 (1/19/2012)
How do the newfangled Analytics functions compare with the Quirky Update method used for - among other things - creating running totals in term of performance ?I haven't tested the full blown running totals yet, but here's a comparison against QU for a 12 month running total.
Calculating Values within a Rolling Window in Transact SQL [/url]
I do believe Wayne did a test (using the window frame facility) of the running totals problem on Denali in his blog. http://blog.waynesheffield.com/wayne/archive/2011/08/running-totals-in-denali-ctp3/
Please note that the analytic functions aren't what you would use to perform running totals. The article on my blog uses the SUM() OVER() aggregate function.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
January 2, 2014 at 5:06 pm
WayneS (1/2/2014)
Hi Dwain,Thanks for your kind remark. And thanks again for responding to these questions.
You're welcome. I was hoping you wouldn't mind too much if I had your back when you might be on holidays somewhere. 🙂
I now have a few thought questions that I have not yet had time to investigate myself:
1. I wonder why it is that LAG performed better than LEAD on the gaps solution. Granted that it could be that I used a sub-optimal version of LEAD, but it didn't look like there could be too much done to it to improve it (but I may be wrong).
2. I haven't yet tried LAG vs. LEAD on a wide array of problems but the few that I have tried it on could be solved by either. I wonder if it is always the case that LAG and LEAD are equally applicable to any problem that can be solved by either.
3. If #2 is true, would LAG always perform better, or under what circumstances would LEAD outperform LAG?
Inquiring minds want to know.
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 5, 2015 at 7:29 am
Great training piece, thanks.
Viewing 4 posts - 31 through 33 (of 33 total)
You must be logged in to reply to this topic. Login to reply