The T-SQL Quiz
-
It's funny
-
Heh... and it's true, too!
--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) -
Which part of Sergiy's post is true?
-
Late to the game, but here's what I came up with for least common multiple:
declare @N1 int, @N2 int
select @n1 = 1513, @n2 = 52
;with
N1 (N1Multiple) as
(select @n1 * number
from dbo.numbers
where number between 1 and @n1 * @n2),
N2 (N2Multiple) as
(select @n2 * number
from dbo.numbers
where number between 1 and @n1 * @n2)
select min (n1multiple)
from n1
inner join n2
on n1multiple = n2multiple
It's quite obviously only going to work that way in 2k5. But if I change the CTEs to derived tables in the From clause, it will work in 2k.
On small numbers (1 to 100), the answer is so fast Set Statistics Time On can't measure it. For larger numbers (I tested with 1513 and 52), it takes a while (almost 30 seconds for that one). 151 and 152 also took about 30 seconds. If the least common is over 1-million, it won't work with my Numbers table, but my BigNumbers table would handle any number BigInt can deal with.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
-
Grant Fritchey (5/29/2007)
Ladies... The signal to noise ratio is starting to drop a bit. This was supposed to be a bit of candy for some intellectual stimulation. A little bit different than the usual. By & large it worked very well considering all the contributions, both of yours included. Let's not bicker and argue over who killed who.Worked great as intellectual stimulation as well as revealing the many approaches as to how to maintain or compose code of any kind T-SQL, CLE. As one who has been 'around the block' more than a few times I heartedly commend Jeff Moden's commenting ones code and planning for scalability.
Thanks Grant
-
A little overly complex, but very slick. Nicely done. A+. Please take my seat... no really... take my job, or just shoot me.
BTW, 2005 or better syntax is preferred.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
Having one of those "where's my porkchops and slingshot" days, Grant? 😀
--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) -
Weeks, dude, weeks. I just keep telling myself that bringing the sword to work won't really help the problem... I think. Maybe it will...
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
Adam Machanic (5/28/2007)
"A memory leak may occur in the common language runtime (CLR) if you pass a CLR user-defined data type as a parameter by using a stored procedure or the sp_executesql procedure."That's the "infamous" leak? I've barely seen anyone touch UDTs so far, let alone pass them as parameters. This is certainly not something I would worry about, and you might notice that my solution didn't use UDTs anyway. No leak, infamous or otherwise, will result.
Place I'm at is heavy into UDTs... We have a shipped app where every single column is a UDT. Yup 78 different user defined data types... including two for char(10) both having to do with Zipcodes, Five for integers, 8 for smallints, four for datetime, three floats, two moneys, two tinyints, and six varchar(100)'s...
I've kind of mentioned to them that this is really poor form... but nobody seems much interested in the development side of correcting the issue.
--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link[/url]
For tips on how to post your problems[/url] -
Mike C (5/29/2007)
God-awful loops and cursors. It's all I seem to see these days, and getting people to think about changing them? Even when you can demonstrate 100%, 200%, 500%, or 1000% improvement, people hate to give up those loops and cursors.
Nobody has to convince me anymore... I had a job that took an hour to process... mostly because it had a loop running updates to a DW data set.
Changed it to just straight update statements based on a single join instead.. suddenly the job takes about 3 minutes. (for about a 43 step job dealing with about 300,000-400,000 records)
Cursors = Bad... Loops = Bad... I get that... sometimes though... you just can't think of the set based solution off hand... so you throw in a good enough solution... and come back to fix it later when you have time... i.e after you've gotten your DW up by the project end date, and you're back out of project mode and can look for ways to improve things without some crazy Project Manager breathing down your neck.
--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link[/url]
For tips on how to post your problems[/url] -
I know I'm very late to the game (wasn't reading this board last year, sorry). And first, let me say I had a good time reading through everyone's approach at solving this problem, and seeing the creativity involved. It got me thinking in different ways than I would otherwise have gone. I completely appreciate this thread getting people thinking and trying out great solutions.
That being said, I want to talk about a couple things I see alot, both on the boards here and in real life - someone talked about this being a Rorschach test, and this is what I see: "context" and "results". (BTW, keep in mind that I work for a small company and have always worked at small companies, so some of what I'm writing may be different (or a no-go) at the bigger places you guys work at...)
First, results. If I were presented this test in an interview, I'd dutifully code the original approach of %3 = "Bizz", % 5 = "Buzz", and %3 and %5 = "BizzBuzz", and then ask them if this is what they were expecting. If they were expecting "Bizz Buzz BizzBuzz" for 15 (which to me is not a natural read of the requirements), I'd expect them to tell me so at that point. I expect to see this in real life, also. If somone codes it one way (I don't care which way), and doesn't check their results with the interested party, I'm upset. Doesn't matter to me how general/specific the original instructions were. If the instructions aren't clear, (to me) do your best but go back and check.
Second, context. All due respect to Jeff and everyone else who spent time figuring out the 1,000,000 row optimized solution, but that's an assumption. What if this is just a basic helper query someone's going to run to divide 100 rows into sets? Maybe it needs to be a stored procedure instead of an application for some reason - maybe my boss is going to run it and need to tweak it periodically. I'm sure as heck not going to use any of the CTE solutions - I'll make a simple WHILE loop for my 100 rows, throw the two divisors as variables at the top, and call it a day, because that's readable by most general coders/admins/techies. So for me at an interview, I'd deliver the WHILE loop approach, and then talk about my assumptions and lack of context. If the interviewer comes back and says that it does need to be scalable, I'd tell them that there's better ways to go, and explore those options. But that's the context part.
And I agree, in a perfect world, the interviewer would present a very clear requirements spec, not impose an extremely short time limit, etc. But (my) world is not like that, and I look for coders who care about a) the bigger picture of the code they're writing (why they're writing it, what it's going to be used for, etc.), b) the intent of the project (not necesssarily what's literally written in the spec sheet), and c) an efficient way to code that need.
-
This was my favorite solution from the thread Peso linked to.
declare @ int set @=0while @<100begin set @=@+1print left(@,sign(@%3*@%5)*3)+
left('Bizz',4-sign(@%3)*4)+left('Buzz',4-sign(@%5)*4)end
Short and easy to understand 🙂
Here is a link to the other thread for those who just can't get enough of this.
-
WOW! 21 pages as of this post. Stimulating. I got here late and had to stop reading after page 9. I thought i would throw in another approach that I didn't see in the first 9 pages. It could be done better, but here:
create table #SeedTable( Seed int identity (1, 1), Result varchar(10) )
go
insert into #SeedTable (Result)
select
case
when isnull(scope_identity(), 100) = 100 then '1'
else
case
when ((scope_identity() + 1) % 15) = 0 then 'BizzBuzz'
when ((scope_identity() + 1) % 3) = 0 then 'Bizz'
when ((scope_identity() + 1) % 5) = 0 then 'Buzz'
else cast(scope_identity() + 1 as varchar)
end
end
go 100
select * from #SeedTable
drop table #SeedTable
-
dfalso (5/1/2008)
I know I'm very late to the game (wasn't reading this board last year, sorry). And first, let me say I had a good time reading through everyone's approach at solving this problem, and seeing the creativity involved. It got me thinking in different ways than I would otherwise have gone. I completely appreciate this thread getting people thinking and trying out great solutions.That being said, I want to talk about a couple things I see alot, both on the boards here and in real life - someone talked about this being a Rorschach test, and this is what I see: "context" and "results". (BTW, keep in mind that I work for a small company and have always worked at small companies, so some of what I'm writing may be different (or a no-go) at the bigger places you guys work at...)
First, results. If I were presented this test in an interview, I'd dutifully code the original approach of %3 = "Bizz", % 5 = "Buzz", and %3 and %5 = "BizzBuzz", and then ask them if this is what they were expecting. If they were expecting "Bizz Buzz BizzBuzz" for 15 (which to me is not a natural read of the requirements), I'd expect them to tell me so at that point. I expect to see this in real life, also. If somone codes it one way (I don't care which way), and doesn't check their results with the interested party, I'm upset. Doesn't matter to me how general/specific the original instructions were. If the instructions aren't clear, (to me) do your best but go back and check.
Second, context. All due respect to Jeff and everyone else who spent time figuring out the 1,000,000 row optimized solution, but that's an assumption. What if this is just a basic helper query someone's going to run to divide 100 rows into sets? Maybe it needs to be a stored procedure instead of an application for some reason - maybe my boss is going to run it and need to tweak it periodically. I'm sure as heck not going to use any of the CTE solutions - I'll make a simple WHILE loop for my 100 rows, throw the two divisors as variables at the top, and call it a day, because that's readable by most general coders/admins/techies. So for me at an interview, I'd deliver the WHILE loop approach, and then talk about my assumptions and lack of context. If the interviewer comes back and says that it does need to be scalable, I'd tell them that there's better ways to go, and explore those options. But that's the context part.
And I agree, in a perfect world, the interviewer would present a very clear requirements spec, not impose an extremely short time limit, etc. But (my) world is not like that, and I look for coders who care about a) the bigger picture of the code they're writing (why they're writing it, what it's going to be used for, etc.), b) the intent of the project (not necesssarily what's literally written in the spec sheet), and c) an efficient way to code that need.
Lot's of good thoughts there... but instead of going through all that, why not just write good setbased code that will be performant and scalable all the time? I've seen too many snippets that were thrown together for supposedly a 100 rows only to be promoted to production because no one knew better. Writing for performance and scalability all the time isn't a real difficult thing to do and it usually doesn't take any longer to write, either. When it does take longer, it's usually worth it in performance and scalability. I look for "coders" that understand that the bigger picture includes performance and scalability without having to go back and fix something that isn't. I look for "coders" that have already embraced setbased technology. I look for managers that have the same appreciation.
And, I sure don't want my boss to be tweaking on an already slow loop. 😉
The difference between a "coder" and a good solid SQLServer Developer is understanding and an appreciation for performance and scalability. I don't hire "general" programmers. Write a loop on any of the questions I might have in a "Developer" interview, and I'm pretty sure you're not going to get hired unless you bust out laughing and say "That's how a 'coder' would do it". :w00t:
--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) -
Jeff Moden (5/1/2008)
Lot's of good thoughts there... but instead of going through all that, why not just write good setbased code that will be performant and scalable all the time? I've seen too many snippets that were thrown together for supposedly a 100 rows only to be promoted to production because no one knew better. Writing for performance and scalability all the time isn't a real difficult thing to do and it usually doesn't take any longer to write, either. When it does take longer, it's usually worth it in performance and scalability. I look for "coders" that understand that the bigger picture includes performance and scalability without having to go back and fix something that isn't. I look for "coders" that have already embraced setbased technology. I look for managers that have the same appreciation.
And, I sure don't want my boss to be tweaking on an already slow loop. 😉
The difference between a "coder" and a good solid SQLServer Developer is understanding and an appreciation for performance and scalability. I don't hire "general" programmers. Write a loop on any of the questions I might have in a "Developer" interview, and I'm pretty sure you're not going to get hired unless you bust out laughing and say "That's how a 'coder' would do it". :w00t:
Jeff, I agree with the point you're trying to make, assuming that this code is destined for a production use. I would absolutely look to a set-based solution, not just because it's faster, but because it's a set-based problem. However, I was just trying to draw attention to the context, because what if it isn't meant for some massive installed system? Maybe it's just a random utility solving a need that your sysadmins have to generate 100ish rows for whatever reason. Maybe that doesn't happen much in your world(s), and if not, then I apologize for the misunderstanding. For me, oftentimes besides coding for purpose I also have to consider things like readability. If this is some stored procedure for my boss (who can probably figure out a while loop, but a CTE would throw him) to generate his own list of 100 rows (but maybe wants to change it to 200 occasionally), this is the way I'll go.
Do I think writing for performance and scalability should be sacrificed? Not at all. I was just trying to illustrate that the scalability may be in a different direction than you all are assuming (I want this to "scale" to my boss' need to generate a handful of rows). So my WHILE loop "scales" much better than a CTE-type solution.
Note: I wish I could find a good business reason for the original problem, to better illustrate a scenario, but I can't, so that probably muddies the waters a bit.
Viewing 15 posts - 196 through 210 (of 309 total)
You must be logged in to reply to this topic. Login to reply