February 23, 2010 at 11:24 pm
OK, I ran Jason's (CirquedeSQLeil) updated code (modified by me as before) and got 0:54, which puts it as the fastest time of the scalable three that I reviewed.
I looked at Fatherjack's entry and since it can't handle 150 000 000 but only about 28 000 000, I have not done any further research on it.
I am ignoring Kev's first entry because it was too slow to consider.
TimothyAWiseman avoided TSQL, so it fails the job interview scenario TSQL expectations.
February 23, 2010 at 11:32 pm
reidres (2/23/2010)
OK, I ran Jason's (CirquedeSQLeil) updated code (modified by me as before) and got 0:54, which puts it as the fastest time of the scalable three that I reviewed.I looked at Fatherjack's entry and since it can't handle 150 000 000 but only about 28 000 000, I have not done any further research on it.
I am ignoring Kev's first entry because it was too slow to consider.
TimothyAWiseman avoided TSQL, so it fails the job interview scenario TSQL expectations.
Thanks for doing all that testing.
I think there are a few things to note.
With the size of the recordset, memory becomes an issue. Another is tempdb and disk space become an issue. One may run into issues depending on settings. I didn't encounter any of these issues with the queries in question until running 150 million records.
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 24, 2010 at 12:29 am
CirquedeSQLeil (2/23/2010)
I think there are a few things to note.
With the size of the recordset, memory becomes an issue. Another is tempdb and disk space become an issue. One may run into issues depending on settings. I didn't encounter any of these issues with the queries in question until running 150 million records.
I just for fun did a test with the modified version of your latest for 1 billion and it came in @ 5:16.
February 24, 2010 at 5:41 am
Manie Verster (2/23/2010)
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.
Heh... that's the spirit, Manny. You took it all very well, thank you.
I'll try to remember to post some code here that shows when and how the use of Global Temp tables can cause a failure during multiple simultaneous runs.
As a side bar, I said I wouldn't post a solution to the FizzBuzz problem and I won't go back on my word to that effect just because there' a contest. I have to let you good folks have all the fun on that one. Sorry.
--Jeff Moden
February 24, 2010 at 5:45 am
CirquedeSQLeil (2/23/2010)
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.
Perfect, Jason. That's exactly what I've been trying to convey especially the part about "ingrained". Thanks.
--Jeff Moden
February 24, 2010 at 6:13 am
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.
Heh... Being a mere human, I assure you that I'm far from perfect and my code sometimes gets spanked by someone smarter just like anyone else. But I realized a very long time ago (in the military, in fact) that getting nervous can cost you no matter what you do. I do get a bit anxious but in a good way... it drives me to do the best I can. The training in the military has taught me that if I allow it to interfer with my thought process, then it will negatively impact the task at hand (whatever it is) and that goes for interviews, as well.
--Jeff Moden
February 24, 2010 at 7:00 am
reidres (2/23/2010)
Are my modifications fair?
My problem with comparing the two solutions above is you are actually comparing apples to oranges. Jason's routine is returning the results directly where as mine is writing them to disk (#TempTable).
February 24, 2010 at 9:06 am
Lynn Pettis (2/24/2010)
reidres (2/23/2010)
Are my modifications fair?My problem with comparing the two solutions above is you are actually comparing apples to oranges. Jason's routine is returning the results directly where as mine is writing them to disk (#TempTable).
Yes similar in that they use cascading cte's, but different like Lynn said.
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 24, 2010 at 9:37 am
Improved the performance of my code, I moved the row_number() function into its own CTE instead of in the last CTE accomplishing CROSS JOIN's. Increased performance almost 50%.
February 24, 2010 at 9:58 am
Interesting change Lynn. I love when you guys start testing competing methods, because it always means that I'm going to learn good stuff without actually having to *do* anything :-P:-D.
A little confused though, why are we all dumping results to a temp table, then selecting from that table? Is this just because we started that way and wanted to compare apples to apples? I tried to go back and find a reason, but didn't see anything.
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
February 24, 2010 at 10:22 am
jcrawf02 (2/24/2010)
Interesting change Lynn. I love when you guys start testing competing methods, because it always means that I'm going to learn good stuff without actually having to *do* anything :-P:-D.A little confused though, why are we all dumping results to a temp table, then selecting from that table? Is this just because we started that way and wanted to compare apples to apples? I tried to go back and find a reason, but didn't see anything.
As Jeff would say, display is the great equalizer. I was trying to keep everything on the server (or home PC) when testing. If I change the code to populate a variable, the run time for 1,000,000 records drops under 1 second (between 800-900 ms IIRC).
February 24, 2010 at 10:44 am
CirquedeSQLeil (2/23/2010)
reidres (2/23/2010)
OK, I ran Jason's (CirquedeSQLeil) updated code (modified by me as before) and got 0:54, which puts it as the fastest time of the scalable three that I reviewed.I looked at Fatherjack's entry and since it can't handle 150 000 000 but only about 28 000 000, I have not done any further research on it.
I am ignoring Kev's first entry because it was too slow to consider.
TimothyAWiseman avoided TSQL, so it fails the job interview scenario TSQL expectations.
Thanks for doing all that testing.
I think there are a few things to note.
With the size of the recordset, memory becomes an issue. Another is tempdb and disk space become an issue. One may run into issues depending on settings. I didn't encounter any of these issues with the queries in question until running 150 million records.
I thought TimothyAWiseman's made a great point for an interview answer. Sometimes T-sql isn't the right tool. Knowing when not to use it is as important as knowing when not to. Everything looks like a nail when all you know how to use is a hammer.
---------------------------------------------------------------------
Use Full Links:
KB Article from Microsoft on how to ask a question on a Forum
February 24, 2010 at 10:51 am
tstaker (2/24/2010)
CirquedeSQLeil (2/23/2010)
reidres (2/23/2010)
OK, I ran Jason's (CirquedeSQLeil) updated code (modified by me as before) and got 0:54, which puts it as the fastest time of the scalable three that I reviewed.I looked at Fatherjack's entry and since it can't handle 150 000 000 but only about 28 000 000, I have not done any further research on it.
I am ignoring Kev's first entry because it was too slow to consider.
TimothyAWiseman avoided TSQL, so it fails the job interview scenario TSQL expectations.
Thanks for doing all that testing.
I think there are a few things to note.
With the size of the recordset, memory becomes an issue. Another is tempdb and disk space become an issue. One may run into issues depending on settings. I didn't encounter any of these issues with the queries in question until running 150 million records.
I thought TimothyAWiseman's made a great point for an interview answer. Sometimes T-sql isn't the right tool. Knowing when not to use it is as important as knowing when not to. Everything looks like a nail when all you know how to use is a hammer.
I have to disagree here. This is a good way to see how some thinks. Do they write a cursor or while loop to complete the task or do they write something set based. It is also an opportunity to question some on why they chose their solution.
Before I learned about Tally tables, I would probably written a cursor or while loop to solve the problem.
February 24, 2010 at 11:02 am
Lynn Pettis (2/24/2010)
I have to disagree here. This is a good way to see how some thinks. Do they write a cursor or while loop to complete the task or do they write something set based. It is also an opportunity to question some on why they chose their solution.
Before I learned about Tally tables, I would probably written a cursor or while loop to solve the problem.
I've only used Tally tables when running through the articles on this site and haven't had an opportunity to use them in production. In an interview I would of used the while loop then explained to the interviewer that t-sql might not be the best approach. I might also explain how to do it with a cte and also the tally table concept.
---------------------------------------------------------------------
Use Full Links:
KB Article from Microsoft on how to ask a question on a Forum
February 24, 2010 at 11:24 am
tstaker (2/24/2010)
Lynn Pettis (2/24/2010)
I have to disagree here. This is a good way to see how some thinks. Do they write a cursor or while loop to complete the task or do they write something set based. It is also an opportunity to question some on why they chose their solution.
Before I learned about Tally tables, I would probably written a cursor or while loop to solve the problem.
I've only used Tally tables when running through the articles on this site and haven't had an opportunity to use them in production. In an interview I would of used the while loop then explained to the interviewer that t-sql might not be the best approach. I might also explain how to do it with a cte and also the tally table concept.
T-sql may not be the best solution could be true. I think that if one can find a solution that meets the requirements for the interview and can do it efficiently, then it could be the right solution. The contest shows several t-sql solutions that put very little load on the server and do it very quickly. Is there a method using a different language that could do it faster - possibly. We have to be careful in interviews for a DBA position when recommending to do it some other way if the interviewer is looking for an answer in t-sql.
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
Viewing 15 posts - 166 through 180 (of 363 total)
You must be logged in to reply to this topic. Login to reply