FizzBuzz

Question

FizzBuzz

Viewing 15 posts - 106 through 120 (of 363 total)

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

SQLRNNR SSC Guru Points: 281324 More actions · Answer 1

reidres (2/22/2010)
Well I have to dispute the scalability of Jeff's implied solution and also think it is clunky and not intuitive.

Why do you think it is clunky?

I cringe at the idea of relying on the # of recs in Master.dbo.SysColumns.

That is why caution was given at using that table. Keep in mind, one reason to use that table may be to create a Numbers table - which is more reliable on the quantity of numbers in it.

Sometimes to meet a real world constraint, you will do something that is ugly because there are no better alternatives.

That may be the case, but it is more costly to create an ugly solution and then fix it later than to do it properly the first time. It may take more time, but quality is more valuable for many DBA's.

The rest is just for show, if one has too much time on their hands.

I don't agree with that. Gaining the best performance out of code prior to releasing it to production is not for show. That is to keep the system running and prevent phone-calls in the middle of the night. When shortcuts are taken and the database group circumvented, I have found that time to perform DBA tasks is greatly diminished due to constant rework or firefighting. That is no fun at all.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

steve-893342 SSCarpal Tunnel Points: 4439 More actions · Answer 2

Has anyone come up with this one yet?

with

cte1 as

(

select top 100 row_number() over (order by (select 0)) as row from master.sys.all_columns

)

select case when row%15 = 0 then 'FIZZBUZZ' when row%5 = 0 then 'BUZZ' when row%3 = 0 then 'FIZZ' ELSE convert(varchar(255), ROW) END

from cte1

reidres SSC Veteran Points: 290 More actions · Answer 3

Jeff,

I don't want people on my team optimizing for trivial things, and the constraints of the project define those things. Speed may not be essential, or it may. Perhaps you are in an environment where speed is always necessary. But there are other environments. But if you want people who are already optimized for speed at all times, that is fine.

Jason,

Clunky: FROM Master.dbo.SysColumns sc1, Master.dbo.SysColumns sc2

Now granted a FROM Master.dbo.SysColumns sc1 CROSS JOIN Master.dbo.SysColumns sc2 makes it much more scalable, but the idea of having sc1, sc2, ... I just don't like it.

SQLRNNR SSC Guru Points: 281324 More actions · Answer 4

Jeff Moden (2/22/2010)
BTW, the version that I've not shown you will take an INT to the max and it does it without referencing any tables at all.

I have to say I like it. I have not seen Jeff's script - but I like the idea and method. I created one that does the same thing (probably not as efficient as Jeff's) and it is crazy fast.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

WayneS SSC Guru Points: 95405 More actions · Answer 5

reidres (2/22/2010)
Jeff,
I don't want people on my team optimizing for trivial things, and the constraints of the project define those things. Speed may not be essential, or it may. Perhaps you are in an environment where speed is always necessary. But there are other environments. But if you want people who are already optimized for speed at all times, that is fine.

Just my 0.02 here... the more you work with optimizing, the more it becomes ingrained in you, to where you do it sub-consciously for everything. If it comes to where you naturally pick an optimized, scalable method to accomplish the task, and the coding time is similiar, how can that be bad?

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

John Burris Right there with Babe Points: 748 More actions · Answer 6

Steve,

I posted earlier that for 1,000,000 records I ran my first solution at 1:30 and Jeff's solution at 10 seconds. I ran the GSquared "ultimate version" on a test box and it is at 6 hours and 30 minutes and it is still going. I am going home for the day so I will never know how long it takes.

This just goes to show how bad code can really slow you down.

[font="Tahoma"]John Burris:hehe:
MCITP Database Administrator[/font]

SQLRNNR SSC Guru Points: 281324 More actions · Answer 7

John Burris (2/22/2010)
Steve,
I posted earlier that for 1,000,000 records I ran my first solution at 1:30 and Jeff's solution at 10 seconds. I ran the GSquared "ultimate version" on a test box and it is at 6 hours and 30 minutes and it is still going. I am going home for the day so I will never know how long it takes.
This just goes to show how bad code can really slow you down.

Thank you for doing that. That really makes the point for us right there.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 8

