Thursday, July 17, 2008

Why Keep It Simple and Fast?

In the past I have seen a lot of applications which do not perform very well. The root cause of these performance issues are related to next factors:
· Functionality
· Number of records to retrieve from the database
· Database model


Functionality. Lot applications are started as simple applications. Later on a lot of functionality is added which resulted in a very complex application with a lot of settings. By adding new settings, the matrix of all different combinations is getting more complex. The challenge, for the functional designer, is to design an application which is still easy to understand and easy to use. The more options you have, the bigger the challenge. At the end this is a mission impossible. The technical designer has the same challenge. How can I develop an application which supports all options which is secure and has an acceptable performance? The more options you have, the more complex your queries will be. In most situations, the performance of a complex query is not as good as the performance of a simple query.

Number of records to retrieve. In a database with a small amount of records, the performance is mostly acceptable. The impact of a table scan on a small table is not so big. However after a while, the number of records in your table is growing. A table scan is not allowed anymore because the impact of a table scan on performance is too big. Complex queries on tables with a lot of records will results in complex query plans. Generating the query plan can take some time but even more the execution of this query plan.

Database model. During the technical design of a new application, a database model is made which supports the functionality of that application in the best way. Later on, new functionality is added and build on the current database model. This is not always optimal. The best way to support this new functionality is another database model. However I can imagine why a redesign is not always possible. Redesign of your database model requires you to rewrite the current code and to convert the data to the new database model. This can have a huge impact for your customers with a lot of records in the tables you need to convert.

The challenge is and will always be: Find the perfect balance between functionality and performance in a way that the user can use your application without any help. Great functionality which does not perform will never be used.