bulk inserts w/ prepared statements too slow...
Posted by: P R
Date: January 26, 2011 05:34AM

I have a C application where I need to perform bulk inserts of about 15million rows/approx. 3-5GB of data, and I can no longer use LOAD DATA INFILE.
Previously, I used an external data application to create a large csv file and then 'LOAD DATA INFILE' to load the csv into an empty table with no indexes. Total processing time was about 8 minutes using 'LOAD DATA INFILE'.

Now, Ive converted the application's code to use a prepared statement via the C API. The bulk insert performance is much worse, it takes more than an hour using a prepared statement and running 15+million inserts. Ive made sure that indexes were disabled, and relevant server variables have been tuned.
Im fully aware that 'LOAD DATA INFILE' is the fastest way to go, but unfortunately the intermediate csv file method is no longer acceptable & I'll have to go directlyfrom the external data processing application to mysql.

My question:
How can I achieve the highest possible performance for bulk inserts ASIDE from using 'LOAD DATA INFILE'???
Im willing to use whatever programmatic means possible...
Would it be possible to just programmatically create the table's .myd/.frm files directly from my data processing application & bypass the mysql api altogether?
is there some other lower-level way to bulk load data into the server, without all of the checks & overhead of using the higher-level insert methods from the C api?



Edited 2 time(s). Last edit at 01/26/2011 05:37AM by P R.

Options: ReplyQuote


Subject
Views
Written By
Posted
bulk inserts w/ prepared statements too slow...
1531
P R
January 26, 2011 05:34AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.