Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
nic
Posting Yak Master
209 Posts |
 Posted - 2005-08-01 : 12:37:07
|
| Hi,I have two scenarios and I'm curious if not locking the table would increase performance.1) We have mulitiple instances where we are retrieving one set of data by it's primary key. Only one record would be returned (the statement may join mulitple tables). Dirty reads are acceptable since in practice only one person would be retrieving an individual record at a time.i.e. select * from myTable where myKey = 1002) We also have a number of statements that return a set of records. These are primarly used in "search" pages. On these pages, multiple records may be returned. Dirty reads are also acceptable since although multiple records are returned, each record for the most part is updated by one individual at a time.i.e. select * from myTable where startDate >= '1/1/2000' and endDate <='2/2/2000'Would setting the noLock on a table be helpful in queries only returning one record? I see benifit in setting noLock on option two (multiple records) but I'm curious if it would be helpful for option one?Thanks,NicNic |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2005-08-01 : 14:25:40
|
| IMHO, both scenarios would benefit from nolock being used in scenario 2. Of course nolock anywhere can only help performance. But with a single record being selected by primary key value, I wouldn't expect to see much of a boost (unless the row being selected is locked by another process.Be One with the OptimizerTG |
 |
|
|
|
|
|
NOLOCK scenarios