Home Forums SQL Server 2005 SQL Server 2005 Performance Tuning Explicit Transaction to improve performance? RE: Explicit Transaction to improve performance?

  • October 12, 2012 at 7:19 am

    #1548622

    Mahesh Bote (10/12/2012)

    [font="Verdana"]Hi All,

    I have one Stored Procedure which frequently gets executed. Though it is a straight forward Stored Procedure with one select statement and one delete statement, its causing major blocking. While investigation for the possible optimization, I could found only one area where I was thinking to introduce Explicit Transaction for Delete statement.

    Can anybody explain, introducing Explicit Transaction will be beneficial in terms of performance improvement. I browse through net, however could not get any solid explanation to justify myself on my above assumption.

    Please clarify.

    Thanks in advance,

    -- Mahesh

    [/font]

    I strongly suspect that adding an explicit transaction would change the blocking that's occurring into deadlocks and, thus, is not the answer.

    The only way to fix this type of thing is to make the query and the delete run fast and use as few resources as possible. The only way for us to help you do that is to read the article at the second link in my signature line and provide the things requested in that article about this problem.

    --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.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)