Performance Enhancements Update - Reporting / Database Performance Tuning
Dateline: Roseville - June 6, 2008 - Bob Towery

The cause of many "slowdowns" experienced at our larger implementations has been determined - it is database contention. The way we were accessing data for a variety of purposes, especially reports, was "locking" data for long periods of time and making other processes wait. Users just know they aren't getting the screens they requested, they do not know why.

So we have employed a series of SQL techniques to both speed up reporting and either significantly reduce, or eliminate, the period of time where any data are locked. One of the main things we are doing is moving away from large "views" that gather all the data into huge data structures. We are finding it is more efficient to do several smaller operations, often involving "temp tables."

So let's look at some real world results. I did some testing myself, on my own laptop, which is a very controlled environment. It operates as the server and the client; it's a scrambled customer database. We use this laptop and configuration for demos. Now obviously this isn't a suitable hardware setup for a production system! But it guarantees consistency for timing tests, and the relationship between the before and after performance numbers should scale appropriately at our customers.

 

Report
Pre-Tuning
Post-Tuning
Pages
 Comment
Fiscal05, Account Summary
:25
:08
40
 General fund, sort/total by fund/resource
Leave01, Leave Usages
2:05
:45
114
 2081 (possible) Employees, usage from 7/1/07-6/30/08
Pay29, Addon Pay Rates
1:45
:30
136
 Same as above, active on 6/1/08

 

You can see that we have sped up each report by a factor of 3 or 4. We didn't particularly think the previous times were "bad," but certainly the new times are outstanding. And more importantly, we aren't locking data for significant periods of time anymore. You can't tell how long or even if the data are being locked by looking at those times, but as said previously, our goal is minimal or none.

These times are the total time as experienced by the user. So that includes requesting the form, getting the data from SQL, the Crystal processing, and then rendering on the client. Amazing stuff.

Escape Online 5 reports go to Warp Speed!

We will continue to tune all reports for both increased performance and reduced/eliminated data locking.

 

So these are real world results, but not on a real world system. In order to gauge how our improvements are panning out in the real world, we checked some statistics on Ventura's system after having installed the updated Fiscal05.

Over 126 executions, the average time to for the SQL data access was 7 seconds.

This is across many districts of all sizes. It's a great result! Thank you to our customers that are assisting us in this process.

 

Comments? Send us an email and let us know what you think.