February 23, 2010 at 10:25 am
$25 from Amazon will cover this: http://www.amazon.com/Omaha-Steaks-Boneless-Pork-Chops/dp/B0017129DC/ref=sr_1_2?ie=UTF8&s=gourmet-food&qid=1266945895&sr=8-2
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
February 23, 2010 at 10:25 am
Jeff Moden (2/23/2010)
Tom.Thomson (2/22/2010)
By the way, there was method in choosing a hundred million as a limit - it's big enough to need a 3-way cross join of master.sys.all_columns to generate the CTE, and small enough that limiting the size of that cross join (by not cross-joining the whole table to itself, but joining part of the table to itself) is useful.You would have no way of knowing this unless you ran a Billion row test like I did... more than a single cross join will cause extensive log file usage. Generating a Billion rows using a triple cross join caused the log file on my machine (I forget which DB it did it in) to grow to over 40GB!
Well, yes, if you create a billion rows you certainly have a problem.
But having a triple cross join doesn't have to mean that - it can mean having just a few more rows than the number of rows you actually need. The code I posted includes a trivial fix that ensures it doesn't generate too many rows with that triple cross join (by curtailing the components of the join, not just the result).
Even on my somewhat aged laptop that code generates the result for FizzBuzz to 100 in a couple of miliseconds (so that's not generating even a million rows, let alone a billion) and handles FizzBuzz to 1,000,000 quite quickly, so despite its triple cross join it is certainly not generating 125,000,000,000 rows (I have about 5000 rows in master.sys.all_columns - come to think of it, how many rows is a table allowed to hold?). I ran it on 1 to 10,000,000 and that was slower (significant disc activity - I could do with more RAM), and started it on 1 to 100,000,000 but stopped it when it was still running and had eaten up a lot of disc after I took a break to do get a snack. That fix could usefully be added even when there's just a single cross join, with of course the 3 changed to 2, as it will speed things up no end if significantly fewer numbers are needed than the square of the number of rows in master.sys.all_columns (that's the view I used).
If you ever need to generate more than what you have in your Tally table and you're using 2k5 or better, I'd recommend using one of the various mods of Itzik Ben Gan's nested CTE floating around on this site. By itself, it causes zero reads and zero writes and uses comparitively little CPU time.
Thanks for the pointer - I will find that and study it right away.
Anyway, thank you very much for the feedback on this. I apologize for not getting back to you sooner. I had my arms full of pork chops. 😛
No worry - you are juggling a lot of subthreads, and I appreciate having a response as quickly as this.:satisfied:
Tom
February 23, 2010 at 11:33 am
Jeff Moden (2/23/2010)
Gary Varga (2/23/2010)
Jeff Moden (2/23/2010)
You're right, Gary... it might make a good article called "Winning the Interview with Code" or somesuch.OK. Perhaps two articles. The one you suggest should highlight that the code you show says a lot AND you must know the code you write - not a technical article. The other one might be entitled "Set Theory vs Functional Programming: Why Set Theory should always be your default choice." - title obviously applicable to SQL Server (or even RDBMS' in general) development.
Thanks for your efforts Jeff.
I can't write that second article because it wouldn't be true. There are places where a very well written WHILE loop or function will smoke the set based methods... even the Tally table. They're just fairly rare. Although RBAR has come to mean "no loops" to a lot of folks, RBR (without the "A") can be and is very effective in some instances. Of course, that might make for a good article too. I'm just not sure I want to admit in public that some good folks have spanked some instances of my code that hard. 😛
Hey, come on you guys, don't say "functional programming" when you clearly mean "imperative programming" or "procedural programming". I'm not aware of a single functional programming language that has a loop construct in it (must get around to looking at F#, in case it scores a first there).
Tom
February 23, 2010 at 12:57 pm
Steve Jones - Editor (2/23/2010)
Since this became a performance discussion, I added a question on ASK about the best method. I know someone might memorize this for an interview, but you could easily change the question, or even make them explain how it works. That would let you see if they understand how to code or just how to memorize.I'll even offer a prize. $25 to the best solution on Mar 31. (highest vote total)
Heh... not sure why everyone keeps saying that. Although performance certainly creeps into the picture here, I'm really trying to help people survive an interview with people who wear set-based hats. 😛
--Jeff Moden
February 23, 2010 at 1:31 pm
Review on the performance based answers contest:
Lynn Pettis' solution is very scalable, though not as fast as CirqueDeSQLeil's solution which appears for some reason to be not as scalable.
A modified version of Pettis' solution so that records are only counted instead of FizzBuzzed, shows that for 150 000 000 the time is approx. 2:40 on my machine (2 minutes...).
A similarly modified version of CirqueDeSQLeil's solution shows that for 100 000 000 the time is 0:27 (27 seconds) But I put in 150 000 000, so I don't know why it is just doing 100M, though I haven't thought about it.
Pettis' modified code:
declare @pEndValue bigint,
@pStartValue bigint,
@pIncrement bigint;
set @pStartValue = 1;
set @pEndValue = 150000000;
set @pIncrement = 1;
with BaseNum (
N
) as (
select 1 union all
select 1 union all
select 1 union all
select 1 union all
select 1 union all
select 1 union all
select 1 union all
select 1 union all
select 1 union all
select 1
),
L1 (
N
) as (
select
bn1.N
from
BaseNum bn1
cross join BaseNum bn2
cross join BaseNum bn3
),
L2 (
N
) as (
select
a1.N
from
L1 a1
cross join L1 a2
),
Tally (
N
) as (
select top (isnull(abs(@pEndValue - @pStartValue) / abs(nullif(@pIncrement,0))+ 1,1)) --Limit N per parameters
row_number() over (order by a1.N)
from
L2 a1
cross join L2 a2
)
select
N as Value
into
#TempTable
from
Tally;
go
select COUNT(*) from #TempTable ;
go
drop table #TempTable
CirqueDeSQLeil's modified code:
Declare @Limit BigInt
Set @Limit = 150000000
;
WITH Nbrs_2( n ) AS (select 1 union all
select 1 union all
select 1 union all
select 1 union all
select 1 union all
select 1 union all
select 1 union all
select 1 union all
select 1 union all
select 0),
Nbrs_1( n ) AS ( SELECT 1 FROM Nbrs_2 n1 CROSS JOIN Nbrs_2 n2 ),
Nbrs_0( n ) AS ( SELECT 1 FROM Nbrs_1 n1 CROSS JOIN Nbrs_1 n2 ),
Nbrs ( n ) AS ( SELECT 1 FROM Nbrs_0 n1 CROSS JOIN Nbrs_0 n2 )
SELECT COUNT(*)
FROM ( SELECT ROW_NUMBER() OVER (ORDER BY n)
FROM Nbrs ) D (n)
WHERE n <= abs(@Limit) ;
;
Are my modifications fair?
February 23, 2010 at 1:40 pm
Lynn has an additional cross join thus supports up into the billions.
I adjusted my solution to support this
Declare @LimitBigInt
Set @Limit = 150000000
;
WITH Nbrs_2( n ) AS (select 1 union all
select 1 union all
select 1 union all
select 1 union all
select 1 union all
select 1 union all
select 1 union all
select 1 union all
select 1 union all
select 0),
Nbrs_1( n ) AS ( SELECT 1 FROM Nbrs_2 n1 CROSS JOIN Nbrs_2 n2 CROSS JOIN Nbrs_2 n3),
Nbrs_0( n ) AS ( SELECT 1 FROM Nbrs_1 n1 CROSS JOIN Nbrs_1 n2 ),
Nbrs ( n ) AS ( SELECT 1 FROM Nbrs_0 n1 CROSS JOIN Nbrs_0 n2 )
SELECT case
when n % 15 = 0 then 'Dr. Pepper'--Multiples of 3 AND 5
when n % 3 = 0 then 'Pepsi'
when n % 5 = 0 then 'Coke'
else cast(n as VarChar(8))
end as 'FizzBuzz'
FROM ( SELECT ROW_NUMBER() OVER (ORDER BY n)
FROM Nbrs ) D (n)
WHERE n <= abs(@Limit) ;
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
February 23, 2010 at 1:44 pm
Review of Kev Riley's v2 solution:
for 150 000 000 the time is 1:38
The modified Riley code used for this test:
declare @maxnum int
set @maxnum = 150000000
set statistics io on
set statistics time on
;WITH Nbrs_4( n ) AS ( SELECT 1 UNION SELECT 0 ),
Nbrs_3( n ) AS ( SELECT 1 FROM Nbrs_4 n1 CROSS JOIN Nbrs_4 n2 ),
Nbrs_2( n ) AS ( SELECT 1 FROM Nbrs_3 n1 CROSS JOIN Nbrs_3 n2 ),
Nbrs_1( n ) AS ( SELECT 1 FROM Nbrs_2 n1 CROSS JOIN Nbrs_2 n2 ),
Nbrs_0( n ) AS ( SELECT 1 FROM Nbrs_1 n1 CROSS JOIN Nbrs_1 n2 ),
Nbrs ( n ) AS ( SELECT 1 FROM Nbrs_0 n1 CROSS JOIN Nbrs_0 n2 )
SELECT
count (n)
FROM ( SELECT ROW_NUMBER() OVER (ORDER BY n)
FROM Nbrs ) D ( n )
WHERE n <= @maxnum;
February 23, 2010 at 6:15 pm
How about something really simple like:
select
case
when (num % 5 = 0 and num % 3 = 0) then 'FizzBuzz'
when num % 5 = 0 then 'Buzz'
when num % 3 = 0 then 'Fizz'
else convert(varchar,num)
end as num
from
(
select top 100 row_number() over (order by type) num from master..spt_values
) t
February 23, 2010 at 7:14 pm
That certainly works and it's a whole lot better than a WHILE loop. It certainly shows that you have more than just a casual knowledge (as previous similar examples have) of SQL Server/T-SQL and doing something like that would certainly put you head and shoulders above the WHILE loop crowd in my eyes.
I'd still recommend making it a bit more scalable, though . Not because the problem needs it with it's paltry 100 row requirement, but because it will show the interviewer (especially if you add a comment to that nature) that scalability is also something that you practice without having to actually think about it.
I haven't run into an interviewer yet that said, "Your code is too scalable".
--Jeff Moden
February 23, 2010 at 7:18 pm
The query certainly works for 100. For large result sets you would want to consider a different method.
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
February 23, 2010 at 10:46 pm
Jeff Moden (2/23/2010)
Manie Verster (2/23/2010)
Jeff I am on your case today.No problem, Manny... I'm on your's, as well but I did install some handrails so you can enjoy the ride. 😀
No where did I say to use a Tally table as a source for this. I pointed at the Tally Table article to give people a clue on how to build a table on the fly using a couple of methods. You actually used that method but came up short... no scalability.
Also, you produced 4 columns... not part of the requirement. You also used a Global Temp table which means that the code can only be used one instance at a time because if the code is executed more than once at the same time, there's a chance that the drop will cause a failure in the other run. If you don't use the drop, then there's the chance that the SELECT/INTO to create the table will fail because the table already exists. And, despite the requirement, the code is hardcoded to 100.
Again, if you "just" meet the requirements, you'll be the same as all the other interviewers. Make yourself standout! Heh... start by meeting the original requirements instead of creating 4 columns. 😛
Oh... by the way... the reason why your code appears to run so fast on my machine is because it uses the copy of syscolumns in the local database. There's only 431 rows in that table in my test DB. Do you have 11000 rows in your machine for the PostalDB or does it come up a wee bit short? Also, if you look at the code, you included making a permanent Tally table AND building it's clustered index in the run time. In real life, the Tally table would already exist. When I changed your code to actually create 11000 rows and remove the time to make the permanent table, your code lost by a pretty good percentage.
Hi Jeff, yes the handrails are making for a jolly good ride. Listen, this morning when I opened my mail I said to myself: "Now let's see if someone's going to floor me today because I had the feeling. The tally table code was yours which I had copied from an article of yours. I just added the fizzbuzz part and yes, you are right about the syscolumns but initially I did run it on 100 records. I do not normally hardcode a query like this but because they said 100 records and I wrote this as if I was in an interview then a person do tend to not do very fancy code.
One point in all this that you are missing is that when a person is in an interview, no matter how tough you are, you are nervous and please anyone tell me that you were ever not nervous when you went in for an interview. Jeff, even you. No-one is that perfect.
:-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)
February 23, 2010 at 10:55 pm
Manie Verster (2/23/2010)
One point in all this that you are missing is that when a person is in an interview, no matter how tough you are, you are nervous and please anyone tell me that you were ever not nervous when you went in for an interview. Jeff, even you. No-one is that perfect.
I think an important thing to remember is that even though one may be nervous, coding habits will reveal themselves. If you expect better things from yourself than an interviewer - then you will be successful. And another very valid point is: If you are able to produce code that works very well, and Joe does it a little better - then he stands in a better position to get the job.
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
February 23, 2010 at 11:05 pm
CirquedeSQLeil (2/23/2010)
Manie Verster (2/23/2010)
One point in all this that you are missing is that when a person is in an interview, no matter how tough you are, you are nervous and please anyone tell me that you were ever not nervous when you went in for an interview. Jeff, even you. No-one is that perfect.
I think an important thing to remember is that even though one may be nervous, coding habits will reveal themselves. If you expect better things from yourself than an interviewer - then you will be successful. And another very valid point is: If you are able to produce code that works very well, and Joe does it a little better - then he stands in a better position to get the job.
Good point. Does that mean that I have bad habits? Jeff might think so but I don't.
:-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)
February 23, 2010 at 11:11 pm
Manie Verster (2/23/2010)
CirquedeSQLeil (2/23/2010)
Manie Verster (2/23/2010)
One point in all this that you are missing is that when a person is in an interview, no matter how tough you are, you are nervous and please anyone tell me that you were ever not nervous when you went in for an interview. Jeff, even you. No-one is that perfect.
I think an important thing to remember is that even though one may be nervous, coding habits will reveal themselves. If you expect better things from yourself than an interviewer - then you will be successful. And another very valid point is: If you are able to produce code that works very well, and Joe does it a little better - then he stands in a better position to get the job.
Good point. Does that mean that I have bad habits? Jeff might think so but I don't.
Nope. I don't think Jeff does either. He's trying to educate and prepare people. The trick is to learn new better, faster, more manageable methods to accomplish the task at hand, implement the techniques so they become ingrained. Sometimes it is the little details that could win a person the job. Most people will not account for scalability in a task such as this. There will be a few who do, and they will be head and shoulders above the rest (in most cases) when it comes hiring time.
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
February 23, 2010 at 11:16 pm
Jeff, I forgot some points in my previous post to you. To hardcode a query is never good and there you have me. It looks like I am getting floored here today:hehe::hehe::hehe:! The other point (also a good one) is that I did not read the question properly and therefore did it wrong. I should have replaced the numbers and not add columns to the query. The global temp table point I cannot agree with. This was a trademark of my mentor to use the global temp table and in all this 10 years I have been working for him, not one query failed with a global temp table. I will however research these temp tables and maybe I can write an article about it.:hehe::hehe::hehe::hehe:
Jeff, as a last point, although I like to take you on. I like you and in the time since I joined SSC I learned a lot from you. I would like you to add a query in Steve's Ask competition because I want to kick your a...........backside.
:-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)
Viewing 15 posts - 151 through 165 (of 363 total)
You must be logged in to reply to this topic. Login to reply