I've got a 6 million row DB in SQLite 3, but getting... odd results
which don't match up with the speed tests I've seen
The statement:
SELECT * FROM myTable WHERE myKey=1000
takes between 1 second to 4 or 5 on spikes. The returned result set
is ~2000 records. I havn't seen more than 2000 recs/second usually
less
Similarly, the query (A basic APPEND or INSERT)
INSERT OR REPLACE INTO myTable (myKey, NumOne, NumTwo, NumThree,
NumFour, NumFive, NumSix, NumSeven) VALUES (1000, 1, 2, 3, 4, 5, 6,
7)
is doing at MOST about 300 records per second and at worst 100 a
second. I have about 3000 inserts/updates all wrapped inside a single
Transaction unless doing a complete population of the DB in which
case it is batched but still all wrapped in transactions
The schema is VERY basic:
CREATE TABLE [myTable] (
[myKey] [bigint] NULL ,
[NumOne] [int] NULL ,
[NumTwo] [real] NULL ,
[NumThree] [real] NULL ,
[NumFour] [real] NULL ,
[NumFive] [real] NULL ,
[NumSix] [float] NULL ,
[NumSeven] [float] NULL
);
CREATE INDEX myMyIndex ON myTable (myKey);
CREATE UNIQUE INDEX [pkPrimary] ON [myTable] ([myKey], [NumOne]);
CREATE INDEX myNumOneIndex ON myTable (NumOne);
Now initially I didn't have anything except the UNIQUE index, though
adding the second two hasn't made any difference once way or the
other
Personalyl I'd LOVE for that UNIQUE index to be a clustered index (it
was elsewhere) but that just doesn't seem to be an option
Any ideas where I am going wrong here? Or are these the numbers I am
expected to see?
(Note: I am using transactions in case I didn't make that clear, I am
also doing this in Delphi using the open source Aducom.nl componants,
but at the raw end it seems their code is mostly fairly close to the
bare bones of the DLL exported functions. Doesn't seem to be an issue
there but who knows)
