Professional Web Applications Themes

Opinions? - Microsoft SQL / MS SQL Server

Hi. I'm seeking opinions regarding a stored procedure coding practice that I keep running into. At a high level, here's what I'm dealing with: One stored procedure that contains a loop with calls to several other stored procedures. Rough example... create procedure A as while (some condition) begin begin transaction exec B (with some parameters) exec C (with some parameters) exec D (with some parameters) exec E (with some parameters) commit transaction end Stored procedures B,C,D and E do not contain explicit transactions. Procedures B,C,D and E also are not called separately by any other part of the system. I ...

  1. #1

    Default Opinions?

    Hi.

    I'm seeking opinions regarding a stored procedure coding practice that I
    keep running into.

    At a high level, here's what I'm dealing with:

    One stored procedure that contains a loop with calls to several other stored
    procedures.

    Rough example...

    create procedure A
    as

    while (some condition)
    begin
    begin transaction

    exec B (with some parameters)
    exec C (with some parameters)
    exec D (with some parameters)
    exec E (with some parameters)

    commit transaction
    end

    Stored procedures B,C,D and E do not contain explicit transactions.
    Procedures B,C,D and E also are not called separately by any other part of
    the system. I believe that this was done just to break up units of work to
    make the code look nicer.

    I'm looking for opinions on:

    1. Do you think this is a bad practice? Why?
    2. Does this sort of practice cause poor query optimization by SQL Server?
    (i.e. Excessive locking?)

    Thanks.

    Dave


    Dave Guest

  2. #2

    Default Re: Opinions?


    "Dave Johnson" <1hotmail2.com> wrote in message
    news:phx.gbl... 
    stored 
    to 

    A better pattern would be.

    create procedure A
    as
    declare rc int
    begin transaction --possibly nested
    save transaction A --rollback to here in case of error

    while (some condition)
    begin

    exec rc = B (with some parameters)
    if rc <> 0 goto eh
    exec rc = C (with some parameters)
    if rc <> 0 goto eh
    exec rc = D (with some parameters)
    if rc <> 0 goto eh
    exec rc = E (with some parameters)
    if rc <> 0 goto eh

    end
    commit transaction --possibly nested
    return 0

    eh:
    rollback transaction A
    commit transaction --just to decrement tran_count
    return 1


    Each of B,C,D,E should follow the same pattern and return a non-zero code in
    case of error.

    In general you want the stored procedure to run in one big transaction
    instead of many small transactions. It's faster and safer. You know that
    if any part of it fails, then it all fails (ie it's atomic). It's faster
    because it's the commits, not the updates that are expensive.

    In SQLServer transactions can generate a lot of locks so you have to be
    careful if the stored procedure takes a long time to execute, but unless
    there is a specific reason to do so, you should run the procedure in one big
    transaction.

    David


    David Guest

  3. #3

    Default Re: Opinions?

    > create procedure A 
    to 

    I think it's quite good for moduling a logic into separate units and doesn't
    matter in all other aspects
     

    No. For the locking no too. Locking doesn't depend on nesting level - it
    depends only on transaction isolation level and operations made by exact DML
    commands. The only problem I can see here is that procedure cache uninfinite
    and if all the DB with hundreds of SP was structurized this way then
    procedure cache is permanently being full - such an effect can cause every
    run SPs recompilation. All other things that spoil you mind are not really
    matters. Never mind if you have one SP 1000 rows long or 5 SPs with 200 rows
    each - it doesn't really affect the performance as DMLs main cost is
    executing not parameters passing between procs


    Alex Guest

  4. #4

    Default Re: Opinions?

    > In general you want the stored procedure to run in one big transaction 

    Well you should know that really transaction is always the one for SQL
    Server (nested transactions are just fake emualtions in my opinion - you
    even can't rollback nested transaction if you didn't SAVE state of most
    upper one). So I just don't understand what you meant
     
    big 

    Agrees. I even would told that you should run one STATEMENT (not only SP) in
    one transaction


    Alex Guest

  5. #5

    Default Re: Opinions?


    "Alex Cieszinski" <com> wrote in message
    news:phx.gbl... [/ref]
    that [/ref]
    faster 
    >
    > Well you should know that really transaction is always the one for SQL
    > Server (nested transactions are just fake emualtions in my opinion - you
    > even can't rollback nested transaction if you didn't SAVE state of most
    > upper one). So I just don't understand what you meant
    >[/ref]

    Yes "Nested transactions" is a misnomer, but savepoints save the day.
    Look at the pattern.

    The SP is both atomic and indifferent to the current tran_count.
    It has no rollbacks except to savepoints inside stored procedures.
    It's atomic.
    It can be run from a connection that has already begun a transaction, or
    not.
    It will always leave tran_count the same as it was before the sp ran.


     
    in 

    Absolutely not. Transaction scope should be defined by the requirements for
    data consistency.
    If it takes 5 staments to move the database from one good state to the next,
    then those 5 statements should be in 1 transaction.



    David




    David Guest

  6. #6

    Default Re: Opinions?


    "David Browne" <davidbaxterbrowne no potted com> wrote in
    message news:phx.gbl...
     
    >
    > Yes "Nested transactions" is a misnomer, but savepoints save the day.
    > Look at the pattern.
    >
    > The SP is both atomic and indifferent to the current tran_count.
    > It has no rollbacks except to savepoints inside stored procedures.
    > It's atomic.
    > It can be run from a connection that has already begun a transaction, or
    > not.
    > It will always leave tran_count the same as it was before the sp ran.[/ref]

    Well, anyway all things are just to leave TRANCOUNT the same as it was
    before running this SP - it's not the question of isolation level or
    atomicity of SP really. All the things you should do with isolating your
    actions or atommizing your actions is on the shoulders of "main" (see origin
    mail) procedure. So there's no difference if this SP is implemented as one
    piece of code or divided into 5 parts. Don't you think so really ?
     
    > in 
    >
    > Absolutely not. Transaction scope should be defined by the requirements[/ref]
    for 
    next, 

    Really true. Nothing to say against this. All I wanted to say is that no
    more than one transaction per statement is required really. But if you
    should atommize 5 statements as the one you should execute them in common
    transaction - of course they are


    Alex Guest

Similar Threads

  1. I need some opinions.
    By Scott_Falkner@adobeforums.com in forum Adobe Indesign Windows
    Replies: 14
    Last Post: September 1st, 06:32 PM
  2. Opinions!!!
    By Carlos in forum Macromedia Flash Sitedesign
    Replies: 7
    Last Post: June 17th, 09:03 PM
  3. opinions please
    By GilesW in forum Macromedia Flash Sitedesign
    Replies: 5
    Last Post: February 23rd, 07:46 AM
  4. Your opinions
    By Fabrizio Fiorenzano in forum Photography
    Replies: 0
    Last Post: August 12th, 08:34 PM
  5. [PHP] just looking for some opinions...
    By Dan Joseph in forum PHP Development
    Replies: 5
    Last Post: July 24th, 06:19 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