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)
order by x1.id;
I don't think 1000 records selected and inserted per second is that bad,
but your idea below separating the select from the inserts may very well
speed it up. For extra speed sort the records in memory by "id" prior
to inserting them.
malloc()ing is not likely as important to disk seeking in this case.
Time the query on its own without the insert.
Also time everything from a :memory: database just for kicks to see
how much overhead the hard disk introduces.
--- Ken <[EMAIL PROTECTED]> wrote:
> 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
____________________________________________________________________________________
Park yourself in front of a world of choices in alternative vehicles. Visit the
Yahoo! Auto Green Center.
http://autos.yahoo.com/green_center/
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
