OK, sure, if you write a query that takes 10 minutes, then your performance is going to be terrible. But of all the systems I have seen, usually there is somebody around who can both detect a long running query and fix it.
What tends to kill systems is the number of round trips between the application server and the database. How bad can it be? REALLY BAD! The worst I have ever seen was 60,000 round trips to do a small batch routine in a UI. Someone from the US military came up to me after a presentation and said that they had seen a single UI operation that required over 200,000 round trips to the server.
How can such a thing be? Two words: “getters” and “setters.” If your UI developer thinks that context switches between the UI code the database are costless and that the pipeline between the database and the application server has infinite bandwidth and speed, then doing a round trip for each attribute can seem like a pretty smart idea.
Of course, context switches are very expensive things. The bandwidth between the application server and the database is not infinite. Therefore, lots of round trips ends up being the number 1 killer of system performance.
The most egregious example of this was a system being built for a very famous organization involved in loans. They placed getters and setters on each attribute in the month-end batch routine. As a result, it took about one minute to do the month end processing for each loan. Given that about 14,000,000 loans needed to be processed, this meant that the month-end routine would take over 26 years to complete.
How can you fix this problem? There are a few different alternatives:
- Use a thick database approach and move the logic next to the data in order to take advantage of the power of the DBMS.
- Be very careful to minimize the number of round trips and filter the rows on the database side. Don’t retrieve 100,000 rows so that you can filter through them and find a single record. No UI operation should take more than a handful of round trips.
- Move all of the data into the middle tier or into memory. This is really the same idea as option number 1, namely put the data and the code in one place and avoid the context switches.
Personally, I am a fan of option number 1. I will post about thick database tomorrow.
Leave a Reply