March 18, 2009 at 8:35 am
Kevin:
Changes do get made and for the better. Constructive suggestions eventually get heard. I was really happy to see try/catch logic emerge, and CTEs, and inline table valued functions. Declare and assign in one line is kind of a yawner. Saves me a couple of keystrokes is all. Doesn't solve problems or improve performance. But change is good.
However, there is one thing I ask you guys to consider.
When you have a question in C#, you don't submit it to to a VB forum. Seems like you should be able to, the logic is the same. But most people are going to ignore your question because you are in the wrong place trying to speak the wrong language. SQL is doubly tricky because the logic isn't even the same. As of his last post, Fox still seemed to be struggling with the fundamentals.
You have a particular syntax in mind that you prefer. Easiest example is {} instead of Begin...End. I don't happen to care for {}, but I'm old school and everyone is entitled to a preference. There are other languages with different conventions. Should SQL support all those as well? I'm sure you don't care as long as it looks more like the various versions of C. Much like Americans who want the whole world to not only speak English, but to use the words from the American vocabulary.
Once you start saying that SQL must have syntax variations to read more like the various procedural languages, then the people who support SQL databases are going to have to fight through all the different variations. Code on one system, or from one vendor, might look very little like code somewhere else. This is a real problem. When books and documentation are written, which syntax will be used for the examples? When discussing code in forums like this, how much time would be wasted in translating? Are some vary knowledgeable people going to say "I only use the VB syntax, or SQL Classic. Go ask somebody else." Proliferation of tongues is an ancient curse. A lot of good thinking might be isolated just because of syntax.
Of course, there is an answer, or rather a question, when that happens.
If it gets you what you want, I suspect you would say to the DB heavies:
"Big deal. If you're going to do DB work, why can't you just suck it up and learn the different syntaxes?"
Good question.
It applies to you guys right now. 😉
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
March 18, 2009 at 8:43 am
Now, back to business. Foxjazz is on the edge of enlightenment. I think we should cut the man some slack, because he has dropped the insults and started trying to cooperate. He was taught badly, and so learned bad habits, and we need to help unlearn him. So....
-------------------------------------------------------------------------
-- Pop Quiz for FOXJAZZ, just to make sure you are with us so far.
-- Based on what you have been shown, recode this as a set based solution
-- Hint#1: Anything with @ at the beginning must go.
-------------------------------------------------------------------------
create table #input (inputString varchar(100))
create table #output (outputString varchar(100))
insert into #input
select 'This is how we roll.' union all
select 'Just putting some strings in.' union all
select 'Setting up the problem and all that.' union all
select 'To make it easier for others to think about the problem.' union all
select 'Paradimethylaminobenzaldehyde '
----------------------------------------------------------------------------
-- Hint #2: everything between here and "select * from #output" can be
-- replaced with ONE line of code (whitespace)
-- or
-- two if you put the INSERT on a separate line for readability
-- or
-- three if you are excessive compulsive about putting a FROM
-- clause on its own separate line
-- or
-- seven, if you put every WORD on a separate line
----------------------------------------------------------------------------
declare @totallyUnnecessaryAndSuperfluousVariable varchar(100)
declare @sel cursor
----------------------------------------------------------------------------
-- ever notice how the loops for open-while-fetch all look alike?
-- wouldnt it be nice if you didnt have to fool with the boilerplate?
-- the fun part about programming is focusing on significant code
----------------------------------------------------------------------------
set @sel = cursor for
SELECT inputString from #input
open @sel
fetch next from @sel into @totallyUnnecessaryAndSuperfluousVariable
while (@@fetch_status = 0) -- so did we really read something or not?
begin
insert into #output (outputstring)
values(@totallyUnnecessaryAndSuperfluousVariable)
fetch next from @sel into @totallyUnnecessaryAndSuperfluousVariable -- didnt we do this already?
-- doesnt it seem silly to have to repeat it?
end
-------------------------------------
select * from #output
drop table #input
drop table #output
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
March 18, 2009 at 9:03 am
Bob, I'll join you in cutting foxjazz some slack, but there needs to be a bit more from him than just dropping the insults.
First, a simple apology for calling me dense would be a start.
Second, some actual recognition from him that we have shown that set based solutions exist for his cursor based processes and that they are actually more performant. At the moment, every time we solve a problem using a set based solution that he said could only be done using cursors and loops, he justs throws out another problem. No real recognition that there was a better solution.
Maybe, if the light bulb goes on, we'll finally get that.
March 18, 2009 at 9:25 am
Lynn, since you brought it up. I want to apologize for calling you dense.
It's been an adventure this week, I still don't have a computer from the recent crash and have only been able to remote into the server to get information.
That said, most of the problems I have encountered in the past has been with updates, not that many with inserts.
It seems that if I write a query like this:
declare @i int
select top 10 @i = @i + 1 , partnumber from prd
I shouldn't get an error. But hell what do I know.
March 18, 2009 at 9:27 am
Understood, Lynn.
Fox, I do agree that you were out of line calling Lynn dense. He's a pretty bright guy who has helped a LOT of people. He is also patient to the point that some of us call him a saint. It wouldn't kill you to summon up a little humility and apologize.
By the way, the pop quiz is very important. This thread is not going to continue indefinitely. We are interested in teaching you how to do it right, not in rewriting all your code for you. You are paid to do that, and we are all volunteers with day jobs of our own.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
March 18, 2009 at 9:30 am
Wow, I type this in and see the latest post. That was a good thing, Fox. I'm not being patronizing when I say I was not only pleased, but proud to see that. When a rant turns into productive communications it is a triumph for us all.
Is your question about updates all about getting sequential numbers for your rows, without using identity columns?
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
March 18, 2009 at 9:31 am
Now, back to business. Foxjazz is on the edge of enlightenment. I think we should cut the man some slack, because he has dropped the insults and started trying to cooperate. He was taught badly, and so learned bad habits, and we need to help unlearn him. So....
I have not seen any trace of any upcoming "enlightenment". His last post on sequences requiring a cursor or a loop still shows an obsession with such. Despites my multiple references to articles written by Jeff Moden on this subject.
If he is self-taught then he has not read the proper material. On the other if he has been taught by an incompetent person, there still is this fixation on inappropriate design and methods despite a truck load of iconstructive criticism. And his challenge to do it without cursors AFTER REMOVING the IDENTITY was yet an other attempt at proving everybody wrong.
This just in: Foxbat has actually apologized about the "dense" insult. It does seem that there some light beginning to filter trhough.
March 18, 2009 at 9:32 am
foxjazz,
I think you probably know a lot in your area. Likely run circles around me in C#. In SQL, however, you seem ignorant. That's not a bad thing, it's a lack of experience.
It's one thing to rant a bit about what you perceive as issues with the language/product. We get that, and we'd show you what we see are flaws in your argument. What we don't appreciate is the insults or inability to concede a point.
Sorry about your computer. Hope it gets back up soon.
Steve
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
March 18, 2009 at 9:38 am
I hear you, J.
But when you've only been taught one way, you build an entire conceptual model around it. It takes time to unlearn that model. In this case, the model is consistent with the procedural programming model, so it is even more strongly reinforced in his mind. Enlightenment comes in the instant that you realize the game is entirely different than what you thought it was.
I was first exposed to SQL in 1980 while working for IBM. It came as quite a shock because I grew up in a flat file and hierarchical db world. My first reaction was amazement. I didn't understand HOW it could do the things it did. More recently, I felt the same way when I first learned about tally tables. I have rewritten a LOT of code in the past year because of THAT epiphany.
I'm editing this to agree with Steve. I confess to people all the time that I'm ignorant, but not stupid. Ignorance isn't shameful. Ignorance just means you haven't been exposed to something before. Stupidity is refusing to admit ignorance.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
March 18, 2009 at 9:42 am
Bob Hovious (3/18/2009)
Understood, Lynn.By the way, the pop quiz is very important. This thread is not going to continue indefinitely.
Wut ? After all the effort we put into it, we deserve to be recognized as the ones who contributed to the longest ever thread in the Forum. Besides, we have not yet reached the point where Foxbat understands that there are far superior approaches than to use cursors.:smooooth:
If there is one reason so many of us stuck on this thread instead of bailing out is to help Foxbat even though up to until today he was clearly not interested and rude. Since there is some beginning of light penetrating through, I think it is worth it now. Foxbat is definitely run into serious employment problems if he does not change his way of thinking.
The only experience I had getting totally lost in a new language was LISP. You know, (((((((((()]. I just gave up on that one. Apart form that, I felt no problem switching between FORTRAN, Pascal and C and then adding T-SQL. The greater level of difficulty came when Microsoft came up with dot net, only because there is a lot of additional stuff in there. So I am just picking up one bite at a time.
March 18, 2009 at 9:52 am
J, my dba wife pointed it out to me last night that set based solutions are not always superior to cursors. It depends on the database, however for batch job processes (the stuff I am doing) they should always be used instead of cursors.
March 18, 2009 at 10:14 am
{} is much better than BEGIN BEGIN BEGIN BEGIN END END END END
March 18, 2009 at 10:16 am
foxjazz (3/18/2009)
Lynn, since you brought it up. I want to apologize for calling you dense.It's been an adventure this week, I still don't have a computer from the recent crash and have only been able to remote into the server to get information.
That said, most of the problems I have encountered in the past has been with updates, not that many with inserts.
It seems that if I write a query like this:
declare @i int
select top 10 @i = @i + 1 , partnumber from prd
I shouldn't get an error. But hell what do I know.
First, I accept your apology.
Second, why don't you post one of those for us to look at and let us show you how they can be approached. Be sure to provide us with the table definitions, some sample data, and the expected results as well. This way we can do more than just write code, we can test and validate it as well.
March 18, 2009 at 10:16 am
foxjazz (3/18/2009)
J, my dba wife pointed it out to me last night that set based solutions are not always superior to cursors. It depends on the database, however for batch job processes (the stuff I am doing) they should always be used instead of cursors.
Foxjazz, I appreciate the change of tone. Today your contributions are considerably more civil.
"Always" is too strong a word. What we've all been saying is cursors should typically used as a last resort. The occasions where a cursor is the best way do exist, but they are few and far between. So, YES, cursors are useful SOME OF THE TIMES. Determining WHEN requires that you also examine the alternatives.
For instance, I see nothing wrong with IDENTITY.
I can give you as an example the case I had for producing two sets of UPC-12 codes: MMMMM-IIIIII-P
(5 digits to identify a manufacturer, 6 sequential item numbers and a parity checksum digit). My first cut, looping on each of the 100,000 sequential product numbers was done with loops in Pascal. Took 4 hours to run. My second cut was to do this in a stored procedure which still contained a loop, reducing the time to 45 minutes. My final cut was to forego the loop construct and replacing with a tally table and instead of doing 200,000 distinct INSERT statements, just post the entire 200,000 record set in one single INSERT all at once. This brought down the run time down to 39 seconds.
So dropping a loop improved the performance from 45 minutes down to 39 seconds. It is based on this experience that I now question whether loops of cursor are the immediate, obvious solution.
While I would not say something like I would do anything to avoid cursors even in the rare occasions where they would make sense, it is a safe bet to use cursors as a last resort, after other approaches are worse than cursors.
About the DBA you mentioned, she should be able to give you specific examples where a cursor is the appropriate solution. I am sure she did encounter situations where cursors were the best approach. But none of the examples you posted so far were valid instances for cursors.
Regards.
And I do insist you study the articles from Jeff Moden that are mentioned in the signature block of Lynn Pettis.
March 18, 2009 at 10:23 am
foxjazz (3/18/2009)
{} is much better than BEGIN BEGIN BEGIN BEGIN END END END END
Hmmm. In the LISP example I posted (((((((((()].
Note the last squared bracket in the end. This a shortcut which allows the programmer to close ANY remaining unclosed opening parenthesis. I have seen it used when they get lost in counting the remaining number of these. The existence of such a shortcut tends to indicate that a single charater BEGIN and END can result in confusion.
In any case, I view this as a minor point, which pales in significance to the main object of this discussion: cursors are to be or not to be.
Viewing 15 posts - 226 through 240 (of 465 total)
You must be logged in to reply to this topic. Login to reply