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?
- Anybody can ask a question
- Anybody can answer
- The best answers are voted up and rise to the top
From what I remember about this database from your earlier questions, I'd suggest you look at these alternatives to your 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.