Post reply

Query over slow connection, when are locks released?

  • January 21, 2014 at 6:45 am

    #284654

    Hi

    I have a quite complex Reporting View using other views.

    The output of this view is being sent over a v.slow network connection.

    It is causing other apps on that DB that write to the DB to slow down. It's is Locking rather than I/O issue.

    My question:

    Are the locks held for the time it takes to run the query only or also the time taken to return results to the client?

    i.e. is the client's connection speed relevant?

    If I were to change it to a SP, is it worth selecting everything First into a temp table and then returning the select from temp table. So only the temp table is locked while the results are sent.

    I think the cursor/recordset at the client end is readonly, not the dynamic sort (notify of changes)

    i.e.

    create proc mySP

    AS

    Select fieldlist INTO #t from (reproduce view's joins and logic)

    SELECT * FROM #t

    I think it should not make a difference, but wanted to check to clear up my understanding.

    P.S. I do NOT intend to use NOLOCK

  • January 21, 2014 at 7:04 am

    #1681877

    Have you considered one of the row-versioning based isolation levels?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 21, 2014 at 7:26 am

    #1681898

    Thanks for relying Gila

    I don't know of this concept.

    However it's a 3rd party DB I cannot make changes to it.

    As a simplified model I THOUGHT Sql executed the locking as it sees fit or instructed to do and places the results into temp store (memory/tempdb?). It then releases locks and starts sending the data.

  • January 21, 2014 at 7:44 am

    #1681917

    Close enough. Shared locks are released no later than the end of the query (assuming default isolation level), exclusive locks are held til the end of the transaction

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply