February 23, 2010 at 12:14 am
reidres (2/22/2010)
It took me 14 minutes and I developed a solution which turned out to closely approximated one or more of what was on Fritchey's page.What is wrong with using a WHILE and what would be a preferable approach? Also, did you learn of this in a book or where?
Thank you.
Yes, it can be learn from SQL Server Books On Linebut I was fortunate to have a genius for a mentor and also a lot was learned through SSC and experience.
:-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 12:23 am
Jeff Moden (2/22/2010)
Manie Verster (2/22/2010)
I checked out Grant Fritchey's[/url] article and was, like Jeff, not very pleased to see some while loops but one query attracted my attention and I tested it and I like it.
;WITH Nbrs(n) AS
(
SELECT 1
UNION ALL SELECT 1 + n FROM Nbrs WHERE n < 100
)
SELECT CASE WHEN n%5=0 AND n%3=0 THEN 'BizzBuzz'
WHEN n%3 = 0 THEN 'Bizz'
WHEN n%5 = 0 THEN 'Buzz'
ELSE CAST(n AS VARCHAR(8))
END
FROM Nbrs
OPTION (MAXRECURSION 100);
Nice go, Manny... but because I'm mostly against the use of procedural code, I'd have to deduct a lot of points on this one. Contrary to popular belief, recursive CTEs are [font="Arial Black"]NOT[/font] set based and they are frequently slower and more resource intensive than a well written WHILE loop.
It is good to take a standpoint Jeff and stick to it but there is a thing called obsessiveness. I tested this query against the one I posted with the temp table and both gave me 3 ms and I used your way of checking a query's duration with the start and en dates and datediff in ms. Would that then be so bad?!
:-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 12:30 am
Jeff Moden (2/22/2010)
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.
There shouldn't be a Temp table or Table variable associated with this problem.
What would you use? Ah yes, a tally table! Is my temp table so far from your tally table? Oh, when you are in an interview, palms sweating, nervous to suddenly put together a tally table and write all that code. No friend, that is when you take a shortcut. You want to proof to this person that you are the guy for the job.
Sorry, I am just a developer and if I were the interviewer I would have appointed me.
:-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 12:38 am
Steve Jones - Editor (2/22/2010)
LOL, in an interview, I'd think the CTE, or a short WHILE loop would make sense, having the person talk through what they're doing. This isn't a test of great programming ability, it's a weed out of lower programming ability. Or a lack thereof.I've been surprised how many people couldn't find duplicates in a short table. They either keep pestering me with questions on other columns, indexes, keys, etc., or they write a count() code without a HAVING. You might also be surprised how many people still return @@identity. BTW, does anyone use that?
Thank you Steve, if Jeff had to interview no-one will be appointed. Nope, I have never used @@identity. Finding duplicates was one of the first things I have had to learn. I think it is fairly easy.
:-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 12:41 am
Jeff Moden (2/22/2010)
Loner (2/22/2010)
JeffWhy don't you post your 'perfect' answer?
I can tell you why... people would simply memorize instead of taking the time to figure it out. 😉
Fortunately Jeff, I never had you as a mentor because I would still not be able to write any code. Sorry, no offence meant but I believe in helping people to get somewhere. Chill out man!
:-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 12:43 am
Martin Vrieze (2/22/2010)
Guess I'm just not a good programmer.Sorry Steve I don't measure up.:crying:
Martin, do measure yourself against Jeff and the likes. Someone must start somewhere. Just do your best.
:-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 12:55 am
Manie Verster (2/23/2010)
Jeff Moden (2/22/2010)
Manie Verster (2/22/2010)
I checked out Grant Fritchey's[/url] article and was, like Jeff, not very pleased to see some while loops but one query attracted my attention and I tested it and I like it.
;WITH Nbrs(n) AS
(
SELECT 1
UNION ALL SELECT 1 + n FROM Nbrs WHERE n < 100
)
SELECT CASE WHEN n%5=0 AND n%3=0 THEN 'BizzBuzz'
WHEN n%3 = 0 THEN 'Bizz'
WHEN n%5 = 0 THEN 'Buzz'
ELSE CAST(n AS VARCHAR(8))
END
FROM Nbrs
OPTION (MAXRECURSION 100);
Nice go, Manny... but because I'm mostly against the use of procedural code, I'd have to deduct a lot of points on this one. Contrary to popular belief, recursive CTEs are [font="Arial Black"]NOT[/font] set based and they are frequently slower and more resource intensive than a well written WHILE loop.
It is good to take a standpoint Jeff and stick to it but there is a thing called obsessiveness. I tested this query against the one I posted with the temp table and both gave me 3 ms and I used your way of checking a query's duration with the start and en dates and datediff in ms. Would that then be so bad?!
As far as scalability goes, 32767 is the max # of rows for that solution. If you exceed that you get a msg: MAXRECURSION option exceeds the allowed maximum of 32767. It takes 2 seconds to run up to that # on my machine. And it appears that the default maxrecursion is 100 if no related option is stated.
February 23, 2010 at 1:14 am
Jeff Moden (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.
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. 🙂
I get exactly the same problem with 3GL developers who do not understand that when they develop prototypes that they are rarely Throwaway Prototypes but usually Evolutionary Prototypes (these are terms that can be looked up if you do not know or cannot guess the difference).
Very simply: every line of code you write should be of production quality. To paraphrase a line from a film I don't rate by an actor I don't rate either: "If you write it, they will release it".
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
February 23, 2010 at 2:10 am
Jeff Moden (2/22/2010)
Steve Jones - Editor (2/22/2010)
No argument about the best programmer, and I'm sure I'd miss out on some jobs if this is the only criteria. In an interview, my first thought is solve the problem, and a set based solution doesn't necessarily occur to me quickly for this issue. It does for others, but not this one.Typically I haven't had someone at a computer, but a whiteboard. Trying to see how they think. To me, standards, syntax, getting code broken on lines/formatted, is something that can be taught. Basic algorithms, or the ability to build them, along with a personality can't be taught.
I agree... it's all something that can be taught. But if 2 people are tied up on all other fronts including personality and other things, which one do you think will get the job? Someone who wrote a blob of unformatted, non-scalable code or someone who put a little thought and effort into it?
Again, I'm not whacking at people to make them feel bad... I'm whacking at people because I'm one of the people that does the interviews and have also won the job "contest" over a whole bunch of people with a bunch of fancy letters after their name. I'm trying to give them a leg up on winning the contest that I've been so successful at winning.
If you want the job, be "the best" candidate and I'm telling you how to be "the best" candidate.
Do you think my piece of code is a blob because I can tell you I put every effort in and wrote that piece of code as if I was in an interview.
:-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 2:12 am
Steve Jones - Editor (2/22/2010)
Jeff Moden (2/22/2010)
... which one do you think I'll pick?The one that says pork chop the most :w00t:
hehehehehehehehehe:w00t::w00t::w00t::w00t:
:-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 2:33 am
Jeff I am on your case today. I took my temp table query and did a duration in ms and I got 3 ms. Guess what I got when I ran your Tally table query. I give you the honor to run it yourself. Performance, performance, performance. My blob did much better than your well contructed and understandable code. I am sorry Jeff, according to your standards the job is mine.
-- Jeff Moden's tally table query as copied from his article in SSC
--================-- Setup--==========================
USE PostalDB
SET NOCOUNT ON
DECLARE @StartTime DATETIME
SET @StartTime = GETDATE()
IF OBJECT_ID('dbo.Tally') IS NOT NULL DROP TABLE dbo.Tally
SELECT TOP 11000 IDENTITY(INT,1,1) AS N INTO dbo.Tally FROM Master.dbo.SysColumns sc1, Master.dbo.SysColumns sc2
ALTER TABLE dbo.Tally ADD CONSTRAINT PK_Tally_N PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100
-- At this point I am taking this out of the query to give him a fair chance
--GRANT SELECT, REFERENCES ON dbo.Tally TO PUBLIC
--from here I add fizzbuzz code
select n, case when n % 3 = 0 then 'Fizz' else '' end [fizz],
case when n % 5 = 0 then 'Buzz' else '' end [Buzz],
case when n % 3 = 0 and n % 5 = 0 then 'FizzBuzz' else '' end [FizzBuzz]
from tally
SELECT STR(DATEDIFF(ms,@StartTime,GETDATE())) + ' Milliseconds duration'
-- Manie's temp table query as written by himself
declare @start datetime, @end datetime
select @start = GETDATE()
if isnull(object_id('tempdb..##rowtest'),0) <> 0 drop table ##rowtest
select top 11000 identity(int,1,1) idkey into ##rowtest from syscolumns
select idkey, case when idkey % 3 = 0 then 'Fizz' else '' end [fizz],
case when idkey % 5 = 0 then 'Buzz' else '' end [buzz],
case when idkey%3 = 0 and idkey%5 = 0 then 'FizzBuzz' else '' end [fizzbuzz]
from ##rowtest
select @end = GETDATE()
select DATEDIFF(ms, @start,@end) ' Milliseconds duration'
Run it for yourselves and check it out.
:-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 2:35 am
reidres (2/23/2010)
Manie Verster (2/23/2010)
Jeff Moden (2/22/2010)
Manie Verster (2/22/2010)
I checked out Grant Fritchey's[/url] article and was, like Jeff, not very pleased to see some while loops but one query attracted my attention and I tested it and I like it.
;WITH Nbrs(n) AS
(
SELECT 1
UNION ALL SELECT 1 + n FROM Nbrs WHERE n < 100
)
SELECT CASE WHEN n%5=0 AND n%3=0 THEN 'BizzBuzz'
WHEN n%3 = 0 THEN 'Bizz'
WHEN n%5 = 0 THEN 'Buzz'
ELSE CAST(n AS VARCHAR(8))
END
FROM Nbrs
OPTION (MAXRECURSION 100);
Nice go, Manny... but because I'm mostly against the use of procedural code, I'd have to deduct a lot of points on this one. Contrary to popular belief, recursive CTEs are [font="Arial Black"]NOT[/font] set based and they are frequently slower and more resource intensive than a well written WHILE loop.
It is good to take a standpoint Jeff and stick to it but there is a thing called obsessiveness. I tested this query against the one I posted with the temp table and both gave me 3 ms and I used your way of checking a query's duration with the start and en dates and datediff in ms. Would that then be so bad?!
As far as scalability goes, 32767 is the max # of rows for that solution. If you exceed that you get a msg: MAXRECURSION option exceeds the allowed maximum of 32767. It takes 2 seconds to run up to that # on my machine. And it appears that the default maxrecursion is 100 if no related option is stated.
Take note, you only have to do 100 records for this interview.
:-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 2:41 am
Jeff,
I have followed this thread from the start and I have read the article of yours you recommended. I was hoping to work out what solution you are eluding to. I cannot. Possibly I cannot piece together what I have read so far but you are suggesting, or at least that is how I am reading it, that there is an even more elegant (pertaining to performance and maintainability) solution.
Is it possible that the technique can be demonstrated. I am not looking for the answer to the so called interview question, however, I am looking to improve myself. Even if you gave me the answer I would expect to fail at interview as I would expect an interviewer to get me to explain my reasoning - said as much to lurkers as to Jeff!!!
Perhaps this is the basis of an article?
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
February 23, 2010 at 2:55 am
Deleted
February 23, 2010 at 6:44 am
Manie Verster (2/23/2010)
It is good to take a standpoint Jeff and stick to it but there is a thing called obsessiveness. I tested this query against the one I posted with the temp table and both gave me 3 ms and I used your way of checking a query's duration with the start and en dates and datediff in ms. Would that then be so bad?!
BWAA-HAAA!!!! I'm trying to tell you how to think about and practice solutions for questions like this in an interview where people might actually know what they're doing in the set-based world and you infer that I'm obsessive? Heh... mind if I call you "short sighted", Manie? 😛
The goal is not to impress the interviewers... the goal is to be more impressive than the 10 other people who will also be interviewing. Think about it, old friend... If you interview in front of people that actually know something about T-SQL and you write a recursive CTE and someone else writes a set-based solution, guess what's going to happen? You might come in a close second for not writing a WHILE loop but the other person will still get the job!
--Jeff Moden
Viewing 15 posts - 121 through 135 (of 363 total)
You must be logged in to reply to this topic. Login to reply