Re: Fetch-on-demand iterators (was Re: [CDBI] Make CDBI go fast)

On 2/15/07, Michael G Schwern <[email protected]> wrote:
> Whatever the backend implementation, it sure sped things up.  The performance of search()->first when the search returns a million rows went from about 2 seconds to almost negligable.  Remember, even if the database is doing the same amount of work it still has to transfer it to your process and then run it through DBI and then bind it all into a list of hashes.

With DBD::mysql (and others), it will still transfer the rows to your
process, even if you never fetch them.  Using "mysql_use_result"
prevents this.  Some drivers will pay attention to DBI's
"RowCacheSize" setting.

> Do not be fooled!  There really is no "give me everything" bulk row loading optimization in DBI.  selectall/fetchall is just doing $sth->fetch in a loop!

It does it in a fast internal C loop though, which does help.  Also,
if you know you actually need all the rows, it's worth checking if
there's a way to tell your DBD driver to fetch in larger more
efficient chunks, like RowCacheSize.

- Perrin