Database Application Performance Architecture
Posted on 2013-06-26
This is a fairly open question unfortunately, but I will award points to all helpful responses.
I manage a fairly complex web application which is running into performance issues. I am looking for ideas and/or resources (e.g. books/articles on the subject) that will help me resolve this. Specifically I am looking for architectural advice, not performance tuning advice.
The system is a business management system for an insurance company. When an insurance policy is added to the system the premiums are automatically calculated for the policy, and if certain things change about the policy the premiums are re-calculated.
This premium calculation is very complex, and consequently can be slow. However of more concern is that deadlocks are being frequently caused when multiple premium updates are being calculated at the same time.
NOTE: I have spent a lot of time performance tuning this area and adding indexes etc and am not looking for help in the detail of this. Rather I am looking for the higher level architecture that will best handle this.
Currently the calculation is contained within a single stored procedure in the database. This SP is called from various triggers which catch changes in conditions which impact the premiums. (No comments about triggers please).
Thoughts I have had are:
1) I could move the calling of this SP into code instead of from triggers, but I don't think that will affect it as its the SP itself where the deadlock is happening.
2) I could build the SP as a CLR SP - but I suspect that will run slower as it will still have to carry out all the same queries but now one step removed from the data. I also suspect this won't help the deadlocks (could be wrong though).
3) I wonder about building a queuing mechanism which only allows this SP to be called sequentially. It would certainly remove the deadlocks but would make the performance even slower. And would make managing the UI more complex as it would have to wait until its SP has been handled.
There must be many large, scalable apps out there which have solved issues similar to this, so I am wondering how (again at an architectural level) they go about this.
The fact that the premium calculation is so complex, and requires lots of different data from the database makes me think that a SP is the best place for it - as opposed to some code which pulls in the data in a granular fashion and would therefore be slower.
The fact that there are so many different data changes which require the premiums to be updated makes me think that a trigger is a good place to handle that.
But maybe thats not the case.
As I said any ideas/resources appreciated, but remember I'm looking at the architectural as opposed to the detailed implementation level.