Unanswered: PHP - Execution time is more than 45 mins.
i m working on project(PHP4.x/MySQL4.x) where DB size is more than 30 GB
there is one module, in which i have to import the data from CSV file and after manipulating the data storing the data in the DB.
Round about 5000 SQL statements are fired during this procedure. and the total time taken is more than 40 mins.
how can i reduce the execution time?
i have tried to run this PHP script in Background but somehow it's not succeced
what can be optimised solution to reduce the execution time?
To achieve the fastest possible insert into a database from a CSV file take a look at the - LOAD DATA INFILE 'file_name' or LOAD DATA LOCAL INFILE 'file_name' statements. Depending on what processing you are doing on the data, you could either insert directly or create an intermediate file and load the data from that file.
If the long execution time is due to the processing being done in PHP, you would need to describe what you are doing and post your code to get more specific help.
See the other sections in the manual in that same chapter. 7.2.14 is for UPDATES (which is a SELECT plus a write.) A user contributed an interesting method when the rows to be updated had some common select condition - he ran a SELECT to get the ID's, then updated just the rows using those ID's instead of scanning through the whole database on each and every update.
Depending on what is changing in the data, you could also do something like the above with the LOAD DATA LOCAL INFILE - select the rows you know are getting updated, use PHP to update the data and write a CSV file, then use the LOAD DATA... command to replace the changed rows in the database.
Since an update involves a SELECT, see chapter 7.2.3 in the manual as well. Having the correct indexes is the one thing that will have the greatest effect on how quick a row can be found and updated.
Edit: If you post more specific information about what you are doing, including your table definitions and your PHP code, someone here can see if there is a problem and provide specific things that will help.
In case you check this thread, I see you have posted another thread which mentions a 10 minute execution time. That thread caused me to recall that there is a performance problem with mysql servers that has to do with name lookup. I can also remember posts where this problem occurs even if you are specifying an IP address in the connection.