Gentlemen programmers, you need professional advice or an idea to increase the speed of the script in php. What the script does:

1. It loads, processes and csv-file with data, recording this file in MySQL database

2. Then the user enters certain data (in this case, the cost per unit of service from each of the well-known providers)

3. The script unloads an array of data from the database, processes each record using mysql _ fetch _ array, prefixing the provider and substituting the required cost + calculating the total cost (number of service units * cost) now using the if operator, as in practice He was faster switch-case. And it writes line by line to the * .xls file.

How can I optimize / speed up this script? Because when processing 40,000 entries, it returns a timeout message. yes and 15.000 records processes ~ 5 min.

Any ideas?

1 answer 1

It is difficult to judge without seeing the implementation. But the first thing you need to check is 1) if this is an InnoDB table, insert data in chunks using transactions, if MyISAM, then create a group of queries and immediately add 2) Verify the correctness of indexing on the database 3) Find the bottleneck among the 3 stages. Maybe you are fine and the longest, say 99%, is the CSV download.

and so, this is a fortune telling. Take measurements of the speed of each stage and see where the brakes go. A sample of 40,000 - with the right indices - a fraction of a second. How complex data is loaded and how it is downloaded - tests, tests, tests.

By the way, from all that I have described, I don’t like the third item exactly. The fact that you are all parsit. It may be more correct at the download stage to parse and distribute the data in such a way so that you can simply choose the right one and not form the whole thing using PHP.

  • I got the result that the most problem is not to download csv. CSV is loaded instantly, but with the handling of the problem. Tests show that the problem itself is in the amount of data being processed. That is, the problem begins at the time of processing the lines and writing to the * .xls file. But there are no problems with writing to xls either. It remains - only data processing line by line. - Vasily Koshelev
  • But with the separation of data - a good idea, you need to think and try. - Vasily Koshelev
  • Well in this case, when adding data, immediately break by prefixes and when you make a sample, immediately select the data whose prefix you need. - aldem67