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
a.x might be the problem. Given that the Master DB is on the same disk as the
attached DB.. So effectively causing all sorts of seek operations.
Question does sqlite when doing an insert into X select * from Attached.X, y
where x.id =y.id operate internally as follows???
for rows:
read 1 row from A.X
Insert row into X
end row
If so then I suspect performance could be greatly improved as follows:
create a large memory buffer say 10,000 rows..
Select and populate buffer.
Begin;
for each row in buffer:
insert into x
commit;
Could the overhead of memory allocation acount for the variances? Is there
any way to pin the sqlite allocated pages into the Master DB's cache? Sort of a
sub cached allocation so that attached DB's do not require malloc/free for
memory pages?
Ken
Joe Wilson <[EMAIL PROTECTED]> wrote:
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 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]
-----------------------------------------------------------------------------
