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
