Re: [sqlite] SELECT query #first run# is very slow VOL2

On 19 Dec 2017, at 8:37pm, zakari wrote: > pasting some logs, Im declaring again this happening only the first time, > afterwards working without problem. > 2017-12-17 15:16:23 - execute > 2017-12-17 15:17:20 - executed > > 2017-12-19 14:53:35 - execute > 2017-12-19

[sqlite] SELECT query #first run# is very slow VOL2

hi all, I have exactly the same problem with topic : http://sqlite.1065341.n5.nabble.com/SELECT-query-first-run-is-VERY-slow-td33100i20.html -- The dbase sitting on linux server, Im accessing the dbase with PDO object. -connected -prepare the statement -execute here makes =>1min lag, *only the

Re: [sqlite] SELECT query first run is VERY slow

On Thu, Jul 28, 2011 at 11:41 AM, Григорий Григоренко wrote: > So, should we file this as a defect or bug somehow? > > As I understand currently the issues acknowledged and fixed during the e-mail conversation in this list ( read http://www.sqlite.org/src/wiki?name=Bug+Reports

Re: [sqlite] SELECT query first run is VERY slow

So, should we file this as a defect or bug somehow? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SELECT query first run is VERY slow

On Tue, Jul 26, 2011 at 05:13:00PM +, Black, Michael (IS) scratched on the wall: > Part of the problem is it seems you can't create an index with rowid: > > > > 3.7.5 > > sqlite> create table t(i int); > sqlite> create index idx1 on t(i); > sqlite> create index idx2 on t(i,rowid); >

Re: [sqlite] SELECT query first run is VERY slow

On 26-07-2011 10:30, Григорий Григоренко wrote: >> >> I thought that, if you have an index on a,b,c,d >> than you should not have an index on a,b,c too >> because if you use those 3 field in the where-clause, use can be made of >> the 4-field index >> > > I'm not sure. Let me explain. > > I need

Re: [sqlite] SELECT query first run is VERY slow

> > > > > EXPLAIN QUERY PLAN SELECT * FROM foo WHERE bar = 'three' and id > 290 > > LIMIT 10; > > > > SELECT * FROM foo WHERE bar = 'three' and id > 290 LIMIT 10; > > > > > > Run query. Now using idx2 index SQLITE reads only about 20 Kbytes! > > > > > > > Grigory, it seems you just added

Re: [sqlite] SELECT query first run is VERY slow

On 26 Jul 2011, at 6:13pm, Black, Michael (IS) wrote: > Part of the problem is it seems you can't create an index with rowid: > > > > 3.7.5 > > sqlite> create table t(i int); > sqlite> create index idx1 on t(i); > sqlite> create index idx2 on t(i,rowid); > Error: table t has no column named

Re: [sqlite] SELECT query first run is VERY slow

On Tue, Jul 26, 2011 at 8:50 PM, Григорий Григоренко wrote: > > EXPLAIN QUERY PLAN SELECT * FROM foo WHERE bar = 'three' and id > 290 > LIMIT 10; > > SELECT * FROM foo WHERE bar = 'three' and id > 290 LIMIT 10; > > > Run query. Now using idx2 index SQLITE reads only

Re: [sqlite] SELECT query first run is VERY slow

: General Discussion of SQLite Database Subject: EXT :Re: [sqlite]SELECT query first run is VERY slow I found some kind of workaround to solve this problem. Create new database and run: CREATE TABLE foo(bar); INSERT INTO foo VALUES(null); INSERT INTO foo VALUES(null); INSERT INTO foo VA

Re: [sqlite] SELECT query first run is VERY slow

nalytics Directorate > > > > > From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on > behalf of Григорий Григоренко [grigore...@mail.ru] > Sent: Tuesday, July 26, 2011 8:16 AM > To: General Discussion of SQLite Da

Re: [sqlite] SELECT query first run is VERY slow

I found some kind of workaround to solve this problem. Create new database and run: CREATE TABLE foo(bar); INSERT INTO foo VALUES(null); INSERT INTO foo VALUES(null); INSERT INTO foo VALUES(null); BEGIN; INSERT INTO foo SELECT bar FROM foo; INSERT INTO foo SELECT bar FROM foo; INSERT

Re: [sqlite] SELECT query first run is VERY slow

If I run your sql.txt script with the normalization of kind my first time query shows 0 seconds. D:\x>sqlite3 sq1 > select count(*) as cnt,kind,computer > from log > group by kind,computer > order by cnt desc > > what happens here? > > SELECT * > FROM log INDEXED BY idxlog_kind_computer >

Re: [sqlite] SELECT query first run is VERY slow

On 26 Jul 2011, at 1:56pm, wrote: > How to write a trigger [snip] Nirmala, please start a new thread with your new query. Do not intrude into another person's thread. Simon. ___ sqlite-users

Re: [sqlite] SELECT query first run is VERY slow

2011/7/26 Black, Michael (IS) > > Turns out the if you include the primary key in an index it doesn't use the > triple index but uses the primary key instead. And analyze doesn't change > it. > > This is with version 3.7.5 > > Not sure about the primary index , because

Re: [sqlite] SELECT query first run is VERY slow

> > select count(*) as cnt,kind,computer > from log > group by kind,computer > order by cnt desc > > what happens here? > > SELECT * > FROM log INDEXED BY idxlog_kind_computer > WHERE kind = 'info' AND computer=1 and id > 7070636 > LIMIT 100; > > there are 3_022_148 identical entries

Re: [sqlite] SELECT query first run is VERY slow

Behalf Of ?? Sent: Tuesday, July 26, 2011 6:04 PM To: Black, Michael (IS) Cc: General Discussion of SQLite Database Subject: Re: [sqlite] SELECT query first run is VERY slow > Could you post your timings and read stats again? Are you happy with what > you're seeing now?

Re: [sqlite] SELECT query first run is VERY slow

26 июля 2011, 16:42 от res...@googlemail.com: > Think about the distribution of your Data. > > select count(*) as cnt,kind,computer > from log > group by kind,computer > order by cnt desc > > what happens here? > > SELECT * > FROM log INDEXED BY idxlog_kind_computer > WHERE kind = 'info'

Re: [sqlite] SELECT query first run is VERY slow

> >> >> Then see how long your first query takes. >> >> >> >> Michael D. Black >> >> Senior Scientist >> >> NG Information Systems >> >> Advanced Analytics Directorate >> >> >> >> ___

Re: [sqlite] SELECT query first run is VERY slow

ers-boun...@sqlite.org] on behalf of Max Vlasov [max.vla...@gmail.com] Sent: Tuesday, July 26, 2011 6:50 AM To: General Discussion of SQLite Database Subject: EXT :Re: [sqlite] SELECT query first run is VERY slow On Tue, Jul 26, 2011 at 2:18 PM, Simon Slavin <slav...@bigfraud.org> wrote:

