0

I want to read data from a table (or view) on SQL server 2008R2 using PHP 5.4.24 and freetds 0.91. In PHP, I write:

$ret = mssql_query( 'SELECT * FROM mytable', $Conn ) ;

Then I read rows one at a time, process them, all is ok. Except, when the table is really big, then I get an error:

Fatal error: Allowed memory size of 1073741824 bytes exhausted (tried to allocate 4625408 bytes)
in /home/prove/test.php on line 43

The error happens in the mssql_query(), so it does not matter how I query.

Altering the query to return less rows or less columns is not viable, because I must read a lot of data from many tables in a limited time.

What can I do to convince PHP to read in memory one row at a time, or a reasonable number at a time?

carlo.borreo
  • 1,344
  • 2
  • 18
  • 35
  • How many rows are we talking about and how much data is in each row? – slapyo Oct 10 '14 at 16:24
  • 1
    Having indexes may help, if you're not already using those. – Funk Forty Niner Oct 10 '14 at 16:26
  • Do you really need everything from the table? Perhaps you could consider splitting it into small tables. – James Oct 10 '14 at 16:28
  • I have no control on the database server, and I cannot read some lines/columns every time because the tables involved are many and I would like to keep the code simple. – carlo.borreo Oct 10 '14 at 16:33
  • Maybe unbuffered queries will help: PDO : details: [PDO::SQLSRV_CURSOR_DYNAMIC: Creates a server-side (unbuffered) dynamic cursor](http://technet.microsoft.com/en-us/library/ff628176%28v=sql.105%29.aspx). OR: PHP Manual :[function.sqlsrv-query.](http://uk3.php.net/manual/en/function.sqlsrv-query.php) – Ryan Vincent Oct 10 '14 at 16:39
  • Table has 5 millions row, each some hundred bytes total size. – carlo.borreo Oct 11 '14 at 09:39

1 Answers1

0

I agree with @James in the Question comments, if you need to read a table so big it exhausts your memory to stash the results before even handing back to PHP, it probably just means you need to figure out a better way. However, here is a possible solution (untested and I've only used MSSQL a couple of times; but if not perfect, I tried my best):

$ret = mssql_query( 'SELECT COUNT(*) as TotalRows FROM mytable', $Conn);
$row = mssql_fetch_row($ret);

$offset    = 0;
$increment = 50;
while ($offset < $row['TotalRows'])
{
    $ret_2 = mssql_query("SELECT * FROM mytable ORDER BY Id ASC OFFSET {$offset} ROWS FETCH NEXT {$increment} ROWS ONLY", $Conn);

    //
    // loop over those 50 rows, do your thing...
    //

    $offset += $increment;
}

//
// there will probably be a remainder, so you'll have to account for that in the post-iteration; probably making the interior loop a function or method would be wise.
//
Hans
  • 3,403
  • 3
  • 28
  • 33
  • And thinking about it, if the structure is badly designed in the first place, then a hack (albeit also a fantastic fix) would be as @Fred-ii- suggested - indexes if none already exist. On a poorly designed table, it can make an entire world of difference, though the query would need to be amended to use the index more efficiently. – James Oct 11 '14 at 15:02