Tell me more ×
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.
up vote 1 down vote favorite

I have a table p with a column points. There are a couple of million of records in the table and i need to regularly calculate and update the points for each row. This process takes a couple of minutes and it locks the table. I need to be able to insert and update records in p in the meanwhile. One solution I was thinking of is to create a second table and add a foreign key to p and the points column there. If this table is locked for a couple of minutes that's fine. Are here disadvantages/consequences that I should keep in mind with this solution?

share|improve this question
Why is the table locked? Doesn't SQL Server do row-level locking? – a_horse_with_no_name Jan 13 '12 at 14:46
2  
@a_horse_with_no_name - Really? The reason for doing so is because each lock takes memory and you don't want to be using GB of RAM just to store the locks for one update when you could trade it in for a single table lock (so it trades off concurrency vs resource usage). There is a trace flag in SQL Server to disable lock escalation but not advisable to use it. – Martin Smith Jan 13 '12 at 15:49
1  
@MartinSmith: In Oracle or PostgreSQL a lock does not need any memory because the lock is "attached" to the row. There is no lock manager (or whatever SQL Server uses) – a_horse_with_no_name Jan 13 '12 at 15:51
1  
@a_horse_with_no_name - Ah OK. I know nothing about the internals of either of those but that's the rationale for the SQL Server behaviour. – Martin Smith Jan 13 '12 at 15:52
2  
show 5 more comments

2 Answers

up vote 3 down vote accepted

From what I remember about this database from your earlier questions, I'd suggest you look at these alternatives to your approach:

share|improve this answer
I like option 2 for preventing lock escalation. The hard part is finding just where SQL will escalate the lock to a full table lock. SInce it can depend on the scenario, testing is the best way to yield the batch size. – Anon246 Jan 13 '12 at 19:58
I am reading up on both options. – olle Jan 14 '12 at 20:05
I ended up going with the snapshot isolation level. The batched approach also worked but I got very random performance out of it and sometimes over 1000% worse. – olle Jan 16 '12 at 11:49
up vote 2 down vote

I'm not sure how the points are calculated and, like others might, I don't have any insight into your earlier questions, but why would you need to constantly calculate the points values and store them? If you can calculate them based on other data, then storing the calculation seems redundant.

Again, not sure how points are calculated exactly, but if they are simple aggregations (e.g. counts), would it be possible to calculate the points in an indexed view? Then they are always up to date and you aren't going back and re-calculating the points for the entire table, even if only a subset of rows have changed since the last time you performed your calculations. Of course you will have to test the impact of the indexed view maintenance.

share|improve this answer
I'll have to look into that, the calculation is quite simple it's comparing the value of columns in p to another table and then adding a number of points depending on whether they are <, = or >. The calculated points will not change over time for a row thats been calculated but there will be billions of records and 100's of millions that will be calculated in one go. – olle Jan 14 '12 at 20:02

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.