Re: Insert speed (JDBC and MySQL)

Mark Matthews wrote:

John wrote:

Hi,

I am writing a program that needs to typically carry out around 100,000
INSERTs to a mysql database. I would normally load the data using a text
file, but in this case, there is the occasional query required (evenly
spread through, and dependent on, the INSERTs).

I had the program connecting to a remote database and was achieving
around 47ms per INSERT. I thought that connecting to the database
locally would improve the speed, but it is unchanged at around 47ms.

This compares (very) unfavourably with the 0.3 ms that can be achieved
by piping a text file of sql into the command line tool.

Does anyone know how to improve the performance of these INSERTs?

Similarly, what speeds can be achieved for INSERTS using JDBC? (using
one sensible machine only).

Thanks,

John 


John,

How are your inserts crafted? Without knowing that, you're comparing
apples and oranges.

I can load 100 thousand rows using JDBC in just under a second over
100mbit ethernet via batched inserts (i.e. of the form INSERT INTO foo
VALUES (...), (...), (...)), so you've definitely got something not
efficient with the way you're doing it.

-Mark 

Hi Mark,

I am using one Connection object.

For each INSERT, I obtain a statement using createStatement(), then call executeUpdate("queryString") on that statement. This takes the 47ms.

90% of the INSERT statements would be of the form "INSERT INTO table_name VALUES(data, data)".

I am quite reluctant to combine multiple INSERTS into one batch, because if there is an error with an update I would like the program to detect this, log it, and take corrective action.

If there is a way of improving performance without batching the inserts or combining multiple queries into one statement that would be ideal. (I'm hoping that I am doing something simple inefficiently and there is some easy way of speeding it up.)

Thanks,

John
.

Relevant Pages

  • Re: Insert speed (JDBC and MySQL)
    ... INSERTs to a mysql database. ... I would normally load the data using a text ... round-trip time for each insert, which is what is killing you now ...
    (comp.lang.java.databases)
  • Insert speed (JDBC and MySQL)
    ... I am writing a program that needs to typically carry out around 100,000 INSERTs to a mysql database. ... I would normally load the data using a text file, but in this case, there is the occasional query required. ... I had the program connecting to a remote database and was achieving around 47ms per INSERT. ... what speeds can be achieved for INSERTS using JDBC? ...
    (comp.lang.java.databases)
  • Re: Permission to open database
    ... John I got it to work with the code below I got from an example I found. ... Dim rst As DAO.Recordset ... "Please contact the Database Adminstrator for assistance.", ... "WelcomeForm" ...
    (microsoft.public.access.gettingstarted)
  • Re: Exporting Info To Word File
    ... degree of automation) to get individual records from an existing databse when ... need from my database. ... "John Nurick" wrote: ... >>> document from the template, and place the information in the bookmarks ...
    (microsoft.public.access.externaldata)
  • Re: Communication Log
    ... "John Marshall, MVP" wrote: ... Visio MVP ... May I send you copy of the database that I made? ... a communication log in the database so that it will store the Date, ...
    (microsoft.public.access.tablesdbdesign)