[sqlite] Re: query 31 days

[Tom Deblauwe]

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,

[sqlite] Re: query 31 days

[Tom Deblauwe]

Ramon wrote:

Have you tried putting the yearmonth in 2 different columns, not sure if it 
will be
any faster, but I think it will, due to being a lot of them repeated.
Check you bandwidth and see if it's enough for your web users to access this 
query
remotely.
Peace



I will try this, thanx for the tip.

Kind regards,
Tom,

[sqlite] Re: query 31 days

[Tom Deblauwe]

Tom Deblauwe wrote:

Hello,


[snip]speed problem[/snip]


For the purpose of testing the existance of a record, it seems that
using this statement:

select date from mytable where yearmonth = 200508 and
date = 1 limit 1;

is slower than this:

select 1 from mytable where yearmonth = 200508 and
date = 1 limit 1;

This is because no lookup for the column 'date' in the main table is
done in the second case, which reduced the query from 2.6 seconds to 0.95

On a sidenote: maybe this can be put in the documentation, optimizing
queries?  Also maybe something to put there: queries go a LOT faster
when not sorting using 'order by'.  Maybe this is obvious, but it
wouldn't hurt to put it on that page, because I hadn't thought of it at
first.

Thanks for your time,
kind regards,
Tom,