T-SQL Arithmetic and Geometric Sequence (Series)

Question

Post reply

T-SQL Arithmetic and Geometric Sequence (Series)

steve.jacobs

SSCommitted

Points: 1830
More actions
December 14, 2013 at 11:14 am

#302256

Comments posted to this topic are about the item T-SQL Arithmetic and Geometric Sequence (Series)

Viewing 15 posts - 1 through 15 (of 21 total)

You must be logged in to reply to this topic. Login to reply

sneumersky SSCertifiable Points: 7667 More actions · Answer 1

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 SSCommitted Points: 1830 More actions · Answer 2

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 SSCrazy Points: 2275 More actions · Answer 3

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 10^th 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 [/url]

Hany Helmy SSChampion Points: 13921 More actions · Answer 4

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.

Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant

Søren SSChasing Mays Points: 628 More actions · Answer 5

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 SSC Guru Points: 259105 More actions · Answer 6

Whoah whoah, it's Monday! Easy with the questions 😀

(seriously, nice question)

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Toreador SSChampion Points: 11365 More actions · Answer 7

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 SSChasing Mays Points: 628 More actions · Answer 8

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 One Orange Chip Points: 27169 More actions · Answer 9

Bit complicated.

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

steve.jacobs SSCommitted Points: 1830 More actions · Answer 10

Søren (12/16/2013)
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 🙂

Thanks for catching that. I really do not know how that got cut off???

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

Ed Wagner SSC Guru Points: 287026 More actions · Answer 11

steve.jacobs (12/16/2013)
Søren (12/16/2013)
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 🙂
Thanks for catching that. I really do not know how that got cut off???
Then Ceiling((@start + (@commonDiff * (@counter -1))) * Power(10,1))/Power(10, 1)

Yeah, the missing ) was inconvenient, but it was still a good question. Maybe I should read the question better next time. 🙂

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

steve.jacobs SSCommitted Points: 1830 More actions · Answer 12

Ed Wagner (12/16/2013)
steve.jacobs (12/16/2013)
Søren (12/16/2013)
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 🙂
Thanks for catching that. I really do not know how that got cut off???
Then Ceiling((@start + (@commonDiff * (@counter -1))) * Power(10,1))/Power(10, 1)
Yeah, the missing ) was inconvenient, but it was still a good question. Maybe I should read the question better next time. 🙂

Ed, I do not know how it got cut off, but I have reached out to Steve so I can add the ")" to the end of the statement.

Thomas Abraham SSChampion Points: 10761 More actions · Answer 13

Since

Abs((@start + (@commonDiff * (@counter -1))) -

Round((@start + (@commonDiff * (@counter -1))), 1, 1)) * Power(10, 1) = 5

would never evaluate to true, the rest was easy enough for a Monday.

Thanks for the question.

[font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
Connect to me on LinkedIn

Dana Medley SSCertifiable Points: 6764 More actions · Answer 14

Koen Verbeeck (12/16/2013)
Whoah whoah, it's Monday! Easy with the questions 😀
(seriously, nice question)

+1 I had to step away from this one and get some brain juices flowing before I walked through it. When I first looked at it I was like :doze: "Math?...Ooh..caffeine time".

Really great question!

Everything is awesome!