Performance Enhancements - Reports - Update
Dateline: Roseville - June 30, 2008 - Stephanie Fury

 

As Escape's QA Coordinator, all new and enhanced software passes through my office. I either test myself, or enlist help from others here. Occasionally, a particular enhancement will really knock my socks off. And so it is for the latest version of our report Fiscal04. Susan Dickinson from our report team is using what she learned at her recent SQL Query/Tuning class as well as suggestions from Ventura COE's SQL guru.

Fiscal04 is one of our most flexible reports. At left is the report request form.

Like any account report, there is complete flexibility on the accounts that will be selected. And the sorting can be done on up to four account components.

Unlike most other reports though, this one provides for a flexible selection of six columns of your own choosing. Besides the account totals I selected here (and this could be done for any number of fiscal years), you can bring in amounts from budget models, and even have columns that do math such as column 5 - column 4.

But let's get back to the performance enhancement.

You can see that the request is for accounts in Fund 001 or 010, and any account whose object begins with 1, 2,3 or 4.

This resulted in a 27 page report. One page of which is shown below.

 

 

When testing reports, the first thing I do is to compare the "old" report to the "new" report. Does the output match as expected? In this case, it did. But, it's the speed of the report that really caught my eye (and my stopwatch). It was so much faster that I used the SQL Profiler tool to review the amount of data being read and written. How much faster, well, it went from almost 5 minutes down to less than 10 seconds. Here are the results:

SQL Activity

CPU Usage
(seconds)

Read
Operations

 Write
Operations		 Elapsed Time
Before
38.77
5,099,557
231
4 minutes 55 seconds
After
2.08
143,066
42
2.2 seconds

Not only is the report completing in 2% of the time it used to, it's also not locking any rows of data, where previously we had this problem situation, as described in a previous News entry by Bob Towery. Look at the read operations - 98% fewer read operations! It feels great to see our system improving so much every month. We know customers are looking for performance increases, and this report, being used a great deal, is a fantastic example. Way to go Susan!

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