Caching and inserting big number of records

Gangabass has asked for the wisdom of the Perl Monks concerning the following question:

Hi, Monks

I need the way to cache records before inserting them into DB (MySQL). This is because my program get information considerable faster then MySQL inserts them into DB. So i think there is a way that i could cache data and in the end insert all data in one operation

I think about MySQL

LOAD DATA INFILE
[download]

but maybe there is another better way?

Thanks for suggestions.

Comment on Caching and inserting big number of records Download Code

Replies are listed 'Best First'.
Re: Caching and inserting big number of records by clinton (Priest) on Aug 21, 2007 at 07:49 UTC
Have a look at MySQL's page on the speed of insert statements. Specifically, using one insert statement with multiple value lines, and using the INSERT DELAYED syntax may be of use to you. That said, if you can collect all of your data, then do a bulk insert, then using LOAD DATA INFILE with indexes turned off is supposed to be 20 times faster than using `INSERT` statements. This does require you to format your data in CSV, for which you can use Text::CSV_XS Clint	[reply] [d/l]
Re: Caching and inserting big number of records by graff (Chancellor) on Aug 21, 2007 at 04:49 UTC
`LOAD DATA INFILE` is the best way to go. You just have to work out the details of saving the data to a suitable tab-delimited file.	[reply] [d/l]
Re: Caching and inserting big number of records by dreel (Sexton) on Aug 21, 2007 at 05:29 UTC
Do you want to insert records by big chunks? I think the simplest way it's generate RAW SQL with data, somthing like this: `INSERT INTO employees VALUES ('Hicks','Freddy','crew'), ('Harris','Joel','crew'), ('Davis','Julie','manager');` [download] You just add new values into query and at the end you simply execute it.	[reply] [d/l]
Re^2: Caching and inserting big number of records by dsheroh (Monsignor) on Aug 21, 2007 at 14:59 UTC
Is there a clean way to do this with placeholders (i.e., without having 50 "(?, ?, ?)," lines) or does inserting multiple VALUES lines necessarily condemn you to putting the data directly into the SQL statement?	[reply]
Re^3: Caching and inserting big number of records by clinton (Priest) on Aug 21, 2007 at 15:16 UTC
No - you have to put in the `?` unfortunately. You could minimise it to: `$sql = 'insert into my_table (x,y,z) values ' . join (',', ('values (' . join(',',('?') x $rows) . ')') x $colum +ns )` [download]	[reply] [d/l] [select]