See in-line comments below
------------------------------
Jay Sprenkle wrote:
Did you put an index on the table/columns the select uses to lookup
the rowids of the parents?
I though that was what declaring 'str' as KEY and 'parent' as KEY was
supposed to do, but see Dennis Cote's response below:
Marian Olteanu wrote:
You should embed the inserts into a transaction. Otherwise every insert is a
separate ACID transaction = 2 disk spins.
I thought putting 128 of the inserts between "BEGIN;" and "COMMIT;" did
that. Am I confused?
Dennis Cote wrote:
Try this table definition instead.
CREATE TABLE xyz
(
str TEXT,
parent INTEGER,
PRIMARY KEY(parent, str)
);
This will create an index to greatly speed the lookups of the
existing rows when you do your inserts.
This may have been the secret (and what Jay was trying to tell me
above). I pulled out the 'KEY' and the 'NOT NULL' on str and parent,
and added the PRIMARY KEY. Performance started out somewhat higher,
around 1000 inserts per second and seems to have leveled off at 500+
inserts per second. I'm already up to 500,000 entries in my test. I'd
like even better performance, but this is passable. I have not started
the testing of my query code to see if there are any new problems.
Thanks
T
On 4/6/06, Thom Ericson <[EMAIL PROTECTED]> wrote:
I am trying to pick a light weight database for a project. SQLite, on
paper, seems like the right choice, but performance is rather
disappointing. I hope it is just that I am doing something wrong.
I have built SQLite for Solaris and Win32 environments and I get
essentially the same results. As I started adding more rows to my
single table the performance drops off (expected). I start off getting
about 500 inserts per second, but after 180,000 rows, the performance
has dropped to around 3 inserts per second. I had hoped to be able to
handle 180,000,000 rows in my largest installation (that's gonna take a
while). The table is very simple:
sqlite3 testdb << EOF
CREATE TABLE xyz
(
str TEXT KEY NOT NULL,
parent INTEGER KEY NOT NULL
);
The "str" column is typically 10 to 20 characters and the "parent"
column is the ROWID of some pre-existing row in the table (except for
the first row, where the parent is zero). For each new row added to the
table, there is, on average, one select performed that produces the
rowid of another entry given specific values for str and parent. I
enclose about 128 of these selects and insert within a
"BEGIN;"/"COMMIT;" block which increased performance, but going to
higher values does not seem to help much.
With the Solaris installation, I don't see much disk activity (possibly
all blocks are cached), on windows, I am seeing around 22,000 I/O Reads
per second.
