Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««2627282930

Eliminating Cursors Expand / Collapse
Author
Message
Posted Friday, February 19, 2010 5:40 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:44 PM
Points: 38,012, Visits: 34,935
RBarryYoung (2/18/2010)

1. Dynamic SQL is not the problem, Injection IS.
2. SQL Injection uses Dynamic SQL, but the two are far from synonymous.
3. Dynamic SQL is just a facility used for Injection, Client code is another.
4. It IS possible to write most needed dynamic SQL without Injection, and usually this is not hard.
5. Dynamic SQL is as safe as anything else in SQL, *IF*, you never Inject user-supplied text into the SQL command.
6. The principal means of doing 4 & 5 is to validate user-supplied text by replacing it with server-supplied text using some concrete table or function to generate valid-only text (in the context for which it is to be used).
7. The permissions problem with dynamic SQL that you alluded to earlier is only a problem for owner-chaining permissions. The stronger and safer privilege elevation techniques of either Impersonation or Certificates do not suffer from this problem and are preferred for Dynamic SQL in any event.


Heh... I especially like observation #1 above. Might be time for you to write a new article, Barry. I've had people argue that any form of dynamic SQL is wrong because of potential security risks... I'd love to point them to a really good article on the subject with the simple instructions of "Read this and see why you're wrong."


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #868985
Posted Friday, February 19, 2010 11:11 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Friday, June 12, 2015 8:42 AM
Points: 9,294, Visits: 9,496
Heh. I did do a presentation on it. And I posted the presentation kit, including SQL code examples and Client code at my blog, here:http://movingsql.com/dnn/LinkClick.aspx?fileticket=pa1HXFdJ5Bs%3d&tabid=125&mid=911 But, I never did get around to writing the article I intended.

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #869572
Posted Friday, February 19, 2010 11:38 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Friday, June 12, 2015 8:42 AM
Points: 9,294, Visits: 9,496
Here is one of several of my Blog posts that talks about this: http://www.movingsql.com/dnn/Default.aspx?tabid=125&EntryID=190. In fact if you go my Blog site and click on the "Dynamic SQL" topic you'll find almost a dozen articles on this subject.

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #869576
Posted Sunday, February 21, 2010 10:06 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:44 PM
Points: 38,012, Visits: 34,935
Very cool set of links, Barry. Great blog site on your part, as well! Other than the awesome content (haven't explored it all, yet, of course) is that it's easy on the eyeballs. There's just something to be said about a traditional white background with dark letters instead of the other way around.

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #869861
Posted Sunday, February 21, 2010 12:41 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Friday, June 12, 2015 8:42 AM
Points: 9,294, Visits: 9,496
Jeff Moden (2/21/2010)
Very cool set of links, Barry. Great blog site on your part, as well! Other than the awesome content (haven't explored it all, yet, of course) is that it's easy on the eyeballs. There's just something to be said about a traditional white background with dark letters instead of the other way around.

Heh, yeah, Grant's wife really punctured my bloated ego about my previous site style, so I changed it to something a little more restrained.


-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #869893
Posted Thursday, February 26, 2015 7:26 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 8:27 AM
Points: 452, Visits: 454
Mark Hickin (1/17/2006)


I think it's worth making the point that this approach does nothing to combat what is fundamentally bad about cursors. All we have done here is createda cursor, without using the declare cursor syntax.

The article states:

<However, this kills the whole theory of set-based operations, which are inherently faster than their row based counterparts. The following is one way of converting your CURSOR statement using the WHILE keyword in such a scenario>


Yes, but this still isn't set based is it? It's still doing everything row by row, which is exactly what we want to avoid.

Much better to take a step back and see if you process many, or all of the purchase orders in a small number of set based sql statements, and try not to take such a procedural approach.

I'd also say that the 75% performance improvement is highly dubious. How can this be? Presumably, the work is being done within the usp_generate_purchase_order procedure. Are we really saying that the looping code was the bottleneck? If this process took 1 min originally, are we saying that by substituting the cursor for the while loop reduced it to 15 secs? I really don't think so.

I'm not saying that this construct should never be used. There are many cases when you might want to slow things down (archiving/purging/data movingfor example) to aid concurrency and reduce the size of transactions, but this is not a good pattern to use in high performance database programming.


I would guess that the difference was their cursor inspected more rows.
Putting only the rows that needed operation into the in memory table and then running through the loop is the best option for any loop.
I would wonder why not use CTE ( as much as I dislike them).

Post #1663803
Posted Wednesday, March 11, 2015 6:48 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:44 PM
Points: 38,012, Visits: 34,935
PHYData DBA (2/26/2015)
Mark Hickin (1/17/2006)


I think it's worth making the point that this approach does nothing to combat what is fundamentally bad about cursors. All we have done here is createda cursor, without using the declare cursor syntax.

The article states:

<However, this kills the whole theory of set-based operations, which are inherently faster than their row based counterparts. The following is one way of converting your CURSOR statement using the WHILE keyword in such a scenario>


Yes, but this still isn't set based is it? It's still doing everything row by row, which is exactly what we want to avoid.

Much better to take a step back and see if you process many, or all of the purchase orders in a small number of set based sql statements, and try not to take such a procedural approach.

I'd also say that the 75% performance improvement is highly dubious. How can this be? Presumably, the work is being done within the usp_generate_purchase_order procedure. Are we really saying that the looping code was the bottleneck? If this process took 1 min originally, are we saying that by substituting the cursor for the while loop reduced it to 15 secs? I really don't think so.

I'm not saying that this construct should never be used. There are many cases when you might want to slow things down (archiving/purging/data movingfor example) to aid concurrency and reduce the size of transactions, but this is not a good pattern to use in high performance database programming.


I would guess that the difference was their cursor inspected more rows.
Putting only the rows that needed operation into the in memory table and then running through the loop is the best option for any loop.
I would wonder why not use CTE ( as much as I dislike them).



Before anyone jumps into this with a reminder, yeah... we realize this is a 9 year old post.

Just pitching in my 2 cents here...

As always, "It Depends". I've found that for control loops, it's usually not the loop that causes the problem. It the fact that people forget about things like table scans on the control table. We have a huge problem at work because people either do the SELECT TOP 1/DELETE WHERE in each iteration or they do the counter thing with a lookup on large control tables that aren't indexed. Each iteration causes a table scan. In the case of the SELECT TOP 1/DELETE WHERE the row with the RowNum was just used, you end up with the equivalent of a triangular join. On the counter thing, you end up with the equivalent of a square join (Cartesian Product) and then they wonder why the heck their code is so slow.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1667855
« Prev Topic | Next Topic »

Add to briefcase «««2627282930

Permissions Expand / Collapse