Databases Reference
In-Depth Information
Here are some questions to ask:
•
Is the driver configured with optimal database protocol packet sizes? This is
key when transferring this much data.
•
Is the application optimized for bulk load? Does the application use an array
of parameters? Does the application use prepared statements?
Thinking Through the Issue
Let's think about what we know:
•
Auto-commit is turned off in the application, which is the correct configura-
tion for this case. Because of the significant amount of disk I/O required to
commit every operation on the database server and because of the extra net-
work round trips that occur between the driver and the database, in most
cases you will want to turn off auto-commit mode in your application. By
doing this, your application can control when the database work is commit-
ted, which provides dramatically better response time.
•
The application uses an array of parameters, which is optimal. When using
an array of parameters, it's a good idea to experiment with the size of the
arrays to find the maximum value that provides the best performance. In
this case, the parameter array value of 1,000 rows per execute gets the best
performance.
•
The application efficiently uses prepared statements.
•
The data being loaded is numeric, and the application is reading all the data
into memory as character strings.
•
A poorly configured database driver could cause performance issues. For
example, the packet's size could be configured to a small value such as 16KB.
For this case, let's assume the database driver is configured correctly.
The Resolution
The performance issue revolves around the fact that the application reads in the
numeric data as character strings. Depending on the implementation of the
database driver, either the driver must convert the character data to the appropri-
ate format to be inserted into the database, or the driver must send the character
data to the database and the database system must do the conversions. Either
