MySQLi query vs PHP Array, which is faster?

Question

I'm developing an algorithm for intense calculations on multiple huge arrays. Right now I have used PHP arrays to do the job but, it seems slower than what I needed it to be. I was thinking on using MySQLi tables and convert the php arrays into database rows and then start the calculations to solve the speed issue.

At the very first step, when I was converting a 20*10 PHP array into 200 rows of database containing zeros, it took a long time. Here is the code: (Basically the following code is generating a zero matrix, if you're interested to know)

$stmt = $mysqli->prepare("INSERT INTO `table` (`Row`, `Col`, `Value`) VALUES (?, ?, '0')"); 
for($i=0;$i<$rowsNo;$i++){
    for($j=0;$j<$colsNo;$j++){
        //$myArray[$j]=array_fill(0,$colsNo,0);
        $stmt->bind_param("ii", $i, $j); 
        $stmt->execute(); 
    }
}
$stmt->close();

The commented-out line "$myArray[$j]=array_fill(0,$colsNo,0);" would generate the array very fast while filling out the table in next two lines, took a very longer time.

Array time: 0.00068 seconds

MySQLi time: 25.76 seconds

There is a lot more calculating remaining and I got worried even after modifying numerous parts it may get worse. I searched a lot but I couldn't find any answer on whether the array is a better choice or mysql tables? Has anybody done or know about any benchmarking test on this?

I really appreciate any help.

Thanks in advance

UPDATE:

I did the following test for a 273*273 matrix. I created two versions for the same data. First one, a two-dimension PHP array and the second one, a table with 273*273=74529 rows, both containing the same data. The followings are the speed test results for retrieving similar data from both [in here, finding out which column(s) of a certain row has a value equal to 1 - the other columns are zero]:

It took 0.00021 seconds for the array.
It took 0.0026 seconds for mysqli table. (more than 10 times slower)

My conclusion is sticking to the arrays instead of converting them into database tables.

Last thing to say, in case the mentioned data is stored in the database table in the first place, generating an array and then using it would be much much slower as shown below (slower due to data retrieval from database):

It took 0.9 seconds for the array. (more than 400 times slower)
It took 0.0021 seconds for mysqli table.

the only way to know is to do your own benchmarking. may be worth hosting it say on ec2 then writing an api to push pull dat as required the you can run it on the appropriate hardware software, at pay for use rates
For a fair comparison, you should bulk insert the arrays it would be much faster than row-by-row insert. But the right approach as mentioned by @Dagon is to test for yourself. You do have performance testing as a tag for your question, so do some performance testing.
The number of factors that effect this type of performance is huge, just quartered a one hour scripts run time by tweaking some db indexes.
You'll only get a performance boost if your calculations are written in MySQL - otherwise, you'll always need to load the tables back into memory anyway... That having been said (and I'm not a PHP developer), the slowness of your inserts is probably related more to the creation of a database connection for each cell in your destination table. For speed, you should consider some sort of bulk insert approach. It's also possible that your (row,col,value) table isn't conducive to performance - can you redesign?
Thanks for the replies. Dagon and Mike, I'm writing up my own benchmarking test on the localhost and will let you know of the results. Simon I'm creating my own code and definitely I can redesign them. The problem with the "bulk insert" is that these matrices of data are generated inside the code, thousands of time and are not predefined data in the file to use that function you mentioned. It is intended to be a Genetic Algorithm application which has a lot of iterations of calculations.

SaVaFa · Accepted Answer · 2013-05-08 20:27:25Z

In my case, as shown on the update part of the question, I think arrays have better performance than mysql databases.

Array usage showed 10 times faster response even when I search through the cells to find desired values in a row. Even good indexing of the table couldn't beat the array functionality and speed.

Your Common Sense · Answer 2 · 2013-05-08 05:08:30Z

Although there is a way to speed up your insert queries (most likely you are using innodb table without transaction), the very statement of question is wrong.

A database intended - in the first place - to store data. To store it permanently. It does it well. It can do calculations too, but again - before doing any calculations there is one necessary step - to store data.
If you want to do your calculations on a stored data - it's ok to use a database.
If you want to push your data in database only to calculate it - it makes not too much sense.

Thanks for the answer. I'm trying to see if the huge multiple indexing needs of my calculations, can be improved by databases or not. In arrays I need to scan all the cells to get to the desired value but in database a query maybe faster finding it.

asked	2 days ago
viewed	54 times
active	2 days ago

MySQLi query vs PHP Array, which is faster?

2 Answers

Your Answer

Not the answer you're looking for? Browse other questions tagged php arrays mysqli benchmarking performance-testing or ask your own question.

MySQLi query vs PHP Array, which is faster?

2 Answers

Your Answer

Sign up or log in

Post as a guest

Not the answer you're looking for? Browse other questions tagged php arrays mysqli benchmarking performance-testing or ask your own question.

Related