Please give me a replacement for a Cursor
-
HT almost H. Hey at least I started the whole thing
.Thanx Noeld, I just wasn't getting what he wanted to do with that.
-
Conclusion to this real world case.
Procedural Approach : 2+ hours
Set based approach (even with lots of dynamic sql) = 26 secs
So that would make this method 277+ times faster.
Anybody else thinks that set based approach is not the way to go???
-
Not me, but then again I never did. It's always been "how to", not "whether to". I can understand the solution once I see it, but I can't always figure it out myself...
Thank God for SSC and helpful SQL folks!
-
That message was more directed towards the original posters of this thread.
-
Sorry for the delay guys I was out to lunch ( Ihave to eat you know
)OK Glad that we could help you and probably other readers of the thread too
cheers!
* Noel -
Understood. I've never subscribed to the argument that cursors or procedural code is the best way to do things, but when you take an "old" programmer and make him a SQL developer by handing him a bunch of manuals, guess what you're going to get. Procedural code. That's what we know. Maybe I can use this example to get my boss to spring for some more advanced training so I can see these solutions myself.
As for the others, I'm betting a lot of them come from the same background. The assumption is that a tight efficient loop must be the best way to do things, and if they don't fully understand set theory and can't see the set approach then that's what they'll write. Some people just can't admit when they're bound up in old habits. I doubt you'll find many SQL developers who've had the appropriate level of training and experience ascribing to cursors and procedural code.
But then again, I could be wrong.
-
I didn't have that approriate training as you call it. But i'm learning new stuff everyday so this set thing is getting pretty deep in my head
. -
I don't do it every day, I only get involved in about 1 SQL project a month so I don't even get regular exposure. Oftentimes I look at these set-based solutions that get posted and I really have to struggle to see how it works. This from a guy who got all A's in math, logic and every science I took. It's not that I don't understand sets, but the SQL to get there sometimes seems to be incredibly convoluted.
Maybe I'm just too mired in 30 years of writing procedural code...
-
This article is just 1 week late
.http://www.sqlservercentral.com/columnists/cherring/replacingcursorsandwhileloops.asp
-
Yup. In fact I posted this thread as a response to the article as another example...
-
Did we just start an infinite loop??
-
Could be...
-
thats why I don't like loops or cursors ... I use SET-BASED answers
* Noel -
Interesting thread - hopefully, you can help me with something similar.
One cursor usage that I'm trying to resolve is one in which the result set is passed to another sProc as one of the parameters. This sProc in turn, does an Insert and then Exec's another sProc using some of the params.
Functionality it is basically retrieving user names for a passed groupname. The cursor loops through the user names EXEC'ing another sproc for each user
Since the EXEC statement only accepts scalars, I haven't been able to think of a way around the cursor usage.
eg: this of course fails, but is an example of the goal:
EXEC usp_MyProc @MsgToSend='Foo', @UserName=(SELECT UserID FROM Groups WHERE GroupName='MyGroup')
-
Please post all the relevant table definition, some sample data and the expected results from the update statement. I'm sure we can optimize this.
Viewing 15 posts - 46 through 60 (of 70 total)
You must be logged in to reply to this topic. Login to reply