[TYPO3-dev] DB query iterator; worth a try?

[Ries van Twisk]

On Apr 22, 2010, at 4:23 PM, Jigal van Hemert wrote:

> Ries van Twisk wrote:
>> On Apr 22, 2010, at 11:31 AM, Jigal van Hemert wrote:
>>> Well, since you're the expert on DBAL: Can you tell if the other
>>> DBMS-es support somehow a method to tell if there is a new record
>>> available? If that is the case, an iterator can be  used.
>> I believe for some databases that don't support cursors the whole  
>> dataset will be loaded into PHP anyways.
>
> I don't think the dataset is loaded in PHP allocated memory. It's more
> likely that the database client receives the entire dataset or a large
> part of it. That will be handled by the database client.
> Anyway, these are details that we don't have to worry about :-)

Actually, in this case you should. Because it can be a difference  
between a very slow
iteration (using a cursors fetching each record using DB  
communications) or a fast iterator.

While looking at the source and it shows something like this:

static void php_mysql_do_query_general(zval **query, zval  
**mysql_link, int link_id, zval **db, int use_store, zval  
*return_value TSRMLS_DC)
...
...
..

                     mysql_result = mysql_use_result(&mysql->conn);
                                 while ((row =  
mysql_fetch_row(mysql_result))) {
                                         if (!strcmp("ALL", row[1])) {
                                                 php_error_docref("http://www.mysql.com/doc 
" TSRMLS_CC, E_WARNING, "Your query requires a full tablescan (table  
%s, %s rows affected
). Use EXPLAIN to optimize your query.", row[0], row[6]);
...
...
..

As you can see the complete result set will get loaded for a  
mysql_query, thus
the result set need to fit in PHP memory.

So a iterator should be about as fast  as using a wiole loop  
(classic...?? retreival)
and only the implementation of the iterator will add a bit of overhead.


>
>> In fact, if my memory serves right is that for the current  
>> implementations in PHP for normal queries (not cursor based) the
>> whole dataset will be read into memory using one call to the RDBM and
>> you can iterate over them because the DS is already there. There is
>> not real 'need' from the RDBM to support this. Using cursors is a
>> whole different story.
>
> To use an iterator in PHP you only need to be able to tell if there  
> is a next item.
> As explained PHP will call next(), valid() (which must tell if there  
> is a next item) and if valid() returns true, it will call current()  
> and key().
>
> I don't expect any problems for any database client in PHP with this.
>


Agreed, a iterator would be as fast as using a classic method, I do  
like iterators,
I don't use them to often though, this is more of a habit thing I guess.

Ries