This is a showcase review for our sponsors at CodeProject. These reviews are intended to provide you with information on products and services that we consider useful and of value to developers.
Introduction
Business Objectives:
Identify bottlenecks within an online pension plan system to increase response time.
Technical Challenges:
PlanGen�s web application responds to hundreds of requests for retirement plan scenarios each day. The heavy traffic combined with complex calculations slowed online transactions significantly.
Solution:
Using Red Gate Software�s ANTS Profiler to identify slow processes in the application then adjusting associated code to reduce the number of database queries being initiated.
Results:
PlanGen decreased response time from 5.4 seconds to 0.8 seconds � nearly seven times faster � and reduced the amount of CPU resources required. |
Surveys say that users required to wait more than five seconds for an online request become frustrated, sometimes enough to abandon the website. With a typical response time above five seconds, software company PlanGen knew it had to speed up the code for its online pension plan system.
Database Access Overload
PlanGen�s IllustrationNET412(i) is a database-driven proposal generator and storage system written in C# using Microsoft Visual Studio .NET. The web application allows insurance agents to display retirement plan scenarios for their clients.
IllustrationNET412(i) takes company databases of employees and creates proposals using actuarial algorithms tailored to the unique product-rate tables of insurance and annuity carriers. The proposals analyze and project retirement scenarios for all employees of a company until they retire. This requires complex calculations and databases that can change tens of thousands of times during an employee�s tenure.
�The web server calls the database server to read and write insurance and annuity rates for specific employees at various ages, calculate values of life insurance and retirement account values, and generate illustration documents,� says Eric Edelstein, senior developer and managing partner at PlanGen.
Specifically, the web-based front end makes calls to the business logic layer in C# modules on the web server. The modules collect the required data from procedures stored in SQL Server. The data is then returned back to the business logic layer for processing and final assembly into customized datasets, which are written into reporting/storage tables in the SQL database. The final calculation datasets are retrieved from the database and displayed on the web server as .aspx pages or PDF file outputs to the client browser.
That process, compounded by the hundreds of people adding lists of employees to the system and modifying proposals to produce different retirement cost/benefit scenarios, compromised the performance of the Dell Pentium servers that housed the application. The process of generating a proposal required approximately 5.4 seconds per employee.
�As far as we could determine, the database and web server were optimized, but the process was just too slow for production of multi-user systems,� Edelstein says.
Streamlining Database Queries
PlanGen had always embedded code as debugging statements displayed on screen to see where bottlenecks occurred in its system. That method, however, was not helping developers identify the problem. PlanGen decided to purchase Red Gate Software�s ANTS Profiler, which identifies slow lines of code in .NET applications and pinpoints problems, allowing developers to modify routines or modules that aren�t performing as originally intended.
In less than 10 minutes, ANTS Profiler revealed that the largest chunk of time was being spent on the thousands of calls made to business logic loops for retrieving data from SQL-stored procedures.
�Database call overhead time was the culprit,� Edelstein says. �It was taking approximately four of the 5.4 seconds required for each employee�s process duration.�
Instead of forcing the web application business logic modules to continually call the database for lookup values during a plan calculation, PlanGen loaded the lookup tables into memory arrays the first time the web application received a request.
�Once these arrays were initialized and stored into memory on the web server processing the business logic, we could eliminate individual database queries,� Edelstein says.
More Capacity, Less Time
After optimizing the slowest routines discovered by ANTS Profiler, PlanGen�s system decreased the response time for each employee from 5.4 seconds to 0.8 seconds � nearly seven times faster.
�Not only is our application faster for the users, we can also accommodate more requests per server, utilize cheaper hardware, and retain overhead processing capacity because we are no longer consuming excess CPU resources,� Edelstein says.
PlanGen now runs ANTS Profiler on all the code it writes. �Based on what I see in ANTS, I write more efficient application code and rethink multi-server, multi-tiered system architecture realistically,� Edelstein says. �Even if there�s no perceived resource or speed problem, code can be optimized, costs can be lowered, and performance can be improved.�
Further information
Find out what other users of ANTS Profiler have to say about the product, and to see how well ANTS Profiler will work with your applications, by downloading a free, fully-functional trial.
