Posted by Hugo Kornelis on 09/18/05 23:18

On 18 Sep 2005 08:28:14 -0700, laurenq uantrell wrote:

>So which should result in a faster scan of the table with fewer
>possibilty of locking in a situation where:
>
>I have a table dbo.myTableName to which hundreds of users are
>UPDATE-ing or INSERT-ing 24/7. They are also running SELECT queries
>against that table 24/7.
>I am tring to remove the slowdown caused by rows that might be being
>written to when users run a query that looks like: SELECT myID, myRow1
>FROM dbo.myTableName WITH (READPAST) <OR> WITH (NOLOCK) WHERE StartDate
>= @DateParam
>
>?

Hi Lauren,

Here's an answer you probably don't want to hear :-)

Try to use neither. In both cases, you'll return information that is
besides the truth. In the case of (READPAST), rows will be missing in
your result set that should be included. In the case of (NOLOCK), you'll
return data that is currently being changed, but might still be rolled
back (e.g. because it violates a business rule).

You should not be trying to get "a faster scan of the table" - you
should be trying to eliminate table scans at all. Especially on a table
that is under heavy use by hundreds of users. Making sure that all
inserts, updates and selects can use appropriate indexes will go a long
way toward preventing table scans. This will also mean that you'll spend
far less time waiting for a lock to be released on a row you didn;t want
to see after all!!

If you're still facing blocking issues after this, you might want to
consider duplicating the table: one "live" table for all the inserts and
updates, and a "reporting" copy for all the selects. Set up a routine
that will periodically (e.g. every 5 minutes, or whatever time delay is
acceptable in your situation) copy over all changes from the "live"
table to the "reporting" copy.


Now to your original question:
>So which should result in a faster scan of the table with fewer
>possibilty of locking in a situation where:
(snip)
>WITH (READPAST) <OR> WITH (NOLOCK)

The only way to find out is to test them both. If I were forced to
guess, I'd say that NOLCOK might be faster as it doesn't check for
existing locks, nor take any locks, whereas NOLOCK still checks for
locks and takes a lock if the row is not currently locked.

But as I said - that's just a guess.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[Back to original message]