Big result sets problem

[eluser]rickoverman[/eluser]
Hello all,

Im having the following problem, when i try to load a result like this (query return about 670,320 records):

1. $q = $this->db->query($query);
2. $result = $q->result_array();

The script stops at line 2 with a 500 internal error message, but cant seem to find out what is the problem. When i use a limit of lets say 10 records its fine.

Is this a memory problem? because the server has lots of memory and should easily handle this. Even when ini is set to 2044M it doesnt work.

Does anyone know the problem and/or a solution/workaround?

Thanks in advanced!
Rick

[eluser]xeroblast[/eluser]
try to set your config log_threshold to 4

Code:

$config['log_threshold'] = 4;

then run the script and look the logs in system/logs/ (linux: set write permission to system/logs folder)
and read where it stop

[eluser]rickoverman[/eluser]
Here's the output, doesnt seem to bad

DEBUG - 2010-03-04 11:26:33 --> Config Class Initialized
DEBUG - 2010-03-04 11:26:33 --> Hooks Class Initialized
DEBUG - 2010-03-04 11:26:33 --> URI Class Initialized
DEBUG - 2010-03-04 11:26:33 --> Router Class Initialized
DEBUG - 2010-03-04 11:26:33 --> Output Class Initialized
ERROR - 2010-03-04 11:26:33 --&gt; Severity: Warning --&gt; fopen(/var/www/vhosts/***/httpdocs/prv/dev2/system/cache/a5bfc9e07964f8dddeb95fc584cd965d) [<a href='function.fopen'>function.fopen</a>]: failed to open stream: Permission denied /var/www/vhosts/dp.mod.proove-bv.nl/httpdocs/prv/dev2/system/codeigniter/Common.php 67
DEBUG - 2010-03-04 11:26:33 --&gt; Input Class Initialized
DEBUG - 2010-03-04 11:26:33 --&gt; Global POST and COOKIE data sanitized
DEBUG - 2010-03-04 11:26:33 --&gt; Language Class Initialized
DEBUG - 2010-03-04 11:26:33 --&gt; Loader Class Initialized
DEBUG - 2010-03-04 11:26:33 --&gt; Helper loaded: url_helper
DEBUG - 2010-03-04 11:26:33 --&gt; Helper loaded: file_helper
DEBUG - 2010-03-04 11:26:33 --&gt; Session Class Initialized
DEBUG - 2010-03-04 11:26:33 --&gt; Helper loaded: string_helper
DEBUG - 2010-03-04 11:26:33 --&gt; Session routines successfully run
DEBUG - 2010-03-04 11:26:33 --&gt; Controller Class Initialized
DEBUG - 2010-03-04 11:26:34 --&gt; Database Driver Class Initialized

[eluser]xeroblast[/eluser]
this should be the last 2 lines of your logs...

DEBUG - 2010-03-04 16:10:07 --&gt; Final output sent to browser
DEBUG - 2010-03-04 16:10:07 --&gt; Total execution time: 0.0212

find who executed the database driver class and the error is the one next to it...

[eluser]rickoverman[/eluser]
sorry missed last 2 lines from the log:
DEBUG - 2010-03-04 11:54:49 --&gt; Model Class Initialized
DEBUG - 2010-03-04 11:54:49 --&gt; Model Class Initialized

Dont see anything wrong with the code really. when i remove the $q->result_array(); the log end correctly with:
DEBUG - 2010-03-04 11:54:50 --&gt; Final output sent to browser
DEBUG - 2010-03-04 11:54:50 --&gt; Total execution time: 1.5692

[eluser]xeroblast[/eluser]
maybe this is your problem:

http://php.net/manual/en/function.array-....php#36837

[eluser]rickoverman[/eluser]
Indeed, well thanks for the help!

My dirty solution will be as followed as the $q->num_rows() is working.

i will loop trough a sequential limit of the query to avoid to much mem consumption of the array.
1. limit 0,1000
2. limit 1000,1000
etc.

Grtz, R.