0

I'm execuiting a simple query with sqlsrv driver for PHP returning 104586 rows x 30 columns as follows:

$sql = 'SELECT [columns] FROM [table]'
$stmt = sqlsrv_query($conn, $sql, $params);

while($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_NUMERIC)) {
    $dataSet[] = $row;
}

If I echo all the $row everything works and page loads in 4-5 seconds but if try to assign them to an array, PHP runs out of memory error with the following message in the log file:

PHP Fatal error:  Allowed memory size of 134217728 bytes exhausted (tried to allocate 20480 bytes)

I need data in array format for further usage. Is there a more efficient way of obtaining this? Am I missing somenthing?

Executing the query with SSMS takes 2 seconds, so there's nothing heavy in it.

PHP version is 7.0.23 if this might matter.

Mark
  • 645
  • 2
  • 9
  • 27
  • 1
    Did you try to increase limit of memory in php.ini? – DaszuOne Feb 28 '20 at 10:08
  • The first questions are: Do you real need all columns? Why want you to store it as array? Is it possible to do the calculations within the fetch loop? Increasing memory limit moves only the border, but isn't a real solution. – Wiimm Feb 28 '20 at 10:11
  • 1
    Have you read [this question](https://stackoverflow.com/questions/6336528/memory-optimization-in-php-array) ? Arrays are definetly not the best memory-optimised data structures in PHP. – Noah Boegli Feb 28 '20 at 10:15
  • Use LIMIT to create pagination if you need only some slices. – Markus Zeller Feb 28 '20 at 10:26
  • @DaszuOne I did it but is not a nice solution in my opinion...just a workaround. And it might not solve the problem as in the future the table might increase. – Mark Feb 28 '20 at 10:55
  • @NoahBoegli didn't know about this array / memory issue that PHP has. But rewrite the entire application in another language because of this is not an option. SplFixedArray went out of memory as well. – Mark Feb 28 '20 at 10:57
  • @MarkusZeller if you mean mysql's LIMIT option, i'm using SQL Server. I can use a TOP x but this will truncate the results, so it is not a solution. – Mark Feb 28 '20 at 10:58
  • Is using [OFFSET and FETCH](https://learn.microsoft.com/en-us/sql/t-sql/queries/select-order-by-clause-transact-sql?view=sql-server-ver15) to do pagination a possible solution? – Noah Boegli Feb 28 '20 at 11:06
  • @Mark yes, that's workaround only, but i had to ask - you don't wrote that you already do that :) another thing that sometimes it is necessary to have more memory to resolve some problem, but as Wiimm mentioned, you need to optimize your code and/or split data for further processing. – DaszuOne Feb 28 '20 at 11:13
  • 1
    You can use `ini_set('memory_limit', -1);` at start of the script to work around the limitation. – Markus Zeller Feb 28 '20 at 12:04

1 Answers1

0

This sounds like a memory limit problem, I cannot reproduce this, but edit your ini file with memory_limit = 256M or some larger number to increase the allowed memory size.

David
  • 65
  • 2
  • 8