single select vs multiple selects using limit and offset Working on a fairly high volume site. There are some tables example - the customers table which gets hit a lot. 

The problem 

We are writing a piece of code that selects a bunch of customers and sends out mailers etc (nothing mission critical). These selects often result in about 20000 records. We obviously do not want the regular hits to the customer table being blocked by these select statements. 

We went down the route of making these reads readUncommitted since the operations on the data are absolutely not mission critical. 

However, instead of making these massive selects if in code we make a select with a limit and an offset and keep looping would that cause less locks on the db. 

In other words - would 20 select statements each retrieving 1000 records be more desirable than 1 select statement retrieving 20000 records in terms of reducing locking etc. 

Any suggestions would be greatly appreciated.
Okay, first thing, I need to pick at this a little bit:


  We went down the route of making these reads readUncommitted since the
  operations on the data are absolutely not mission critical.


Using READ UNCOMMITTED/NOLOCK should only be considered when the accuracy of the results is not critical, because that's what the transaction isolation level affects. I understand that the operation is not mission-critical, but that should not be the driver behind selecting a less restrictive isolation level.


  In other words - would 20 select statements each retrieving 1000
  records be more desirable than 1 select statement retrieving 20000
  records in terms of reducing locking etc.


If you continue to use READ UNCOMMITTED, the locking overhead will be the same between the two methods (i.e., no locks will be taken). However, running multiple statements instead of one can affect other things, such as query compilation (which uses CPU), network chattiness, and the complexity of your application. In this case, I think the operation of sending a mailer would be relatively infrequent (I could be wrong), so the latter factor may be the most important to consider.

If you choose to switch back to a higher isolation level for accuracy reasons, that is an entirely different ball game in terms of locking.