This series of blogs is a direct response from observing application patterns that can cause poor database performance. Some of the patterns are may seem trivial or simple, but they still need to be covered because we see them many times. Most of the patterns have a simple interpretation of what causes the bad performance – too many round trips to the database.
Here are the patterns that will be covered in future blogs:
- Complex screens.
- “Academically correct” object modeling
- Row-at-a-time processing
- Using middle tier application servers for batch jobs
Fortunately, I have not seen an application with all of these together, although I’ve encountered some that have 3 of the patterns. Regardless of which ones you may suspect are in your application, I have had several customers thank me for one simple tip on how to discover if your application may have a problem. The recommendation is to add one measurement to each of your unit tests – for each user action, how many round trips to the database were needed. If you just add this to your tests, you may discover some unexpected numbers.
What is a user action, you might ask? Some are obvious to test, like pressing the submit button after filling in a screen of data. Others are navigating to another screen or dialog box. Form load is usually where we find the most round trips, whether it is a WinForms application or WebForm. For the batch jobs, the user action is when the job starts.
How many round trips is too many? One would be ideal, but sometimes hard to accomplish. Is 5 too many? Is 10 too many? That’s for you to decide based on your application and business requirements. However, I did have to tell one CIO that there was a certain spot in one application where there were 1,100 round trips to the database. That’s obviously too many!
Stay tuned for separate blog entries on each pattern… Kevin