On Fri, 3 Mar 2006, Daniel Franke wrote:
>
>Another question that arose today:
>Is there any penalty for switching tables during INSERTs within the
>same COMMIT? E.g.
>
> BEGIN;
> INSERT INTO tableA VALUES ...;
> INSERT INTO tableB VALUES ...;
> INSERT INTO tableA VALUES ...;
> INSERT INTO tableB VALUES ...;
> :
> COMMIT;
>
>opposed to
>
> BEGIN;
> INSERT INTO tableA VALUES ...;
> INSERT INTO tableA VALUES ...;
> INSERT INTO tableA VALUES ...;
> :
> COMMIT;
> BEGIN;
> INSERT INTO tableB VALUES ...;
> INSERT INTO tableB VALUES ...;
> INSERT INTO tableB VALUES ...;
> :
> COMMIT;
>
>Yesterday I did the former, it seemed to take ages. Today I use the
>latter ... it seems to be faster?!
You could probably mix the two, by inserting into tableA all the values
required, then inserting into tableB second, all in a single transaction:
BEGIN;
INSERT INTO tableA VALUES ...;
INSERT INTO tableA VALUES ...;
INSERT INTO tableA VALUES ...;
:
INSERT INTO tableB VALUES ...;
INSERT INTO tableB VALUES ...;
INSERT INTO tableB VALUES ...;
:
COMMIT;
Given the large number of inserts, the above will not save much on the
second example, as we're saving a single sequence of synchronous I/O. But
it will also keep all the inserts atomic with respect to each other.
I think the second is faster due to cache thrashing perhaps? You're
switching the page cache from tableA's working set to tableB's working set
back and forth. Doing all of tableA's inserts followed by tableB's inserts
utilises the cache better. Increasing the cache size may also improve
performance:
PRAGMA cache_size = <Number-of-pages>;
Another thing to look out for, if generating inserts in roughly the
correct index order, create the index after inserting all the values.
Inserting in index order will generate worst case index BTree maintenance,
as tree nodes will be continually being rebalanced. I think this is why
it's quicker to create the index afterwards as suggested in other posts.
>
>
>Many thanks for your replies, everyone =)
>
> Daniel
>
--
/"\
\ / ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
X - AGAINST MS ATTACHMENTS
/ \