Re: [sqlite] SELECT query first run is VERY slow

> Could you post your timings and read stats again? Are you happy with what > you're seeing now? > Actually, there was ~ 50% speed-up. More or less. The idea of normalizing worked great, thank you. I'm concerned about SQLITE indexes. > > > I think when you drop the index and recreate

Re: [sqlite] SELECT query first run is VERY slow

On Tue, Jul 26, 2011 at 2:18 PM, Simon Slavin wrote: > > On 26 Jul 2011, at 11:05am, Григорий Григоренко wrote: > > > This leads us to conclusion: index in SQLITE database if scattered and > cannot be "jumped directly" to N-th element. SQLITE has to read it somehow >

Re: [sqlite] SELECT query first run is VERY slow

On 26 Jul 2011, at 11:05am, Григорий Григоренко wrote: > This leads us to conclusion: index in SQLITE database if scattered and cannot > be "jumped directly" to N-th element. SQLITE has to read it somehow > consecutively. > > And so SQLITE has to read half of index (!) to find matching index

Re: [sqlite] SELECT query first run is VERY slow

> > > I need query to be ORDER BY id DESC. I've dropped this ORDER BY to simplify > > my case. But in real-life app I need it. > > > > So, index on (kind, computer) has these index records: > > > > [ KIND ] [ COMPUTER ] [ ID ] > > I don't know that SQLite does an inherent addition of the 'id'

Re: [sqlite] SELECT query first run is VERY slow

; > > 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 r

Re: [sqlite] SELECT query first run is VERY slow

On 26 Jul 2011, at 9:30am, Григорий Григоренко wrote: > I need query to be ORDER BY id DESC. I've dropped this ORDER BY to simplify > my case. But in real-life app I need it. > > So, index on (kind, computer) has these index records: > > [ KIND ] [ COMPUTER ] [ ID ] I don't know that SQLite

Re: [sqlite] SELECT query first run is VERY slow

> > > > 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. > > > > Please, post your query. To understand whether

Re: [sqlite] SELECT query first run is VERY slow

> > I thought that, if you have an index on a,b,c,d > than you should not have an index on a,b,c too > because if you use those 3 field in the where-clause, use can be made of > the 4-field index > I'm not sure. Let me explain. I need query to be ORDER BY id DESC. I've dropped this ORDER BY to

Re: [sqlite] SELECT query first run is VERY slow

On Mon, Jul 25, 2011 at 5:45 PM, Григорий Григоренко wrote: > > 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

Re: [sqlite] SELECT query first run is VERY slow

l.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

Re: [sqlite] SELECT query first run is VERY slow

On 25-07-2011 15:45, Григорий Григоренко wrote: > 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. I thought that, if

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

Re: [sqlite] SELECT query first run is VERY slow

Okay, here are some steps I've done. 1) normalize db; I've created single table (item). "computer","process" and "who" fields in log table became rowid integers pointing to this table. "kind" is still a short string. 2) give up covering indexes; For those not aware

Re: [sqlite] SELECT query first run is VERY slow

ANALYZE lasted for ~ 15 minutes. 24 июля 2011, 17:21 от Tito Ciuro : > Hi, > > It has worked fairly well with small databases, but I see the problem with > medium to large files. Have you tried to run ANALYZE on your database? I'm > curious to know how long it takes. > > --

Re: [sqlite] SELECT query first run is VERY slow

Hi, It has worked fairly well with small databases, but I see the problem with medium to large files. Have you tried to run ANALYZE on your database? I'm curious to know how long it takes. -- Tito On Jul 24, 2011, at 8:26 AM, Григорий Григоренко wrote: >> >> Perhaps my post dated Aug. 19,

Re: [sqlite] SELECT query first run is VERY slow

> > Perhaps my post dated Aug. 19, 2009 will help a little bit: > > http://www.cocoabuilder.com/archive/cocoa/242954-core-data-dog-slow-when-using-first-time-after-boot.html > > -- Tito > Thanks for sharing. "warming file" is a way to cache whole database as I understand it. After

Re: [sqlite] SELECT query first run is VERY slow

Thank you for explaining this. I guess you're right about query planner deciding to avoid index usage based on stats. 22 июля 2011, 18:30 от Richard Hipp : > On Fri, Jul 22, 2011 at 9:48 AM, Григорий Григоренко > wrote: > > > > > > > Please post the

Re: [sqlite] SELECT query first run is VERY slow

Hello all, Perhaps my post dated Aug. 19, 2009 will help a little bit: http://www.cocoabuilder.com/archive/cocoa/242954-core-data-dog-slow-when-using-first-time-after-boot.html -- Tito On Jul 22, 2011, at 10:40 AM, Simon Slavin wrote: > > On 22 Jul 2011, at 2:11pm, Григорий Григоренко wrote:

Re: [sqlite] SELECT query first run is VERY slow

On Fri, Jul 22, 2011 at 9:48 AM, Григорий Григоренко wrote: > > > > Please post the results of: > > > > SELECT * FROM sqlite_stat1; > > > > > tbl = log > idx = idxlog_kind_computer_process_who_id_msg_created_at > stat = 2815667 563134 563134 469278 74097 1 1 1 > The

Re: [sqlite] SELECT query first run is VERY slow

> > > > > > Something that might speed up your operation is to issue the SQL command > > 'ANALYZE' just once. The results are stored in the database file, so you > > can just do it manually now you have some data in the database. It gives > > the query optimizers lots of clues about how best

Re: [sqlite] SELECT query first run is VERY slow

On Fri, Jul 22, 2011 at 5:25 PM, Григорий Григоренко wrote: > Database is "insert-only". There wasn't any deletes or updates, will VACUUM > actually help in this case? I though it was about unused space? > There's also internal fragmentation coming from the nature of

Re: [sqlite] SELECT query first run is VERY slow

On 22 Jul 2011, at 2:11pm, Григорий Григоренко wrote: > Let's say it'll shrink by 50%. Still, if first-time runnning query timing > will change from 2 min to 1 min it is still not acceptable. > I cannot wait even a minute without logging. > > So, before restructuring database and re-writing

Re: [sqlite] SELECT query first run is VERY slow

Database is "insert-only". There wasn't any deletes or updates, will VACUUM actually help in this case? I though it was about unused space? Still. There's an index: idxlog_kind_computer_id_process_who_msg_created_at ( kind, computer, id, ... ) Query is kind = XXX AND computer = YYY and

Re: [sqlite] SELECT query first run is VERY slow

