Joe,
each of the tables involved also had a parent table. that was also being
copied.
It turned out that the parent table copy (insert .. select) was taking over 50%
of the time. So I flattened the tables including the neccessary fields into the
children tables.
This doubled the throughput
Forget about the alternate insert statements I suggested.
Assuming "id" is declared INTEGER PRIMARY KEY in all tables,
you can't get better performance than this in a single
insert statement:
insert into x select x1.* from a.x x1, y where x1.id = y.id;
Joe,
Yes I have temp_store = memory... I think during the compilation of the
library.
I don't think I need the order by clause but I'll try it just to see if it
helps...
The original table is created and stored in sorted order so just a plain
select returns sorted rows.
I'll
--- Joe Wilson <[EMAIL PROTECTED]> wrote:
> This should be faster:
>
> insert into x
> select * from a.x x1
> where exists (select 1 from Y where x1.id = y.id);
>
> See if adding an order by statement will make it faster by speeding
> up the inserts:
>
> insert into x
> select * from a
This should be faster:
insert into x
select * from a.x x1
where exists (select 1 from Y where x1.id = y.id);
See if adding an order by statement will make it faster by speeding
up the inserts:
insert into x
select * from a.x x1
where exists (select 1 from Y where x1.id = y.id)
orde
Joe,
Thanks for the reply.
The id field is also the Primary Key and no other indices exist. I've run the
explan query plan and they seem optimal, reading table X, and using an index
access into Z.
I just had a thought. It seems to me that doing the insert into X select from
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.
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 a
8 matches
Mail list logo
