Managing large datasets
Permalink
I'm in the process of converting a Wordpress-based application into a C5 one and have run into an issue with the DB handling.
I know for a fact I will have to return datasets of very large numbers of records (e.g. for export) and this 'broke' Wordpress whose DB built-ins think it's a great idea to load the entire resultset into memory. Doing it in Mysqli was an easy enough solution and outputting the data via the time-honoured while($r=fetch_assoc()) {...} would allow for any number of rows that the DB could return.
DBAL seems to suffer from exactly the same shortcomings - the 2.1 API docs don't seem to provide an iterative per-row consumption of query results and fetchAll() would be idiotic for any large dataset on a busy site.
Before I just revert to good old known quantity Mysqli for handling large results I wanted to check that there wasn't a sensible iterative approach to getting at the data in a sane manner that didn't murder server memory, builtin to DBAL (which may be more recent than 2.1 now, admittedly, that was an older C5 article).
Talking about data that kills an in-memory DB handler which was given 1GB of RAM in the PHP limit before converting it to DB-pointer style handling. The non-Wordpress-builtin Mysqli approach reduced that script to about 53MB during profiling so I'm obviously not going to visit anything resembling a fetchAll() solution.
I know for a fact I will have to return datasets of very large numbers of records (e.g. for export) and this 'broke' Wordpress whose DB built-ins think it's a great idea to load the entire resultset into memory. Doing it in Mysqli was an easy enough solution and outputting the data via the time-honoured while($r=fetch_assoc()) {...} would allow for any number of rows that the DB could return.
DBAL seems to suffer from exactly the same shortcomings - the 2.1 API docs don't seem to provide an iterative per-row consumption of query results and fetchAll() would be idiotic for any large dataset on a busy site.
Before I just revert to good old known quantity Mysqli for handling large results I wanted to check that there wasn't a sensible iterative approach to getting at the data in a sane manner that didn't murder server memory, builtin to DBAL (which may be more recent than 2.1 now, admittedly, that was an older C5 article).
Talking about data that kills an in-memory DB handler which was given 1GB of RAM in the PHP limit before converting it to DB-pointer style handling. The non-Wordpress-builtin Mysqli approach reduced that script to about 53MB during profiling so I'm obviously not going to visit anything resembling a fetchAll() solution.
So I looked at the PDO docs instead and indeed 'fetch()' will pull data in a row-wise manner from a result handle.
Seems to work but nil points for the DBAL reference page.