Phase II - Performance Enhancements - SQL 2005

Dateline: Roseville - May 28, 2008

We are making an adjustment to our four phase approach to performance enhancements. We are inserting another phase, and making this phase II. From the info we gathered recently by sending Susan Dickinson (Crystal Report Developer) and Jennifer Kenney (Business Analyst) to SQL Training, and the information we are getting from Ventura COE's DBA, we find that having customers on SQL 2000 limits us from using new functionality that would result in performance increases.

First of all, one would expect Microsoft to improve performance overall given five years of development between the releases. Moreover there are new commands that can be used to increase performance. As long as we have any customer remaining on SQL 2000, we cannot use these improvements. Once all our customers have moved to SQL 2005, we can begin using the new commands which will result in performance increases. FYI our application was tested and approved by Veritest, for SQL 2005, a qualification requirement of being a Microsoft ISV Partner.

We view performance increases we can make available solely by making changes to the database to be among the most productive and efficient from a development point of view.

 

Here's an example of one. There is a step in payroll processing where all of the data for the printed checks/advices is created, and all of the YTD/FYTD figures by employee are updated. We have a timeout set such that any SQL process that takes more than 20 minutes creates an error situation. In April, the largest live Ventura district reached the timeout during this step. Time to dive in and see what we can do. By doing performance tweaks, we reduced this from 21 minutes to 7 minutes. Cut the time required by 2/3! If you spread this across all the districts of a county, you can see a pretty significant savings in time. This will be a continual process for some time - refactoring our software based on real world performance results. This was CR 6949 for those of you that follow CR numbers.

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