December 23, 2007 at 7:31 pm
Jeff Moden (12/23/2007)
Absinthe (12/23/2007)
Actually we have 2 differnt issues here:1.
I was not saying that the code was fast, merely that it specifically satisfied the described problem of doing it inline without using a temp table. It is a mere parlor trick.
Why would you teach someone who is asking for help a parlor trick instead of the right way to do things... if they take your word for it instead of taking it as a parlor trick, they're in trouble.
That is a good point. However, my concern was not to teach this person anything. The temp table answer was already given as was the 2005 solution. But he responded with "I want to do it without a temp table"
No one will ever accuse me of being Joe Celko. And especially in this case, I didn't feel it was my place to give the lecture on how SQL is a relational database and why would he ever want to use any kind of row numbers etc... I have use this particular parlor trick in getting the nth historical record back to implement algorithms for forcasting future delivery counts for newspapers and magazines and bakery items. However, I had alread reduced the number of records involved (more like 10 than 20000) so it worked fine.
As well, it demonstrates a different thinking. In a relational database there are not specific record numbers. And the lesson learned from this particular methodology is that the "faux row number" has a relationship to the data. It is a count of how many rows have a value less than that.
2. (completely unrelated to #1)
As for the "select into" I was red flagging it relative to its corresponding
Create Table #temp
(
...
)
insert into #temp
select ...
Other than the fact that it is easier to type, it is no faster to use. Also, it will cause more than necessary recompiles since it does ddl in the middle of the sproc. I am pretty sure it is not ansi standard. It works differntly in PL sql as it does in TSQL and if I am nto mistaken doesn't even exist in MYSQL at all.
I agree, but since you're using some form of an identity column to get the numbering in this case, your select would require a full column list as least in the insert clause. 😉
Not if the identity column was last would it?
Yes, I agree about the recompiles... unless you execute the SELECT INTO at the beginning of the code so you don't interlace the DDL with DML. And, don't forget... if as little as 4 or 5 rows change in a table, you may get a recompile on the next call, anyway.
Who cares about Ansi Standard? If you did, you wouldn't use a Temp Table at all. 😉 And why are you talking about Oracle and MySQL? You can't possibly believe in the myth of portable code.
Sorry, I am new to M$ specific forums. I cut my teeth on things like alt.comp.sql and places of that ilk. I am used to it being an issue. Granted I am personally a M$ whore in that I only really program for MS Sql Server. But if one sticks to standards and such, when M$ decides to change things for its upgrade of the month club, I mean yearly version change, they are less likely to change something that is ansi standard, than what they consider a M$ Extension... But I guess that is just my opinion...
The fact that it is locking both tempdb (the whole database) adds to the issues.
At least it's not a parlor trick that cripples a CPU for a minute on only 20,000 rows 😛
His example was merely 5 rows. It was a very viable solution to the universe and problem he presented.
But I will agree that it depends... if it's GUI code that's making hundreds or thousands of hits per second, then you should probably avoid SELECT INTO and use CREATE TABLE. In fact, if it's GUI code and you need a temp table, you're probably doing something wrong anyway. :hehe:
If it's reporting or other batch code, no problem.
I have no argument with you there.
December 23, 2007 at 10:59 pm
Correct... If the IDENTITY column is the last column, you wouldn't need a list of columns in the INSERT clause.
Heh... I've got a real live appreciation for being a bit of an M$ whore myself. 😛
Sorry about the banter... I just get really pissed when I see folks offer a "trick" solution that folks might perceive as being a panacea for a certain problem. It's the teacher in me. Thanks for owning up to it being a "parlor trick". 😀
Anyway, thanks for the good feedback. Don't know if you celebrate Christmas so lemme just say "Good Seasons" and "Happy New Year...
--Jeff Moden
Change is inevitable... Change for the better is not.
December 24, 2007 at 8:30 am
Well crap! I just took about an hour to enter a very detailed and intricate response to this. And of course after hittign the "post quoted reply" I got " The following error occurred...
Sorry the application encountered an unexpected error. Information about this error has been logged. If you continue to receive this message please contact the board administrator.
"
I refuse to try and remember it to type it all back in. So suffice it to say:
Thanks.
Happy Holidays.
As a fellow teacher I appreciate your stance.
Every tool has a purpose.
Every problem has more than one solution.
Not ever solution is optimal.
Solutions proposed for a particular problem, may have concepts helpful in solving other problems.
Something about using a "Numbers" table or even spt_Values to solve some problems like this, perhaps with a cross join and distinct.
An obscure reference to an article in "Computer Language" and multiple letters to the editor follups, regarding "If all you have is a hammer, then every problem looks like a nail." (It had to do with writing a cubed root in all sorts of differnt languages" and a reference to how this problem could provide such intereting fodder for discussion in its differign solutions.
And a humorous disclaimer about politically correct seasonal greetings.
However, when I wrote it, it was much more verbose and perhaps even enetertaining 🙂
later
December 24, 2007 at 8:33 am
Jeff Moden (12/23/2007)
Sorry about the banter... I just get really pissed when I see folks offer a "trick" solution that folks might perceive as being a panacea for a certain problem. It's the teacher in me. Thanks for owning up to it being a "parlor trick". 😀
No problem. As I said it may not be the perfect solution all the time. And in fact it is usually a parlor trick. But I also use it to teach a few syntactical lessons.
For example I create a table with a single unique column and walk them through it. Once they have the idea down. I change the table to have 4 columns that make up a unique key. You would be surprised how difficult people find getting the correct combination of = and <= or whatever in the comparison.
Now just as one might learn that the number itself represents a count of how many rows are less than or equal to the current one, it would stand to show that using a temp table also would give that same information.
As for a another real world application in which it worked pretty well was outputting denormalized invoice detail lines such that they were numbered per invoice. This (as a flat file) was the input to a hand held device. So again at most you needed to run through maybe 5 rows in the sub select something like
inv#(101), cust#(2000), detail# (1), detailinfo..
inv#(101), cust#(2000), detail# (2), detailinfo..
inv#(101), cust#(2000), detail# (3), detailinfo..
inv#(102), cust#(2220), detail# (1), detailinfo..
inv#(102), cust#(2220), detail# (2), detailinfo..
inv#(102), cust#(2220), detail# (3), detailinfo..
Where the dertail number was calculated in the "parlor trick fashion."
Anyway, thanks for the good feedback. Don't know if you celebrate Christmas so lemme just say "Good Seasons" and "Happy New Year...
No problem I will feed back as long as I feel like I have something meaningful to contribute. Just like the "goto" in most programming languages, every tool has its place. Triangular joins and "select into" included. Not every tool is the best tool for every situation, which is why we carry a toolbox full of them.
I also thought I saw an example once using a "numbers" table to do it. The idea was to keep a table around that just had consecutive numbers in it from 0 to some large number. Then it was used to do all sorts of things. I believe I have seen folks use Spt_Values similarly. I am just guessing that perhaps a crossjoin with a distinct might do it. And of course I have seen people just use a cursor and a counter to accomplish it as well. *shudder*
I remeber years ago in, I think, Computer Language magazine someone wrote an article abotu "If all you have is a Hammer, then every problem looks like a nail" and proceded to write a solution to something, I think it was a cubed root function or smoe such thing, in every differnt language imaginable. Over the following months many readers wrote in their perosnal favorite language solutions. It made for quite a bit of entertainment. I suspect that this particular problem (numbering rows) could result in several different solutions (obviously all of differing effficacy.) But I also believe somethgin can be learned from each proposed solution, and conceptually each could contribute to solving some other problem one might encounter.
Merry Christmas to you and Happy New Year. I am not easily offended by well wishes regardless of their origin. I am pretty sure, at least here in USA, every one celebrates Christmas to some extent. Whether it be related to their particular religious bent, or merely the fact that it has it's ecconomic and commercial implications, or just that one gets off of school or work for some part of it. I do acknowledge that there are some people actually opposed to the holiday itself, though I must have already offeded them by being someone who observes it. In genral, I think many people merely choose to be offended really easily. I have never seen the reason to lecture someone on my personal holiday observations when the mis-greeted mis-wished me on a particular holiday not in my repetior. In most cases, I merely interpretted it in the polite greeting that it was offered in.
*disclaimer*
I appologise for not being politically correct in my seasonal wishes. And if was offensive to anyone, please just interpret it to mean happy holidays. But also if you choose not to be happy, then just take it as an acknowlegement that the holidays are upon us. If however you do not get them off of work not observe them at this time please feel free to insert your favored generic letter closing such as "regards, or sincerely"
December 24, 2007 at 8:35 am
Wow, it posted afterall... Hopefully it lives up to my description. If not ignore the one that is less meaningful or less offensive.
December 25, 2007 at 6:02 am
Jeff Moden (12/23/2007) if it's GUI code that's making hundreds or thousands of hits per second, then you should probably avoid SELECT INTO and use CREATE TABLE. In fact, if it's GUI code and you need a temp table, you're probably doing something wrong anyway. :hehe:
Nothing wrong here.
Used temp tables for GUI calls, it was implemented in big production database and tested by 3rd party for performance and reliability.
They emulated thousands of calls from hundreds of users connected at the same moment. We had to switch the DDOS-attack protection off to let them perform that test.
And there was nothing wrong discovered.
_____________
Code for TallyGenerator
December 25, 2007 at 11:18 am
Perfect... thanks for the feedback, Serqiy! And, long time no see... Happy holidays!
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply