I used the explain command and it uses the right index. Also I work on
embedded and I like to keep the memory requirements low, so I can't
afford to use these pragma commands.
greetz,
Tom,
Khamis Abuelkomboz wrote:
Hi
How many records contain your table? Your select command scans all the
table entries and I guess that the database uses the standard index. try
the following command to tell the db which index it should use:
select distinct date from mytable where yearmonth = '200508' order by date
Another possibility for this slowness is may be your database memory and
temp store usage configuration. the standard memory configuration of
release 2.8.16 is very slow, I had some performance problems until I had
changed the memory usage configuration. I use the following configuration:
PRAGMA cache_size = 6000
PRAGMA default_synchronous = OFF
PRAGMA synchronous = OFF
PRAGMA default_temp_store = MEMORY
PRAGMA temp_store = MEMORY
PRAGMA show_datatypes = OFF
good luk
khamis
Tom Deblauwe wrote:
Hello,
I'm using SQLite 2.8.16, on an embedded system, and I have a question.
I have a database with one table, containing all my records. Each has a
field 'yearmonth' which contains for example '200508', and also a field
'date', which indicates the day of that month. I want now to make an
overview of a month and see which days contain a record. I've created a
combined index on 'yearmonth' and 'date'. My query to do this is:
'select distinct date from mytable where yearmonth = 200508;' This
takes about 50 seconds, which is really slow to show my users(it is
displayed on a webpage).
I noticed that this takes a long time, because I guess almost all
records are checked. My other idea was to do 31 queries(one for every
day), like this: 'select ID from mytable where yearmonth = 200508 and
date = 1 limit 1;'. This second method does not give me anymore speed
improvement.
for example:
1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | ...
^ ^ ^ ^
the days marked with '^' contain records. To check with the second
method for example day 7, it takes 2.6 seconds. Day 4 takes less, and
from day 3 and lower, it is reacting immediately(like 40 ms). Day 8 and
higher takes about 1 second each.
When I do a random query om my database with select, and want to return
10 results for example, it takes 2.8 seconds. So getting 1 result, or
getting 10 results only differs about 0.2 seconds. Is there anything I
can do so things go faster? Maybe I should maintain a separate table
with the monthoverview, but if it is possible to do some other solution
it would be nicer ;-).
Thanks for your time,
kind regards,
Tom,
