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.
Did you put an index on the table/columns the select uses to lookup the rowids of the parents?
