0

User keys in search parameters, then we make a request to a data provider and redirect user to a loading page. The response from the data provider hits a callback url, in which case we parse the results and store about 200 rows into the db. Meanwhile the loading page uses ajax to query the db every second and when the results are all there we display the results to the user.

The issue is that insert into the mysql db is too slow. We know the response back from the data provider comes back within seconds, but the processing of the script and inserting of rows into the db is very slow. We do use multirow insert.

Any suggestions to improve? FYI, the code is hugely long... that's why not displaying right now.

TimNguyenBSM
  • 817
  • 2
  • 15
  • 33
  • how slow it is?? insert 200 rows, its won't be that fast also. – rahul Apr 24 '16 at 18:26
  • Extension, db engine, indexing ? Has pre processing before insert? Connect/disconnect for each insert? Enough machine power ? Many other things to consider I guess. – frz3993 Apr 24 '16 at 18:26
  • @Plum Takes about 15-25 seconds. On development its much faster about 2-3 seconds. – TimNguyenBSM Apr 24 '16 at 18:28
  • @frz3993 request and response is a XML file. Hits a php callback script. There is pre-processing before insert. Since it's a multirow insert we only the mysql database once. – TimNguyenBSM Apr 24 '16 at 18:29
  • Did you check the script execution time to make sure that it is caused by the insert? And you can enable the slow queries log and check if any query is too slow. – frz3993 Apr 24 '16 at 18:37
  • @TimNguyenBSM some pictorial representation, can you do that. Not understanding the requirement though. – rahul Apr 24 '16 at 18:37
  • There are quite obviously 100's of possible reasons for the difference between 15-20 secs and 2-3 seconds. You are in the best place to work out why this might be. **Or we can make lots of guesses if you like** – RiggsFolly Apr 24 '16 at 18:37

2 Answers2

0

There are multitude of factors affecting your insertions:

1) slow hardware and bad server speeds. Sol : Contact your server administrator

2) Use something other than InnoDB

3) Use a surrogate key , other than your primary key that is numeric and sequential along with your natural primary key.

OR

4) Try this https://stackoverflow.com/a/2223062/3391466.

Community
  • 1
  • 1
user3391466
  • 1
  • 1
0

Suggestion: Instead of running the code on one page and having the user wait the whole process, why not have the php page store the instructions in a php queue? The instructions would then be executed by a separate php script (for instance a Cron Job) and the user wouldn't have to wait for the whole process to take place.

However, in this situation it would be ideal to let the user know that the changes made can take a bit of time to update.

Cron jobs are very easy to implement. In CPanel there is an option for Cron Jobs where you specify which script you want to run and in which intervals. You can let your script know to run once every 1 minute (or more or less depending on how much demand there is). From there your script would check the queue and could keep on running until the queue is empty again.

Let me know if that helped!

Webeng
  • 7,050
  • 4
  • 31
  • 59