Re: [PDO] Fwd: Bug #50755 [Opn]: PDO DBLIB Fails with OOM

Stanley Sufficool wrote:
> Returning an array of results from large recordsets as an array lands
> me back at square one with the OUT OF MEMORY bug.

This is an area where the business logic within the database may be more 
appropriate for reducing the amount of data required to be returned to PHP. 
There are a number of actions that I would more normally handle in triggers in 
Firebird, but these have to be handled in a more generic way in shared projects. 
Like you I could speed things up considerably by simply ignoring all of the 
other developers on bitweaver or PGV ..... and people are developing even more 
databases to fix their own problems .....

> There is no way ADOdb will ever out perform PDO since it performs
> another copy of the data above the driver level. Just as PDO will
> never outperform a well coded native interface due to another memcopy,
> of at the least, pointers to data.

That I think is the crux of the problem. Creating a lower level API that 
actually allows the higher level to transparently see the data ... why is 'pass 
by reference' no longer available where it would be most useful?
That was the original 'target' for PDO I think, but currently one still has to 
use generic drivers in parallel for all of the functions that PDO does not 
currently provide access to. The statement that we should add that functionality 
to PDO has hit the current brick wall of 'how'. The PDO framework simply does 
not allow it currently and that needs fixing before things can move forward.

>> Of cause for best speed you use numbered arrays anyway ;)
>> But this is where PDO is currently falling over ... even simply switching
>> off 'blob streams' would be better currently.
>
> blob/clob/image/text streams are the only way some drivers will return
> large object data. You can have a 2TB field of raw blob data and the
> only way is to deal with it as a stream. I don't advocate the use a
> database for large objects, but that's not my call and the driver
> needs to handle it in a rational way. Streams are faster in some cases
> since again, you are copying only the data on-demand from the driver
> instead of allocating a huge in-memory  buffer and streaming the data
> into it. Chances are, if the driver (like dblib) supports a streaming
> interface to blobs, the driver will do it more efficiently than PHP
> can.

The problem here is that we can't switch between blob and text on a field by 
field basis in PDO. In Firebird 'TEXT' is a BLOB SUBTYPE TEXT, and so PDO 
handles it as a stream, Oracle uses CLOB which gets the same treatment, but TEXT 
fields are returned as strings. A database schema designed for MySQL will simply 
not work when mapped to Firebird or Oracle, so one has to create a higher level 
'hack' to transfer the stream to the string the MySQL version is expecting. I 
agree with your statement on 'large objects' and I keep THAT sort of data 
separate, so for me returning blobs as strings fixes the problem.

>> Stanley - if you have no interest in cross database working, then I would
>> SERIOUSLY suggest simply comparing performance between pdo and the generic
>> dblib driver. And certainly use one of the accelerators which as indicated
>> in the above benchmark makes a considerable difference. I use eaccelerator
>> which performs well for me.
>
> PDO with my out performs the generic dblib driver because of fewer mem
> copies. The generic DBLIB driver also still has the OOM bug with large
> recordset statements. I'm not inclined to fix that extension.

That answers the question!
Other generic drivers are still being developed RATHER than fixing the PDO 
version which is the underlying problem. DBLIB is obviously the exception to the 
rule :) Things like 'asynchronous queries' are a perfect candidate for PDO, but 
they have been developed in isolation on MySQL driver and I think Posgresql has 
them now? The method used is transparent to the database and so could be easily 
ported into PDO.

-- 
Lester Caine - G8HFL
-----------------------------
Contact - http://lsces.co.uk/wiki/?page=contact
L.S.Caine Electronic Services - http://lsces.co.uk
EnquirySolve - http://enquirysolve.com/
Model Engineers Digital Workshop - http://medw.co.uk//
Firebird - http://www.firebirdsql.org/index.php