SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


T-SQL Arithmetic and Geometric Sequence (Series)


T-SQL Arithmetic and Geometric Sequence (Series)

Author
Message
steve.jacobs
steve.jacobs
SSC Eights!
SSC Eights! (894 reputation)SSC Eights! (894 reputation)SSC Eights! (894 reputation)SSC Eights! (894 reputation)SSC Eights! (894 reputation)SSC Eights! (894 reputation)SSC Eights! (894 reputation)SSC Eights! (894 reputation)

Group: General Forum Members
Points: 894 Visits: 279
Comments posted to this topic are about the item T-SQL Arithmetic and Geometric Sequence (Series)
sneumersky
sneumersky
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3120 Visits: 487
Somewhere your algebra 2 teacher is smiling......

I wonder what the performance difference would be if these operations were done in the CLR vs T-SQL:

a. In 2008
b. In 2008 R2
c. In 2012
d. In 2014

Would the difference be way less if the T-SQL operation was performed by natively compiled stored procedures accessing an in-memory table?
steve.jacobs
steve.jacobs
SSC Eights!
SSC Eights! (894 reputation)SSC Eights! (894 reputation)SSC Eights! (894 reputation)SSC Eights! (894 reputation)SSC Eights! (894 reputation)SSC Eights! (894 reputation)SSC Eights! (894 reputation)SSC Eights! (894 reputation)

Group: General Forum Members
Points: 894 Visits: 279
sneumersky (12/14/2013)
Somewhere your algebra 2 teacher is smiling......

I wonder what the performance difference would be if these operations were done in the CLR vs T-SQL:

a. In 2008
b. In 2008 R2
c. In 2012
d. In 2014

Would the difference be way less if the T-SQL operation was performed by natively compiled stored procedures accessing an in-memory table?


Algebra 2? Actually we covered these formulas in 2nd grade :-P. As for the performance difference, it is faster (at least 50% (taking in to account memory, processors, etc.) in the CLR because of the compiled code. But here is a really good article and test scenario comparing T-SQL UDF vs. SQLCLR UDF. While the article focuses on splitting strings, it applies for any UDF process. By the way, we use Idera for all of our database (scripts included) performance measurements.

http://blog.idera.com/sql-server/performance-and-monitoring/comparing-simple-efficiencies-t-sql-udf-vs-sqclr-udf-for-splitting-strings/


Just as an FYI, this was asked of me years ago for some students in the 3rd grade. Basically, all the formulas were in UDF's (so the code was "clean") and had a ASP.NET front-end. Other UDF formulas I added were Arithmetic Series Summing, Geometric Series Summing and Mathematical Series.

Anyway, I wanted to share an easy and fun QoTD.
thava
thava
SSC-Addicted
SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)

Group: General Forum Members
Points: 471 Visits: 557
To be honest, it's too much of calculation,
To me finding a geometric sequence with bare hands is not that much easier, when it reach over 10th term it's going to a night mare,
so i just find the answer for arithmetic and tick it straight away, if there are more than two same answers for the arithmetic mean i would surely get stuck, seems bit lucky today? i got the answer Right



Every rule in a world of bits and bytes, can be bend or eventually be broken
MyBlog About Common dialog control
A Visualizer for viewing SqlCommand object script
Hany Helmy
Hany Helmy
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4419 Visits: 1117
As per the question, it was mentioned that "Developed and tested using SQL Server 2008 R2 SP2"; so why don`t we use the new features available from SQL Server 2008 onwards like follows:

declare @start float = 1,
@commonDiff float = 3,
@counter float = 0

print 'Begin';
while @counter < 200
begin
set @counter += 1
declare @currVal float = 0


Setting the Variable`s values in the same declaration is more efficient, easier, faster and it looks even better.
BTY; I tested it, got the same results.
Søren
Søren
SSC-Enthusiastic
SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)

Group: General Forum Members
Points: 128 Visits: 189
a ) is missing in the line (at the end).

Then Ceiling((@start + (@commonDiff * (@counter -1))) * Power(10,1))/Power(10, 1


Otherwise a very nice example :-)

Søren,
MCITP SQL 2008 (administration / programming), MCITP SQL 2005 (BI), MCT
Koen Verbeeck
Koen Verbeeck
SSC Guru
SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)

Group: General Forum Members
Points: 59457 Visits: 13297
Whoah whoah, it's Monday! Easy with the questions :-D

(seriously, nice question)


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
Toreador
Toreador
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3776 Visits: 8123
Søren (12/16/2013)
a ) is missing in the line (at the end).


I'm most disappointed that there aren't already several pages of identical posts moaning about that and demanding that some points be given back ;-)

I confess to getting it right by the same method as thava used - check the easy bit and ignore the hard bit!
Søren
Søren
SSC-Enthusiastic
SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)

Group: General Forum Members
Points: 128 Visits: 189
It is not about getting points back or focusing on wrong things.

It is a good, funny and very interesting example and why not make it better by fixing the code?

Søren,
MCITP SQL 2008 (administration / programming), MCITP SQL 2005 (BI), MCT
free_mascot
free_mascot
SSCertifiable
SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)

Group: General Forum Members
Points: 7207 Visits: 2250
Bit complicated.

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search