Have Geek Brain

How do I execute lengthy batch processes in PHP?

I have a batch process where I need to update a db table, about 100000-500000 rows, from a downloaded CVS file. It usually takes 20-30 minutes, sometimes longer.

What's the best way? any good practice? Any suggestion would be appreciated

Thanks.

0


a source to share


5 answers


Does it take 30 minutes to import 500,000 rows from CSV?

Do you feel like letting MySQL do the hard work? There is LOAD DATA INFILE

one that supports working with CSV files:

LOAD DATA INFILE 'data.txt' INTO TABLE tbl_name
  FIELDS TERMINATED BY ',' ENCLOSED BY '"'
  LINES TERMINATED BY '\n';


If the file is not quite in the correct form to import directly into the target table, you can either use PHP to preconvert it, or load it into a "staging" table and let MySQL handle the necessary transformations, whichever is faster and more convenient.

As an additional option, it seems to be possible to run MySQL queries asynchronously via the MySQL Native Driver for PHP (MYSQLND) . You may also be able to explore this option. This will allow you to maintain instant UI performance.

+7


a source


If you do a lot of inserts, do you do bulk inserts? that is, as follows:

INSERT INTO table (col1 col2) VALUES (val1a, val2a), (val1b, val2b), (....

This will speed up inserts significantly.



Another thing you can do is turn off indexing when you make changes, and then let it rebuild the indexes in one go when you're done.

A little more detail on what you are doing and you can get more ideas

+1


a source


PEAR has a package called Benchmark that has a Benchmark_Profiler class to help you find the slowest section of your code so you can optimize.

+1


a source


We had such a function in a large application. We had a problem to insert millions of rows from csv into a table with 9 indices. After a lot of refactoring, we found the perfect way to insert data, to load it into a [temp] table using mysql LOAD DATA INFILE command , perform transformations there and copy the result with multiple insert queries into the actual table ( INSERT INTO ... SELECT FROM

), processing only 50k rows or so with every request (which performed better than throwing a single insert, but YMMV).

+1


a source


I cannot do it with cron because it is under user control. User click process buttons and later can check logs to see process status.

When the user clicks the specified button, set the flag on the table in the database. Then check the cron job for that flag. If it's there, start processing, otherwise it won't. I am applicable, you could use the same table to post some sort of status update (like xx% done), so the user has some feedback on the progress.

0


a source






More articles:


All Articles