On Fri, Jul 22, 2011 at 8:55 AM, Григорий Григоренко wrote: > > > > > > > Something that might speed up your operation is to issue the SQL command > 'ANALYZE' just once. The results are stored in the database file, so you > can just do it manually now you have some data in

Re: [sqlite] SELECT query first run is VERY slow

You are right. There's a way of normalizing and it will certainly reduce database size. Let's say it'll shrink by 50%. Still, if first-time runnning query timing will change from 2 min to 1 min it is still not acceptable. I cannot wait even a minute without logging. So, before restructuring

Re: [sqlite] SELECT query first run is VERY slow

On Fri, Jul 22, 2011 at 3:50 PM, Григорий Григоренко wrote: > Okay, I rebooted and tested again. > > First run took 76 seconds. Read bytes: ~ 307 Mb, Write bytes: ~ 66 Kb. > > Re-running: 1 second, Read bytes: ~ 307 Mb, Write bytes ~ 66 Kb. > > Grigory, you posted to me

Re: [sqlite] SELECT query first run is VERY slow

> > Something that might speed up your operation is to issue the SQL command > 'ANALYZE' just once. The results are stored in the database file, so you can > just do it manually now you have some data in the database. It gives the > query optimizers lots of clues about how best to

Re: [sqlite] SELECT query first run is VERY slow

ite-users@sqlite.org Subject: EXT :[sqlite] SELECT query first run is VERY slow I have a log's database. Schema : CREATE TABLE log(id integer primary key autoincrement,msg text,created_at int,kind text,computer text,process text,who text); CREATE INDEX idxlog_created_at ON log(creat

Re: [sqlite] SELECT query first run is VERY slow

> > > Database extension is ".dblite" > > That should be okay. No need to change that. > > Something that might speed up your operation is to issue the SQL command > 'ANALYZE' just once. The results are stored in the database file, so you can > just do it manually now you have some data in

Re: [sqlite] SELECT query first run is VERY slow

On 22 Jul 2011, at 12:36pm, Григорий Григоренко wrote: > Database extension is ".dblite" That should be okay. No need to change that. Something that might speed up your operation is to issue the SQL command 'ANALYZE' just once. The results are stored in the database file, so you can just

Re: [sqlite] SELECT query first run is VERY slow

On Fri, Jul 22, 2011 at 3:36 PM, Григорий Григоренко wrote: > Database extension is ".dblite" > > I'm using Process Explorer > (http://technet.microsoft.com/en-us/sysinternals/bb896653) to monitor > processes cpu and i/o usage. > > During these long running queries I am not

Re: [sqlite] SELECT query first run is VERY slow

On Fri, Jul 22, 2011 at 3:28 PM, Simon Slavin wrote: > > Does Windows XP have some sort of task display where you can see what task is > hogging most of the CPU or disk access ? > The mentioned Task Manager (Ctrl-Alt-Del -> Task Manager) reports both CPU and I/O Read Bytes

Re: [sqlite] SELECT query first run is VERY slow

Database extension is ".dblite" I'm using Process Explorer (http://technet.microsoft.com/en-us/sysinternals/bb896653) to monitor processes cpu and i/o usage. During these long running queries I am not using any other program. I've terminated any unused service (MS SQL, for example). System

Re: [sqlite] SELECT query first run is VERY slow

On 22 Jul 2011, at 12:15pm, Max Vlasov wrote: > Do you have and anitvirus software installed? It may scan the file at > the first usage. What is the extension to the filename of the database file ? Windows does fancy caching for files that have some particular extensions including, IIRC,

Re: [sqlite] SELECT query first run is VERY slow

I have antivirus (avast) but it's disabled during testing. 22 июля 2011, 15:15 от Max Vlasov : > On Fri, Jul 22, 2011 at 2:38 PM, Григорий Григоренко > wrote: > > I have a log's database. Schema : > > > > Query: > > > > SELECT 1 as today, id as rowid,

Re: [sqlite] SELECT query first run is VERY slow

On Fri, Jul 22, 2011 at 2:38 PM, Григорий Григоренко wrote: > I have a log's database. Schema : > > Query: > > SELECT 1 as today, id as rowid, kind,who,msg,computer,process,created_at,id > FROM log WHERE id > 4070636 AND id <= 9223372036854775807 AND kind = 'info' > AND

[sqlite] SELECT query first run is VERY slow

I have a log's database. Schema : CREATE TABLE log(id integer primary key autoincrement,msg text,created_at int,kind text,computer text,process text,who text); CREATE INDEX idxlog_created_at ON log(created_at); CREATE INDEX idxlog_kind_computer_id_process_who_msg_created_at ON