Services

Designing for Performance

The required performance of an application should be clear at the very start of it's design. It may even be stated that performance is more important than functional details. I have never had a user complain about an application that showed to many decimals digits in a reported value. I have however allmost daily experience users who complain because the applications or queries take to long to finish (where "too long" is of course dependent on the person complaining).

Wrong design decissions can dramatically affect performance. At one customer a design team had made a nice Java application and created their datamodel themselves (without consulting the dba's). Result was a data model that was basically an abstract of the Object Oriented Java model they employed. The whole application was created, tested using 10 products and supplied for release. At pre release testing the application was tested for the first time using actual data loads that were present in the production environment. Where the application performed nicely with 10 products and customers, it perfromed exponentially bad on increase of the number of customers and products and attributes. As the production system contained over about 500k customers, about 40 products with each around 40 attributes the system took about 5 minutes to process just 1 products request. This bad design could also not be forced to perform by massively increasing the hardware so the whole application had to be redesigned.

Many of such examples have I seen. I have however also shown that good design can result in predictable performance. At one customer a datawarehouse was loaded from DB2 Mainframe using an Oracle Gateway. Whole loading an processing took about 60 hours which only allowed the datawarehouse to be refreshed one a week (weekend). I redesigned the whole solution into a standard ETL (Extract Transform Load) model using flat ebcdic files. Files were loaded when they were available, subsequently validated for datatype errors and process to the datawarehouse tables using PL/SQL packages. The design was a cascading waterfall setup where a process could start the moment it's precedessor (load or validate) was finished. End result was that the new design performed loading, validating and processing of all data within 3 hours. An increase of factor 20 allowing for daily refresh.

It should be clear that designed for performance will pay off allmost allways.