Home Forums SQL Server 2008 T-SQL (SS2K8) Is there a way to improve this SELECT statement for performance? RE: Is there a way to improve this SELECT statement for performance?

  • ScottPletcher (8/30/2013)


    MyDoggieJessie (8/30/2013)


    It may "bypass" the locking of the records it's ready but it does not improve performance or reduce any overhead one bit.

    LOL. That's self-contradictory -- if NOLOCK avoids locking that the statement would otherwise have to do, it de facto improves performance.

    I'm not saying NOLOCK should be used indiscriminately. I'm saying it does have proper uses because it DOES reduce overhead.

    Just because it's often overused and/or misused is no reason to falsify what it does or does not do.

    NOLOCK can be useful, for example, for code lookup tables, such as state code lookups. A state hasn't been added since 1959, I think I'll risk it :-).

    Also, for example, if/when I'm forced to do read(s) from very busy production table(s) and I want to insure that I don't interfere with production processing.

    So you['re saying that if you compare the execution plan of two queries, one with tables having WITH(NOLOCK) specified and one that does not, you would see a more optimal plan with the latter?

    I agree "time" is saved by bypassing potentially locked rows, but there's no difference to the performance of the query any more than adding a WAITFOR DELAY in the middle of your stored-procedure/TSQL

    ______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience