Hi!
I have recently converted a "problem application" from MS-Access to
SQLite
in Visual Basic but the performance is really worse then with Access.
I have not
changed much in the application code except for a few things that
should be in favour
to sqlite, like implementing a second "in memory" database for
temporary data and disc
based write-to db.
Has anyone tested and concluded that sqlite is faster for small
databases (<5000 records)
so I don't sit here in vain, trying to optimize for something that
just can't be done?
Inserts are done in a transaction (extremely good prestanda here),
selects are indexed (could this be a problem in a table with 2-3 000
records?) by
adding too much overhead compared to a non-indexed, sequential,
search?
I haven't done any measurements, but as you already notices, SQLite needs
some
help to be fast.
You already put your insert's in explicit transactions - good.
(Hopefully you did not write
begin transaction
insert ...
commit
begin transaction
insert ...
commit
...
but
begin transaction
insert ...
insert ...
...
commit
)
You use indexes - good.
But carefully check if the indexes are really used!
The SQL optimizer in SQLite is pretty simple. Sometimes you can write
a simple looking SQL statement that will not use indexes.
So check if your indexes are being used (use the .explain command of
the command line tool sqlite[.exe]).
Perhaps you have to change your queries a little.
For example:
select * from mytable where a like 'b%';
may be slow - even if a is an indexed column.
select * from mytable where a >= 'ba' and a <= 'bz';
may be very fast.
Some of those tips have been discussed on this list and
some tips are on the wiki.
Generally you don't need to use a second in-memory db
for temporary tables - SQLite will put tmp tables in
memory by default...
If you use indexes, write queries that force SQLite to
utilize these indexes and put your inserts and updates
in explicit larger transactions, SQLite should outperform
nearly everything else...
But I don't know the VB<->SQLite technology you use.
Maybe your wrapper unneccessarily converts much data
or is otherwise slow...
I hope this helps a bit,
Danny
--
Danny Reinhold
Reinhold Software & Services
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
