On Thu, 9 Sep 2004, Steven Van Ingelgem wrote:
>I just noticed something rather stupid...
>
>when you .dump a table via the sqlite.exe binary (2.8.15)... It dumps first
>the "create table", then the "insert"s, and afterwards the indexes....
>
>Now if you have a very big table it will take a LOT of time to place those
>indexes... Maybe it is more performant to place the "create index" just
>after the "create table" statement?
Try benchmarking it yourself. You'll probably find that it is in fact
quicker to insert the data without indexes, and then index.
This is because indexing isn't free. It impacts insert and potentially
update performance, as the index may also have to be updated with each
update, and will certainly need to be updated with each insert.
A quick test (374245 rows, single column with index.) With the index
created after the inserts:
$ cat test.dump.1 | time -p sqlite test.1
real 22.67
user 17.59
sys 1.27
With index created before the inserts:
$ cat test.dump.2 | time -p sqlite test.2
real 24.17
user 18.99
sys 1.50
Repeated the test with similar results, so the post-indexing is about 1.5
seconds quicker in this case.
>
>Greetings,
>
>KaReL (aka Steven)
>
Christian
--
/"\
\ / ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
X - AGAINST MS ATTACHMENTS
/ \
