Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

3 Answers

up vote 2 down vote accepted

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.

share|improve this answer
    
Nope. There are other transactions that happen on the customers table from which the 20k records are selected. I do not want this select to block these transactions as they are more important. –  user529265 Aug 3 '12 at 12:36
    
we are on SQL Azure. The isolation level Read Committed snapshot seems to be a database level setting. We have several transactions running as Serializable. Would this setting affect those transactions? –  user529265 Aug 3 '12 at 13:17
1  
According to TechNet Read Committed Snapshot and Snapshot isolation levels are enabled by default in Azure; query sys.databases to confirm. Both those isolation levels allow selects to read data even if records are locked by another transaction (even Serializable ones). Your reader will see an older version of the data. –  ligos Aug 3 '12 at 23:57
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.