[DB-SIG] Fetching result sets

[Keating, Tim]

> > On a side note, I've always been a bit dissapointed by how 
> long it takes
> > to actually wrap data from the RDBMS to python.  For 
> example, my C layer
> > API can retrieve 39100 results from a Zip code table in .7 seconds
> > (actually fetching the data into an opaque result set); yet the
> > operation of turning these into python values adds an additional 2.4
> > seconds -- by the time it goes through all of the python 
> layered above,
> > it blows out to about 9 seconds for a fetchall(). Granted, 
> part of that
> > is because that code is not highly optimized, but it still irks me.
> 
> Well, this is most certainly due to the fact that Python stores
> the values in objects rather than a C array. (You could, however,
> try to use the array module for faster access to these values.)

Actually, the way to do large homogeneous ops in oracle is to use bulk
binding w/ PL/SQL tables. Unfortunately, I don't believe DCOracle2 supports
this currently. (For that matter, does the Python DB API?)

I recently had to do this, writing an application-specific module on top of
OTL. Inserting 1000 rows manually (i.e. doing 1000 INSERT statements) took
about 4 seconds, bulk binding was .4 seconds. However, while 10K rows took
40 seconds with individual inserts (as expected), the cost of bulk binding
merely doubled (.8 seconds).

TK