Re: [cx-oracle-users] Re: Need fetchall invalidate LOB locators?

[Michael M. <mi...@ma...>]

On 15 Jul 2005, at 22:55, Anthony Tuininga wrote:

> On 7/15/05, Hamish Lawson <ham...@gm...> wrote:
>
>> Anthony Tuininga wrote:
>>
>>
>>> without looking at the code I would guess that [DCOracle2] define  
>>> new
>>> LOB locator variables for each of the rows that are fetched. That
>>> seems to me to be a lot of overhead for very little gain --  
>>> unless you
>>> can give me some valid reason for why fetchall() is a good idea when
>>> dealing with LOBs.
>>>
>>
>> The main reason I'd offer is completeness and to avoid special- 
>> casing.
>> I've been working on the contribution of an Oracle driver to the PyDO
>> 2 ORM project, and I've been obliged to reimplement one of the
>> inherited core methods just because it uses fetchall; I can envisage
>> other situations where code that is not our own might expect to be
>> able to call fetchall. You are right of course that there is a memory
>> overhead in allocating LOB locator variables for each row. But any
>> time the programmer chooses between fetchall and fetchone/iteration,
>> they are deciding on a trade-off between memory and time costs. Why
>> for the special case of the resultset's having LOBs should cx_Oracle
>> remove that choice from the programmer?
>>
>
> The main reason is because I can't tell up front which method you are
> going to use. Thus, I would have to use the "bad" method at all times.
> If you can think of a sane way of getting around this problem I'd love
> to hear about it. If any one else wants to chime in with an opinion
> I'd love to hear those, too.


I don't have a sane implementation suggestion, except perhaps an  
argument to the constructor like threads (true-false), however I do  
have use cases.

I do a lot of bioinformatics work where I use large strings, many of  
which are stored as CLOBs, now most of the sequences are actually  
shorted than 4000 char long, but a proportion of them are not, so I  
need to use CLOBs. I regularly need to get many sequences, and while  
in most cases I use fetchall() or fetchmany() I obviously can't with  
CLOBS. As I often need all the sequences in memory at once to do  
something with them saving memory by iterating over them one at a  
time saves me nothing and just costs time, although usually not  
enough to really worry about.

In other situations I need to iterate over millions of CLOBS and  
doing them a few hundred at a time would certainly save a substantial  
amount of time, at some memory cost, but not that much.

Finally I agree with the special casing point, It'd be great to be  
able to deal with all queries in essentially the same fashion, but I  
can't use the same pattern for CLOBs.

It isn't a deal breaker but if there was a clean way of being able to  
use fetchall()/fetchmany() with LOBs I'd be very happy to see it.

cheers

Michael