Professional Web Applications Themes

Are Cursors in Triggers really bad? - Microsoft SQL / MS SQL Server

I'm using SQL Server 7. I've got two versions of an trigger that just keeps an audit record of when a table was updated. One uses a cursor, and the other is set-based. I've read that you should try to keep cursors out of triggers, because they are slower. However, when I look at the execution plan in Query yzer for these two, the one that uses cursors looks faster. This is how I tested it. I ran update order_ set blah = "blah" where order_no = 123456 For this trigger... CREATE TRIGGER order__audit_update ON dbo.order_ FOR UPDATE AS declare ...

  1. #1

    Default Are Cursors in Triggers really bad?

    I'm using SQL Server 7.

    I've got two versions of an trigger that just keeps an audit record of when
    a table was updated. One uses a cursor, and the other is set-based. I've
    read that you should try to keep cursors out of triggers, because they are
    slower.

    However, when I look at the execution plan in Query yzer for these two,
    the one that uses cursors looks faster. This is how I tested it. I ran

    update order_ set blah = "blah" where order_no = 123456

    For this trigger...
    CREATE TRIGGER order__audit_update ON dbo.order_
    FOR UPDATE
    AS
    declare orderno integer
    DECLARE MyCursorOLDel CURSOR for
    (select order_no from inserted)
    open MyCursor
    FETCH NEXT FROM MyCursor
    INTO orderno
    WHILE FETCH_STATUS = 0
    begin
    insert into order_a
    (action, order_no, user_id, action_date)
    values
    ('U', orderno, system_user, getdate())
    FETCH NEXT FROM MyCursor
    INTO orderno
    end
    CLOSE MyCursor
    Deallocate MyCursor

    ....my QA results were
    Query 1: update order_ ...
    subtree cost = 0.0402, diagram not important
    Query 2: insert order_a...
    subtree cost = 0.0100, and the diagram shows "Insert <- Clustered Index".

    For this trigger...
    CREATE TRIGGER order__audit_update ON dbo.order_
    FOR UPDATE
    AS
    insert into order_a
    (action, order_no, user_id, action_date)
    (select 'U', order_no, system_user, getdate() from inserted)

    ....my QA results were...
    Query 1: update order_ ...
    subtree cost = 0.0402, diagram not important
    Query 2: insert order_a...
    subtree cost = 0.0476, and the diagram shows "Insert <-Clustered Index <-
    Compute Scalar <- Inserted Scan"
    (and the subtree cost of the Inserted Scan was 0.0376.)

    So I don't get it. Should I conclude that it is actually better to use
    cursors in triggers in some cases? Or am I misinterpreting the QA results?

    (If you're going to suggest code, it needs to work with multirow updates.)

    Thanks,
    George


    George Wynne Guest

  2. #2

    Default Re: Are Cursors in Triggers really bad?

    George,

    In the example you showed there is absolutely no reason to use a cursor.
    Without actually looking at the plans I can't say for sure what that was
    showing but the bottom line is they both have to do the same work when it
    comes to the insert into the Order_a table when you are talking about a
    single row. The only difference is the cursor has more overhead in general
    than just the Insert - Select statement on top of it. When you get to
    multiple rows it gets even worse as the numbers increase. You shouldn't
    even give it a thought when you can do it set based then that is what you
    should use. Checking the query plans to see which is more optimal will only
    waste time you could be spending on optimizing something that really needs
    it. Now I am not saying you couldn't come up with a cursor based example
    that out performed a set based in some situation but it is highly unlikely.

    --

    Andrew J. Kelly
    SQL Server MVP


    "George Wynne" <georgenssco.com> wrote in message
    news:eddOpP9PDHA.2244TK2MSFTNGP11.phx.gbl...
    > I'm using SQL Server 7.
    >
    > I've got two versions of an trigger that just keeps an audit record of
    when
    > a table was updated. One uses a cursor, and the other is set-based. I've
    > read that you should try to keep cursors out of triggers, because they are
    > slower.
    >
    > However, when I look at the execution plan in Query yzer for these
    two,
    > the one that uses cursors looks faster. This is how I tested it. I ran
    >
    > update order_ set blah = "blah" where order_no = 123456
    >
    > For this trigger...
    > CREATE TRIGGER order__audit_update ON dbo.order_
    > FOR UPDATE
    > AS
    > declare orderno integer
    > DECLARE MyCursorOLDel CURSOR for
    > (select order_no from inserted)
    > open MyCursor
    > FETCH NEXT FROM MyCursor
    > INTO orderno
    > WHILE FETCH_STATUS = 0
    > begin
    > insert into order_a
    > (action, order_no, user_id, action_date)
    > values
    > ('U', orderno, system_user, getdate())
    > FETCH NEXT FROM MyCursor
    > INTO orderno
    > end
    > CLOSE MyCursor
    > Deallocate MyCursor
    >
    > ...my QA results were
    > Query 1: update order_ ...
    > subtree cost = 0.0402, diagram not important
    > Query 2: insert order_a...
    > subtree cost = 0.0100, and the diagram shows "Insert <- Clustered Index".
    >
    > For this trigger...
    > CREATE TRIGGER order__audit_update ON dbo.order_
    > FOR UPDATE
    > AS
    > insert into order_a
    > (action, order_no, user_id, action_date)
    > (select 'U', order_no, system_user, getdate() from inserted)
    >
    > ...my QA results were...
    > Query 1: update order_ ...
    > subtree cost = 0.0402, diagram not important
    > Query 2: insert order_a...
    > subtree cost = 0.0476, and the diagram shows "Insert <-Clustered Index <-
    > Compute Scalar <- Inserted Scan"
    > (and the subtree cost of the Inserted Scan was 0.0376.)
    >
    > So I don't get it. Should I conclude that it is actually better to use
    > cursors in triggers in some cases? Or am I misinterpreting the QA
    results?
    >
    > (If you're going to suggest code, it needs to work with multirow updates.)
    >
    > Thanks,
    > George
    >
    >

    Andrew J. Kelly Guest

  3. #3

    Default Re: Are Cursors in Triggers really bad?

    It's not that I'm wasting time. It's just that I'm curious. And I have
    more complicated triggers with cursors that I am willing to rewrite, if and
    when I am convinced that cursors in triggers are really so bad.

    But you did mention updates of multiple rows, which I did not test. So I
    tried it just now.
    For the trigger with the cursor, the trigger fired multiple calls for
    "insert order_a", each of which has subtree cost = 0.0100.
    For the set-based trigger , the trigger only fired one call for "insert
    order_a", which had subtree cost = 0.0477, regardless of how many order_
    rows were affected by the original update.

    So, it looks to me, again, if I can completely trust what I am seeing in
    Query yzer, that the cursor-based trigger is somewhat faster when the
    update affects fewer than 5 rows, but the set-based trigger is faster when
    the update affects 5 or more rows (and the more rows, the bigger the
    difference).

    So, I wonder if I can assume these results are a general rule. Maybe I
    should look at my more complicated triggers with cursors and only rewrite
    them if I often perform multi-row updates on those tables.

    I welcome any more thoughts and experiences on the subject.

    Thanks,
    George


    George Wynne Guest

  4. #4

    Default Re: Are Cursors in Triggers really bad?

    George,

    Don't assume that a higher cost value translates directly to longer
    execution time. Cost is not a time based figure but a reflection of overall
    work, resources addressed etc. It is an estimate and not always will you
    see a direct correlation to time spent. A plan that costs more, may in fact
    bring the results back in shorter time. An example would be parallel
    processing. Using 4 procs to run a query is more costly in terms of
    resources but will generally give results in faster time frames. In the end
    it is what returns the results in a shorter amount of time that is generally
    the desired effect. Then come amount of I/O, CPU etc. Another thing to
    consider is that you essentially tie the optimizers hands behind it's back
    when using a cursor in that it no longer can do certain things that it might
    choose to do with set based logic. For instance if you had 100 rows to
    update in the trigger at one time, a set based approach may choose to do a
    page lock where as the cursor approach will lock 100 individual rows even if
    they are on the same page. So there is a lot more to it than just looking
    at the cost of the query plan. If you go SET based you will always be more
    likely to get a more efficient overall execution than a cursor based
    solution.

    --

    Andrew J. Kelly
    SQL Server MVP


    "George Wynne" <georgenssco.com> wrote in message
    news:OJ2eLr9PDHA.1584TK2MSFTNGP11.phx.gbl...
    > It's not that I'm wasting time. It's just that I'm curious. And I have
    > more complicated triggers with cursors that I am willing to rewrite, if
    and
    > when I am convinced that cursors in triggers are really so bad.
    >
    > But you did mention updates of multiple rows, which I did not test. So I
    > tried it just now.
    > For the trigger with the cursor, the trigger fired multiple calls for
    > "insert order_a", each of which has subtree cost = 0.0100.
    > For the set-based trigger , the trigger only fired one call for "insert
    > order_a", which had subtree cost = 0.0477, regardless of how many order_
    > rows were affected by the original update.
    >
    > So, it looks to me, again, if I can completely trust what I am seeing in
    > Query yzer, that the cursor-based trigger is somewhat faster when the
    > update affects fewer than 5 rows, but the set-based trigger is faster when
    > the update affects 5 or more rows (and the more rows, the bigger the
    > difference).
    >
    > So, I wonder if I can assume these results are a general rule. Maybe I
    > should look at my more complicated triggers with cursors and only rewrite
    > them if I often perform multi-row updates on those tables.
    >
    > I welcome any more thoughts and experiences on the subject.
    >
    > Thanks,
    > George
    >
    >

    Andrew J. Kelly Guest

  5. #5

    Default Re: Are Cursors in Triggers really bad?

    Yes, I'm convinced that set-based cursors are better for tables with
    multi-row updates.

    I tried these triggers in SQL Server Profiler, as someone else suggested.

    The set-based cursor outperformed the cursor-based one when the update
    affected 100 rows, as far as CPU, reads, and duration go.

    They performed about the same for single-row updates.

    Thanks,
    George

    "Andrew J. Kelly" <sqlmvpnooospamshadhawk.com> wrote in message
    news:Of$roz9PDHA.2852tk2msftngp13.phx.gbl...
    > George,
    >
    > Don't assume that a higher cost value translates directly to longer
    > execution time. Cost is not a time based figure but a reflection of
    overall
    > work, resources addressed etc. It is an estimate and not always will you
    > see a direct correlation to time spent. A plan that costs more, may in
    fact
    > bring the results back in shorter time. An example would be parallel
    > processing. Using 4 procs to run a query is more costly in terms of
    > resources but will generally give results in faster time frames. In the
    end
    > it is what returns the results in a shorter amount of time that is
    generally
    > the desired effect. Then come amount of I/O, CPU etc. Another thing to
    > consider is that you essentially tie the optimizers hands behind it's back
    > when using a cursor in that it no longer can do certain things that it
    might
    > choose to do with set based logic. For instance if you had 100 rows to
    > update in the trigger at one time, a set based approach may choose to do a
    > page lock where as the cursor approach will lock 100 individual rows even
    if
    > they are on the same page. So there is a lot more to it than just looking
    > at the cost of the query plan. If you go SET based you will always be
    more
    > likely to get a more efficient overall execution than a cursor based
    > solution.
    >
    > --
    >
    > Andrew J. Kelly
    > SQL Server MVP
    >
    >
    > "George Wynne" <georgenssco.com> wrote in message
    > news:OJ2eLr9PDHA.1584TK2MSFTNGP11.phx.gbl...
    > > It's not that I'm wasting time. It's just that I'm curious. And I have
    > > more complicated triggers with cursors that I am willing to rewrite, if
    > and
    > > when I am convinced that cursors in triggers are really so bad.
    > >
    > > But you did mention updates of multiple rows, which I did not test. So
    I
    > > tried it just now.
    > > For the trigger with the cursor, the trigger fired multiple calls for
    > > "insert order_a", each of which has subtree cost = 0.0100.
    > > For the set-based trigger , the trigger only fired one call for "insert
    > > order_a", which had subtree cost = 0.0477, regardless of how many order_
    > > rows were affected by the original update.
    > >
    > > So, it looks to me, again, if I can completely trust what I am seeing in
    > > Query yzer, that the cursor-based trigger is somewhat faster when
    the
    > > update affects fewer than 5 rows, but the set-based trigger is faster
    when
    > > the update affects 5 or more rows (and the more rows, the bigger the
    > > difference).
    > >
    > > So, I wonder if I can assume these results are a general rule. Maybe I
    > > should look at my more complicated triggers with cursors and only
    rewrite
    > > them if I often perform multi-row updates on those tables.
    > >
    > > I welcome any more thoughts and experiences on the subject.
    > >
    > > Thanks,
    > > George
    > >
    > >
    >
    >

    George Wynne Guest

  6. #6

    Default Re: Are Cursors in Triggers really bad?

    sorry - I meant "set-based triggers"

    "George Wynne" <georgenssco.com> wrote in message
    news:ec9tSF#PDHA.452TK2MSFTNGP11.phx.gbl...
    > Yes, I'm convinced that set-based cursors are better for tables with
    > multi-row updates.

    George Wynne Guest

  7. #7

    Default Re: Are Cursors in Triggers really bad?

    sorry, I meant "set-based trigger"

    "George Wynne" <georgenssco.com> wrote in message
    news:u$G6HC#PDHA.3016TK2MSFTNGP10.phx.gbl...
    > The set-based cursor outperformed the cursor-based one when the update
    > affected 100 rows, as far as CPU, reads, and duration go.

    George Wynne Guest

Similar Threads

  1. Cursors
    By flashster in forum Macromedia Director Lingo
    Replies: 8
    Last Post: December 12th, 01:47 PM
  2. Why are my custom cursors flashing?
    By Kenny Fraser in forum Macromedia Director Lingo
    Replies: 3
    Last Post: December 11th, 09:28 AM
  3. posible BUG in CURSORS...
    By Gabriel Sebastián Rivera in forum Macromedia Director Lingo
    Replies: 1
    Last Post: December 2nd, 02:58 PM
  4. cursors problem
    By sylmy webforumsuser@macromedia.com in forum Macromedia Director Basics
    Replies: 1
    Last Post: October 22nd, 07:05 PM
  5. Change cursors opacity to 0?
    By OniLink webforumsuser@macromedia.com in forum Macromedia Director Lingo
    Replies: 3
    Last Post: October 21st, 01:54 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139