On Sat, 2005-07-16 at 00:25 +0200, Pavel Cisar wrote:

> Maurice Ling wrote:
> >
> > It is not about the join, it is about the amount of data returned. I can
> > do a select * from X table in isql.
> >
> > cursor.execute("select * from X")
> > data = cursor.fetchall()
> >
> > gives me a vm_allocate() error
>
> Can't you use a loop over fetchone() or itermap() ?
>
> best regards
> Pavel Cisar

Extending on this ... make the process streaming. Use the IO "idle"
time to harvest CPU to run the transformations.

1. Run the fetch loop in one thread - package up text into batches of
<<configurable>> elements to process and put it into a queue q1. Signal
end of data by putting null onto q1.



2. Run the transformations in a second thread (or set of threads,
depending on whether you are IO or CPU bound), pulling raw data from q1
and putting output parameter batches into queue q2. detect end of input
as null returned from q1. Signal end of queue by putting null onto q2.
Ensure that end of queue remains marked for other transformation threads
by putting null back into q1 also.



3. Run the writes to firebird in a third thread (or set of threads),
receiving batches of text processed into parameters from q2 and writing
them to the database. Every thread has its own Firebird connection.
Strive for a "single write" paradigm - there is no need to keep
intermediate steps if you can avoid it, especially rows that you will
delete. Commit at the end of every batch. Detect end of data as null
returned from q2. Ensure that null is reposted to q2 so other writer
threads can detect end of data.


Helen would probably be better able to tell you whether super server
tied to one CPU or classic would work best for this application.

Since you are only expecting to produce a maximum of three million rows
of data, there should be no need for restart logic. Unless your
transformations are extremely complex, your server is extremely slow, or
your communications infrastructure is really slow, this should get the
data really moving.

I find 1 reader -> 2 transformers -> 4 writers seems to be an ideal mix
on 100 MHz backbone with WIntel (ugh!) 2 ghz pentium app servers. I
have seen benefits from up to 5 transformers and 20 writers when the
transformation was trivial. I am using java and xslt for my
transformation problem, but the tradeoffs for python probably come out
similar.