Not much you can do.
You could examine the output of EXPLAIN QUERY PLAN for those statements.
Hard to know without knowing the schema, but try making the "id" an
INTEGER PRIMARY KEY, assuming it's appropriate for your data.
Try to have as few indexes as possible on the table being inserted into.
--- Ken <[EMAIL PROTECTED]> wrote:
> I'm looking for your help solving a performance issue:
>
> Master db and an attached db. called A
>
> Table x and table Y are identical in both the master and Attached database.
> table Z is built to determine the set of rows to copy and has an index on the
> id field.
>
> The goal is to move data from many attached db's to the master.
>
> SQL statements:
> Begin;
> insert into x
> select x1.* from a.x x1 , Y
> where x1.id = y.id ;
> commit;
> takes 4.08 seconds to move 3904 rows.
>
> begin;
> insert into y
> select y1.* from a.x y1 , Y
> where y1.id = y.id ;
> commit;
> Takes 2.19 seconds to move 2676 rows.
>
> So X is moved at 922 r/s and y is moved at 1221 r/s.
> Table X has 16 columns and no blobs
> Table Y has 22 columns one of which is a blob.
>
> All columns are integers except for the blob on table y.
>
> So why is the X copy performance 25% slower than Y ?
>
> Page size is 4k.
> Cache size is 4000.
> locking_mode = exclusive.
> Synchronous off
>
> Is there any way to disable journaling?
> I can always re-create the DB should anything fail.
>
> Any other ideas on how to make this run quicker?
>
> Thanks,
> Ken
____________________________________________________________________________________
Building a website is a piece of cake. Yahoo! Small Business gives you all the
tools to get online.
http://smallbusiness.yahoo.com/webhosting
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
