[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

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 <max.vla...@gmail.com>: > On Fri, Jul 22, 2011 at 2:38 PM, Григорий Григоренко <grigore...@mail.ru> > wrote: > > I have a log's database. Schema : > > > > Query: &g

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

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

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

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

erformance... 22 июля 2011, 17:07 от Max Vlasov <max.vla...@gmail.com>: > On Fri, Jul 22, 2011 at 3:50 PM, Григорий Григоренко <grigore...@mail.ru> > wrote: > > Okay, I rebooted and tested again. > > > > First run took 76 seconds. Read bytes: ~ 307 Mb, Write byte

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

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 <d...@sqlite.org>: > On Fri, Jul 22, 2011 at 9:48 AM, Григорий Григоренко > <grigore...@mail.ru>wrote: > &g

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

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

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] EXT :Re: SELECT query first run is VERY slow

> > 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 Григорий Григоренко [grigor

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

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

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

nd='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 > > >

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

> 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

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

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

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

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

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

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

[sqlite] A question about prepared statements

Hello, I am using prepared stmts to cache selected record for later use. I have a wrapper class, that has methods like AsString(name), AsFloat(name) etc to extract data for a column name. I prepare, bind and then do Sqlite3_step() to get record. Later I use sqlite3_column_XXX() to access

Re: [sqlite] A question about prepared statements

Fri, 19 Oct 2012 15:49:07 -0400 от "Igor Tandetnik" <itandet...@mvps.org>: >Григорий Григоренко <grigore...@mail.ru> wrote: > > I am using prepared stmts to cache selected record for later use. I have a > wrapper class, that has methods like AsString(name)

Re: [sqlite] A question about prepared statements

ng such prepared stmt not closed? > > I think table client cannot be dropped altogether until all statements > using it are reset/finalized. > > > Pavel > > > On Fri, Oct 19, 2012 at 12:49 PM, Igor Tandetnik <itandet...@mvps.org> wrote: > > Григорий Григоренко <gri

[sqlite] SUGGESTION: "now" as alias for "strftime('%s','now')"

Hello, it is a common practice to store datetime values as UNIX time UTC.  Maybe, Sqlite should have some shortcut for evaluating current moment? Some alias for strftime('%s','now') ? Like, "now" or "unixnow":   created_at   DEFAULT  (now())     vs     created_at    DEFAULT  

Re: [sqlite] SUGGESTION: "now" as alias for "strftime('%s','now')"

Thu, 1 Nov 2012 19:57:42 + от Simon Slavin <slav...@bigfraud.org>: > On 1 Nov 2012, at 7:55pm, Григорий Григоренко <grigore...@mail.ru> wrote: > > > it is a common practice to store datetime values as UNIX time UTC. > > Maybe, Sqlite should have some shortcut

Re: [sqlite] SUGGESTION: "now" as alias for "strftime('%s','now')"

Fri, 02 Nov 2012 15:32:44 +0100 от Clemens Ladisch : >Igor Tandetnik wrote: > > SQLite does in fact accept CURRENT_TIMESTAMP in DEFAULT clause. > > > > http://sqlite.org/lang_createtable.html#tablecoldef > > SQLite does in fact accept CURRENT_TIMESTAMP anywhere:

Re: [sqlite] SUGGESTION: "now" as alias for "strftime('%s','now')"

Fri, 2 Nov 2012 10:25:18 -0400 от Richard Hipp : > > >On Fri, Nov 2, 2012 at 10:18 AM, Simon Davies >wrote: > datetime() will give current date and time >> likewise date(), time() etc >> >If I read the original post correctly, I think the OP is

Re: [sqlite] SUGGESTION: "now" as alias for "strftime('%s','now')"

GEOINT Solutions Operating Unit > Northrop Grumman Information Systems > > > From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Григорий Григоренко [grigore...@mail.ru] > Sent: Friday, November 02, 2012 8:08 AM > To: General Discussio

Re: [sqlite] Please help. Read only process being blocked by writer process.

Hi, CREATE TABLE rev(current); INSERT INTO rev VALUES(0); CREATE TABLE data(..., revision); Readers: SELECT * FROM data JOIN rev ON revision = current WHERE ... ; // or "SELECT current FROM rev" into var and passing it value in "SELECT * FROM data WHERE revision=?" Writer: // insert new

[sqlite] sqlite_interrupt() does not cancel ATTACHing of blocked database

RRUPT error, perhaps? Platform: Windows 7 x64, SQLite 3.7.16.2 -- Григорий Григоренко ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Foreign Key constraint problem while dropping tables inside transaction

': SAVEPOINT edit; PRAGMA FOREIGN_KEYS=0; DROP TABLE cat; DROP TABLE owner; RELEASE edit; PRAGMA FOREIGN_KEYS=1; This script works OK: PRAGMA FOREIGN_KEYS=0; DROP TABLE cat; DROP TABLE owner; PRAGMA FOREIGN_KEYS=1; Why? --  Григорий Григоренко ___ sqlite

Re: [sqlite] Foreign Key constraint problem while dropping tables inside transaction

Понедельник, 13 мая 2013, 17:03 +01:00 от Simon Davies <simon.james.dav...@gmail.com>: >On 13 May 2013 16:52, Simon Slavin < slav...@bigfraud.org > wrote: >> >> On 13 May 2013, at 3:54pm, Григорий Григоренко < grigore...@mail.ru > wrote: >> >>> s