reidres (2/22/2010)
Jeff,
I don't want people on my team optimizing for trivial things, and the constraints of the project define those things. Speed may not be essential, or it may. Perhaps you are in an environment where speed is always necessary. But there are other environments. But if you want people who are already optimized for speed at all times, that is fine.
Jason,
Clunky: FROM Master.dbo.SysColumns sc1, Master.dbo.SysColumns sc2
Now granted a FROM Master.dbo.SysColumns sc1 CROSS JOIN Master.dbo.SysColumns sc2 makes it much more scalable, but the idea of having sc1, sc2, ... I just don't like it.

Understood... and that's certainly your choice. But everytime I get called into a shop to fix performance problems it's because they made a similar choice. Rework of the performance tuning nature takes extra regression testing to make sure nothing breaks and it normally takes a lot more time to figure out what's causing the problem because of all the previously trivial spots that have grown up over time.

There are Developers out there that know how to write non-RBAR code and understand the reason for doing so all the time... and it doesn't take them any longer (sometimes it takes less because there's usually less code to actually type and test) than doing it with RBAR. I'm not everyone but that's what I look for during an interview and, so far, that's what everyone else I've interviewed with looks for... good, accurate code that blows the doors off the competition and won't bog down over time... even on the trivial stuff. 🙂

--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)
Intro to Tally Tables and Functions

reidres SSC Veteran Points: 290 More actions · Answer 9

Jeff,

Thanks for the heads up on the fact that it can be done without tables. I now see the solution. To just generate and count the numbers from 1 to 15 000 000 without Fizz Bizzing them or printing them takes 12 seconds on my machine.

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 10

reidres (2/22/2010)
Jeff,
Thanks for the heads up on the fact that it can be done without tables. I now see the solution. To just generate and count the numbers from 1 to 15 000 000 without Fizz Bizzing them or printing them takes 12 seconds on my machine.

'zactly. 😉 Thanks for the feedback.

--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)
Intro to Tally Tables and Functions

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 11

WayneS (2/22/2010)
reidres (2/22/2010)
Jeff,
I don't want people on my team optimizing for trivial things, and the constraints of the project define those things. Speed may not be essential, or it may. Perhaps you are in an environment where speed is always necessary. But there are other environments. But if you want people who are already optimized for speed at all times, that is fine.
Just my 0.02 here... the more you work with optimizing, the more it becomes ingrained in you, to where you do it sub-consciously for everything. If it comes to where you naturally pick an optimized, scalable method to accomplish the task, and the coding time is similiar, how can that be bad?

Heh... I wasn't going to go there but now that you've brought it up...

If you never practice doing it wrong, chances are, you'll never do it wrong. 😛 It will become like riding the proverbial bike and in pretty short order.

--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)
Intro to Tally Tables and Functions

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 12

John Burris (2/22/2010)
Steve,
I posted earlier that for 1,000,000 records I ran my first solution at 1:30 and Jeff's solution at 10 seconds. I ran the GSquared "ultimate version" on a test box and it is at 6 hours and 30 minutes and it is still going. I am going home for the day so I will never know how long it takes.
This just goes to show how bad code can really slow you down.

Way to go, John! Heh... and absolutely correct...

--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)
Intro to Tally Tables and Functions

Steve Jones - SSC Editor SSC Guru Points: 728164 More actions · Answer 13

Thanks, John. Hopefully it will be done tomorrow 🙂

Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com

WayneS SSC Guru Points: 95405 More actions · Answer 14

Steve Jones - Editor (2/22/2010)
Thanks, John. Hopefully it will be done tomorrow 🙂

Nope... Gus seems to be the type that would have the code finish 'bout the time the weekend is upon us. I predict sometime on Friday...:-P

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Manie Verster SSCertifiable Points: 7022 More actions · Answer 15

reidres (2/22/2010)
Manie Verster,
I notice that you used a temporary table rather than a table variable. Why did you make that choice?
Thank you.

I did this as if I was in the interview and first tried a CTE but got an error which I could not solve because I worked against time. The temporary table was my next option and because I knew it so well I chose that. I also checked the duration of both queries (temp table and Grant's) and both gave me 3 ms. You can also use a table variable but the amount of coding would have been much more and in an interview you want to impress with speed.

:-PManie Verster
Developer
Johannesburg
South Africa

I can do all things through Christ who strengthens me. - Holy Bible
I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)