You need to normalize your "kind" value.


.pragma cache_size=15000;

drop index idxlog_kind_computer;

create table kind(id integer,kind text);
insert into kind values(1,'debug');
insert into kind values(2,'error');
insert into kind values(3,'info');
insert into kind values(4,'timing');
insert into kind values(5,'warn');
update log set kind=1 where kind='debug';
update log set kind=2 where kind='error';
update log set kind=3 where kind='info';
update log set kind=4 where kind='timing';
update log set kind=5 where kind='warn';
create index idxlog_kind_computer ON log(kind,computer);



Then see how long your first query takes.



Michael D. Black

Senior Scientist

NG Information Systems

Advanced Analytics Directorate



________________________________
From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Григорий Григоренко [grigore...@mail.ru]
Sent: Monday, July 25, 2011 8:45 AM
To: sqlite-users@sqlite.org
Subject: EXT :Re: [sqlite]SELECT query first run is VERY slow

I think I narrowed the problem a bit.  Guys, hope I'm not bothering you too 
much :)


I've calculated size of index (it is index on log (kind,computer) ) of its own: 
dropped index, run VACUUM and re-created index.

Database file increased by 105 Mb (and sqlite3 process counter shows that there 
were ~105 Mb written to disk).


This means that index on log(kind, computer) takes 105 Mb of database file (and 
whole size of database is 1259 Mb).


Now, I'm running query which is using this index (and is not returning any 
data) and monitor that sqlite3 process reads ~50 Mb.


So there are  two major problems here.

1) SQLITE has to read about _half of index_ before it can use it (and 
understand there are no records matching query).

If cache is enough to hold 50 Mb then on subsuquent queries sqlite process is 
not reading at all.

2) SQLITE is reading abnormally slowly during this first-time running query  
(waiting for something a lot?).

During index creation I monitored sqlite3 process and it was consuming CPU at 
~20% rate and it's doing I/O at ~10 Mb per second rate.
That's what I call "normal load"!














_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Reply via email to