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?
From what I remember about this database from your earlier questions, I'd suggest you look at these alternatives to your approach:
- Consider RCSI or Snapshot isolation. This will prevent your select queries being blocked during the update. Be mindful of the increased usage of tempdb this can generate but IIRC the nature of your database/data should mean this is small.
- Updating the points record in batches, rather than one almighty transaction for the entire table. See How to Use Batch Size to Speed Mass Updates, Inserts and Deletes for an old (but still relevant) approach.
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.