Disable index?

[SanjayK]

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.

--
View this message in context: http://www.nabble.com/Disable-index--t1148798.html#a3012557
Sent from the SQLite forum at Nabble.com.

[Dennis Cote]

Post by SanjayK
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.
* 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

[Jim Dodgen]

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

Post by Dennis Cote

Post by SanjayK
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.
* 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

[d***@public.gmane.org]

Post by Jim Dodgen
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

That trick works, but it imposes a run-time overhead because
SQLite actually has to evaluate the +0 and the ||"". If you
just say:

where filda = +flda

The unary "+" operator is a no-op in SQLite, it works with both
numbers and strings, and there is no run-time penalty (other than
the fact that an index will not be used.) On the other hand,
I have no idea if oracle supports a unary "+" operator or not.
--
D. Richard Hipp <***@hwaci.com>

[Nemanja Corlija]

Post by SanjayK
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.

Disabling index might come in handy in some cases. For example, I was
able to speedup one query a lot by disabling index for sorting. For
some reason SQLite 2 was going back to disk to sort by index even
though it had all data needed in result set already. You can disable
the index like this:
SELECT * FROM foo ORDER BY +bar;

where bar is your indexed column.

If that still doesn't help, you'll have much better chance of getting
some help here on the list if you post more info. Such as SQLite
version used, schema of tables involved in query and query it self.
EXPLAIN's output for that query wouldn't hurt also. If you're using
some wrapper it would be good to run that query from sqlite shell and
see how fast that goes.

--
Nemanja Corlija <chorlya-***@public.gmane.org>

[Bert Verhees]

Post by Nemanja Corlija

Post by SanjayK
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.

Disabling index might come in handy in some cases. For example, I was
able to speedup one query a lot by disabling index for sorting. For
some reason SQLite 2 was going back to disk to sort by index even
though it had all data needed in result set already. You can disable
SELECT * FROM foo ORDER BY +bar;

I used a lot of the opcode from 2.8.x (forgot exact which one)
It did not use an index on sorting, but it sorted the result-set

Post by Nemanja Corlija
where bar is your indexed column.
If that still doesn't help, you'll have much better chance of getting
some help here on the list if you post more info. Such as SQLite
version used, schema of tables involved in query and query it self.
EXPLAIN's output for that query wouldn't hurt also. If you're using
some wrapper it would be good to run that query from sqlite shell and
see how fast that goes.
--