On Tue, 8 Feb 2005, Chris Schirlinger wrote:
>> I did a small test to see if performance was linear with time.
>> I wanted to make sure it was suitable for my application.
>> It seems with both indexed and unindexed tables it doesn't take
>> significantly longer to do the 1,000,000th insert than it did the
>> first.
>
>I've discovered there are oodles of factors with this sort of thing,
>all with depend on what you actually want to do. With 2 million rows,
>a simple select statement (select * from wibble where key = 1)
>returning 2000 records out of a dataset of 2 million+, takes between
>3 and 10 seconds
Doing a keyed search is no guarantee that you won't touch *every* single
page in the table, if the rows are inserted in random order. Try this:
sqlite> create table wibble2 as select * from wibble;
sqlite> delete from wibble;
sqlite> insert into wibble select * from wibble2 order by key;
sqlite> drop table wibble2;
Assuming key is the key field you want, the records will be inserted into
wibble in key order. Selecting by key will then touch the least number of
pages, speeding up the select.
>
>The *SECOND* time you call this, it's instant due mostly to SQLites
>caching and HDD caching, however in our case, the 10 second wait at
>the start was a major issue
What do you expect? SQLite can't second guess what might be needed and
load pages in the background ready for use.
>
>The only way we could get correct test results for our purposes was
>to clean boot between every test, and then the results are
>depressing. Still trying to get past this
>
Why? Does you program require the machine to be rebooted before use?
I'm not trying to be facetious, but your test seem very invalid without
further explanation.
Christian
--
/"\
\ / ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
X - AGAINST MS ATTACHMENTS
/ \
