Chatty vs. Chunky

Most of my client-server experience has led me to believe that a chatty application will perform better than a chunky one. A chatty application will pull, from the server, the smallest amount of data needed to perform a task. If more data is needed for another task another small dataset is retrieved. It is an only pull what you need system.

While working at GiftRAP, Beth Massi, tried to convince me that a chunky interface is far more scalable and much better when writing distributed applications. Since Beth knows far more about distributed applications than I do I said OK but didn't really believe her. After all, I was always taught that smaller is better when it comes to getting data from your database server. Well, I now have proof that Beth may have been right, but don't tell her. :-)

I am working on a project right now that is experiencing some bottlenecks on our Oracle Server. Some of the long running processes are really blocking the other 250 users of the system. So, I have decided that I need to do what Calvin would do and remove the slow parts.

So, here is this process that scans through a FoxPro table and retrieves some data from two different Oracle tables based on the FoxPro key field. The first mistake the original developer made was to not create one query to get the data from both Oracle tables at once since they are related. No big deal, this is an honest mistake for someone not well versed in Client-Server techniques. So, I fixed that but the process was still very slow.

As it turns out, there were about 140,000 records in the one Oracle table and just over 3000 that matched a generic query needed for this process. The Oracle cost of the generic query was like 6703 and the cost of the query specific to the Fox data was 474. The problem is that the query with th 474 cost was executed in a loop more than 15 times. Therefore, it was significantly more efficient to pull all 3000 records down (a small # of fields) and let FoxPro filter the more specific data as it was needed.

In general the process would take anywhere from 15 minutes to 1 hour to run on a daily basis and consume enough server resources to slow down other processes. Now it runs in under a minute and is barely a bleep on the server's performance monitor.

Don't get caught in old habits when tweaking performance. Chunky can be better than Chatty even in non-distributed (or plain 2 tier) applications.

Note: Mike and I were discussing this post the other day and he indicated that the point of the blog might not be so evident. So basically here is the point in a nutshell: Pulling down 10 different records 5 times is not always faster than pulling down all 50 records from the start.