Geek Answers Handbook

Php performance issue in while loop

I use PHP to continue the latitude / longitude points to generate JS and display the points on the OSM map. I would like to make a new track on the map when I have a pause of 10 minutes or more on the recording.

My dataset currently contains about 30,000 records on about 10 different tracks (some tracks have about 300 points, others have thousands).

I ran into a performance issue with PHP. When a cycle combines several hundred points, the data is processed at a good speed, but if the track has thousands of points, performance drops sharply.

Here is the time it takes to go through each point for each track

+-----------------+------------------------------+ | Points On Track | Time To Proceed 10000 Points | +-----------------+------------------------------+ | 21 | 0.75 | | 18865 | 14.52 | | 539 | 0.79 | | 395 | 0.71 | | 827 | 0.79 | | 400 | 0.74 | | 674 | 0.78 | | 2060 | 1.01 | | 2056 | 0.99 | | 477 | 0.73 | | 628 | 0.77 | | 472 | 0.73 | +-----------------+------------------------------+

We see that when I have a lot of points on the track, the performances fall dramatically. In this particular case, processing all the points takes about 30 seconds. If I limit the number of points for each track to 500 points, the performances are pretty good (about 2.5 seconds to continue my data set).

I use my Synology DS415play as a web server.

Here is my code:

 $dataTab = array(); if ($result = $mysqli->query($sql)) { $count = 0; $row = $result->fetch_array(); $data = $dataTab[$tabPrt] . "[" . $row['latitude'] . "," . $row['longitude'] . "]," ; $date = new DateTime($row['time']); while($row = $result->fetch_array()) { $count++; $newDate = new DateTime($row['time']); if(($newDate->getTimestamp() - $date->getTimestamp()) > 600) { array_push($dataTab, $data); $data= ""; $count = 0; } $data = $data . "[" . $row['latitude'] . "," . $row['longitude'] . "]," ; $date = $newDate; } array_push($dataTab, $data); }

If I limit each track to 500 points, the performance is pretty good

 $dataTab = array(); if ($result = $mysqli->query($sql)) { $count = 0; $row = $result->fetch_array(); $data = $dataTab[$tabPrt] . "[" . $row['latitude'] . "," . $row['longitude'] . "]," ; $date = new DateTime($row['time']); while($row = $result->fetch_array()) { $count++; $newDate = new DateTime($row['time']); if(($newDate->getTimestamp() - $date->getTimestamp()) > 600 || $count > 500) { array_push($dataTab, $data); $data= ""; $count = 0; } $data = $data . "[" . $row['latitude'] . "," . $row['longitude'] . "]," ; $date = $newDate; } array_push($dataTab, $data); }

thanks

EDIT: here I provide sample data: http://109.190.92.126/tracker/gpsData.sql Slow script: http://109.190.92.126/tracker/map.php Normal speed of execution, dividing each track (maximum 500 points): http://109.190.92.126/tracker/map_split.php

thanks

+7
performance php while-loop
source share
2 answers

Here is the final product (wait for it on Heroku before launching dinosaur) http://sove.herokuapp.com/gps/

The idea of ​​the solution is to calculate the diff in the timestamps on the server side and manipulate the data in the arrays.

the script ends with 0.278 with my MBP, takes 15.75 MB of memory, and the final output of $ts is an array of routes (7 in total).

There are many optimizations, including skipping the same coordinate points. Scale limitations are not true on the map, but you will find out. I had to really ask for generosity for this work ... If you like the result, let me know, I can share the code base.

Source: https://gist.github.com/jpaljasma/04f54e0d2fa3a632071e

+1
source share

If you get 18,000 records from the database in your worst case, you can transfer the timestamp check to the query to significantly reduce it, it seems that all you do is see that if there is a ten-digit space, then clicking on the array, which can be run at the mysql level, so you won’t retrieve 18,000 rows from the database each time, only the ones you need.

If you post your queries in mysql, we can take a look at what it is there.

Edit: try changing the request to this:

 SELECT time, latitude, longitude FROM gpsData WHERE time >= '2015-09-01' AND provider = 'gps' ORDER BY time DESC
+2
source share

More articles:


All Articles