single select vs multiple selects using limit and offset

Question

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.

Answer 1

20k records is not that many. Are you SELECTing other data from other tables as part of your mailout which is slowing things down? Are you keeping your transaction open longer than you need?

Otherwise, can you use one of the opimistic concurrency isolation levels: Snapshot or Read Committed Snapshot? They should allow you to read without blocking.

Answer 2

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.

Answer 3

You should go for proper indexing of records as in SQL server 20000 records is not much more ,you can easily pick in a single select statement. If possible in single statement then why should go for multiple.

So better solution is that if records are not retrieved fast then create the indexes as per the need to optimize your select statement.