2 Replies Latest reply on Jul 11, 2006 8:12 AM by ScottAtHelloMetro

    Optimizing CF using 'NOLOCK' in queries

      I've been considering using the 'nolock' hint in most of my sql queries with sql server 2005 to speed up my ColdFusion app. But I have some questions/concerns however:

      1. I see on Microsoft's site that 'nolock' is deprecated and will not be available in some future release of SQL Server (doesn't say which one) and it will need to be removed from all code at some point. I don't want an application full of code that will break in the future should an upgrade take place without people being aware of the potential problem.

      2. Is there any good reason not to just use the command SET READ UNCOMMITTED ON before each transaction? Shouldn't this provide the same effective results but not have the 'future deprication' issue?

      3. Is there a way to reset the default isolation level for a given database to READ UNCOMMITTED so I do not have to specify it in every connection?

      Does anyone have any advice or experience regarding this? My objective is to speed up the delivery of database-driven content but I don't want to cause problems down the road. Also, I'm not concerned with data integrity (dirty reads are ok).