Slow MySQL execution over Network under Java

If an application and the connected database is developed and tested on one single machine, code optimization and design of queries may suffer due to instant access time and nearly no transfer time between database and application. However, if you put the Application and Database on different machines such design flaws can lead to horrible processing times. There are several fields that may cause problems in that case. One problem is transferring of many records from or to database. Another problem is execution of hundreds of single independent SQL statements in a very short time, where the application always has to wait for response of database.

Taggings:

Submitted by Stefan Bacher on Sun, 11/16/2008 - 18:11
1 answer

Speed up communication between Application and MySQL Database

There are some ways to improve performance of executing SQL Statements on a distant Database Server.

In case of SELECT queries it can be useful to analyze the queries and determine if all data which has been retrieved is really necessary. Size of the result can be drastically decreased, if you just retrieve specific fields instead of using SELECT * FROM xy WHERE ...
There is also the possiblity that you can put logic from application into a query, so only one query has to be sent to Database Server, instead of executing several queries and then process the result on Application Server.

In case of many similar INSERT or UPDATE statements several optimization processes can be done.

First of all it is useful to use PreparedStatements instead of normal statements as this takes load from Database Server as statements don't have to be generated new on every request.

In an second attempt it is useful to gather all statements into a Batch Job. And when you are finished with generating all statements you execute the Batch. So all data is sent together to the MySQL Database.

If you have several batches which have to be processed and should not return the result immediatly it is very useful to disable "AutoCommit". So you can execute all batches and commands and when you are finished sending all comands to server, you can execute COMMIT and everything is processed at once on database server. (Dont forget to enable "AutoCommit" afterwards again)

For my Internet Application these steps have increased the processing time of a large data update from about 3-4 Minutes down to 3 seconds.

Submitted by Stefan Bacher on Sun, 11/16/2008 - 18:27