--- In firebird-support@yahoogroups.com, "Rick Debay" <rdebay@r...> wrote:

> > ...get a list of primary keys from A table using
> kinterbasdb.cursor.execute() method and roll out as a Python list using
> interbasdb.cursor.fetchall().
> > For each of the elements in the list (primary keys), it is used to
> pull out a record in another table for processing
>
> Instead of getting a list and looping through the list selecting from
> another table, select directly from a join between the two tables, which
> presumably are connected by a foreign key.
> Can you post the DDL for the two tables, including foreign keys and
> indexes, and the plan used by the optimizer?

I've tried to do an inner join before but I get a kernel error through
Python (from vm_allocate() method) in my Mac system. This issue had
been documented and Python does not have vm_allocate() method in its
codes. Which is why I used this workaround to get all my records.

>
> > ...insert the results (usually a few records) into another table.
> This process is repeated for about 500k times.
>
> Does this need to be one transaction, or can you commit every 10,000
> items?

KinterbasDB keeps the transactions as transparent as possible. In
general usage, transaction is not even a concept to worry about.

What I did is this (python codes):

import kinterbasdb
connection = kinterbasdb.connect(<connection string>)
cursor = connection.cursor()
# inserting data for example
for line in data:
cursor.execute(<sql statement>)
cursor.commit()

# when i'm done
cursor.commit() # final commit, just in case
cursor.close()
connection.close()

>
> Since the sysadmin installed it just for you, do they do any
> maintainence such as sweeps or index selectivity calculations?
> I'm guessing that regular maintainence is done on the other databases,
> including vacuuming the Postgres, your other MGA database.

I am sweeping my database everyday through cron now. Doesn't seems to
help very much.

cheers
maurice