one simple trick I first started using with oracle and also use with
sqlite is to do the following on the where clause
for numerics
where filda = fldb+0
for strings
where flda = fldb||""
this would cause a index on fldb to be ignored during optimization
Jim
Dennis Cote wrote:
On 2/18/06, SanjayK <[EMAIL PROTECTED]> wrote:
I am using a virtual tree control to display columns dynamically when
needed
from the sqlite database. While it works ok for display purposes, in
certain
other operations, I need to improve the speed. I found that the new sqlite
random access (even with prepared/transaction) approach is about 15 times
slower than my earlier design where I was using a direct access file with
read, seek, etc on Windows.
In spite of this, sqlite has several advantages and I am staying with it.
I
am looking for speed improvement suggestions. Somewhere in a thread I read
"disable indexing." I can't find any reference to how to do it in the docs
or in this group. How do I disable indexing? I will also appreciate any
other suggestions for speed improvement too.
Basically, these random access routines are very simple:
* Use a prepared read statement to get one column value directly.
* I am using a generic read statement that reads all columns desired but I
directly get only one column value after the Step. I tried to prepare a
single column read statement but that didn't help much over the generic
read.
Sanjay,
The idea of disabling indexing to improve speed only applies to writing the
database. SQLite must update each index for every record that is inserted,
so if you delete the indexes you reduce the amount of work (and I/O) that
must be done to add a record. Removing indexes won't speed up queries, and
it might greatly slow them down if you delete an index that is being used to
accelerate your query.
HTH
Dennis Cote
