PDO fetchall () performance considerations?

Question

After reading several articles on the performance impact of PHP PDO fetchall() , I wonder if there is another way to achieve the same result without calling fetchall() .

To explain, most agree that fetchall() tends to be resource intensive. In my case, I do not think this will be too big a problem. Yes, I need to pull the whole table from my database and display it to the user, but it will be about 100 rows; I do not believe that this will be a problem. Hypothetically, however, if I needed to pull out 100,000 lines, what would be a better solution?

Answer 1

Hypothetically, if you need to print all 100,000 rows in one answer, you should set PDO::MYSQL_ATTR_USE_BUFFERED_QUERY to false , execute the query and repeat the result set one row at a time using fetch . To clarify, php buffers the entire result set regardless of whether fetchAll () is called if PDO::MYSQL_ATTR_USE_BUFFERED_QUERY set to true.

The advantage is that you drastically reduce the max script memory consumption, and you can start the streaming stream earlier, although the total time to completion may or may not be longer.

I ignore other things that you should consider in extreme circumstances such as output buffering, etc.

Answer 2

An alternative is to select one line after another inside the loop, for example:

 while( $row = $statement->fetch() ){ // so something with $row } 

Obviously if you need all the lines, for example. to calculate some statistics with them, the above does not work. However, in many cases, SQL provides solutions that allow you to calculate such statistics on the fly and return only the result.