Re: [cx-oracle-users] Loop over Cursor stalls for several minutes

[Grzegorz D. <gol...@wp...>]

> Could it be the case that the initial time might be in setting up and
> executing the query?  Are there joins, order bys, etc?

Wouldn't that affect cursor.execute() rather than the iteration? I was
thinking memory allocation for fetched data or so...

Anyway, I think it's unlikely this has to bo with the RDBMS executing the
query so long. Sometimes Python finished in 50 seconds in the first
iteration. I also had attempts when in consecutive executions against the
same data in a loop (like in the example from the original post) I got the
excessive timing several times. Finally, when running the script against a
series of database schemas with same data scheme, different content,
something the tool has been designed for, I usually got the 10 minutes plus
performance only for the first one, no matter what order the schemas were
processed.

Finally, I also have a legacy tool which performs the same SQL in a more
simplistic Python setup and that one always finished with the data I'm using
for testing in about 1 minute 10 seconds. That's why I started looking
towards cx_Oracle internals, though that might not be the good direction
too.

Here's the code of the generator function that steers the cursor:

def cycle(self, schema, matchprofile, fixedcond):

    cursor = self.cursor
    cursor.set_schema(schema)
    cursor.execute('TRUNCATE TABLE schema.table')
    metaatts = self.get_metaattributes(schema)

    for condset in matchprofile:
        condsql = self.dostuffwith(matchprofile, fixedcond)
        qry = self.qrybase.replace('<<condset>>',
condset).replace('<<condsql>>', condsql)
        cursor.execute(qry)             # queries performing logic aganst
database

    cursor.execute(self.finalqry)           # select query extracting
results

    for row in cursor:
        yield Row(row, metaatts.copy())

    if self.counts:
        self.counter.addto(cursor.rowcount)

When run against multiple database schemas, as normally envisaged, the
output of several such generators is chained to form a single data stream
which is then written to a file.

cycles = (self.cycle(schema, matchprofile, fixedcond) for schema in schemas)
rows = itertools.chain.from_iterable(cycles)

- Grzegorz Dabkowski