[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now


Database Application Performance Architecture

Posted on 2013-06-26
Medium Priority
Last Modified: 2013-07-02

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.


Question by:Dale Burrell
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Assisted Solution

by:Vijay Pratap Singh
Vijay Pratap Singh earned 300 total points
ID: 39280370
You need to use SSD as Storage that will make the database access very fast and try to use cache between front end and Database so will query access very fast.
LVL 49

Accepted Solution

PortletPaul earned 900 total points
ID: 39280387
From this architects perspective, I would rather see this type of complex business logic separated from the data. In other words; gather the minimum data needed to perform the calculation(s) from the db but actually perform the calculation in a business logic layer.

This of course is easy to write as a simple sentence, and I know almost nothing of the details here, but many highly scalable J2EE apps I'm familiar with do it exactly this way. The db may be queried numerous times during such a process, but written to much less frequently.

SPs are preferred over triggers in this approach (in my experience) but of course both are used.

This overall approach however reduces the role of the dbms to data persistence, and many features of the dbms are not used (or even avoided), the business logic layer can even be responsible for referential integrity as an example of this.
LVL 23

Assisted Solution

Racim BOUDJAKDJI earned 300 total points
ID: 39280494
From an architecture standpoint, cince Performance are primarily the result of a mismatch between physics resources and applicative consumption of resources you need to consider the following:
> Does your IO subsystem provide sufficient throughput to meet the demands of the application.  Segregate your physical disks by usage (logs, data, tempdb).  
> Does your IO subsystem provide sufficient IOPS to meet the concurrency demands of the application in regard to other applications hosted on the same platform.
> Does you box provide sufficient CPU to meet the need for processing and threading by the application
> Do you have sufficient RAM to allow SQL Server to provide multiuser effective caching without latching
LVL 21

Author Comment

by:Dale Burrell
ID: 39288539
Thanks guys... all helpful... I'll give it a few more days to see if anyone else has anything to add.
LVL 21

Author Closing Comment

by:Dale Burrell
ID: 39294693
Thanks guys

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

650 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question