Re: [sqlite] New madIS v1.3 release

Why JPack? May be Tcl lists will be more useful? The tcl dictionary (also known as associative array) can be stored as list too. SELECT TCLCMD('dict', 'get', 'key 1 mykey 2', 'mykey'); 2 SELECT TCLCMD('lindex', 'key 1 mykey 2', 0); key SELECT TCLCMD('join', 'key 1 mykey 2',

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

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

Did that. Timings has decreased. As I understand it it's about decreasing index size (that includes kind column). To me the problem is still there. If my database will have 10 mln log records first running query will stuck again :( I don't understand SQLITE strategy. Let me explain.

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

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

[sqlite] trying to build from sqlite-3.6.7 on ubuntu 11.04, missing sqlite3.c

not sure if this is the appropriate forum for this but i'm hoping someone can give me some useful pointers. as part of an embedded system build, i'm building a number of tools for the *host* system, including sqlite-3.6.7 from the tarball (along with a few patches which i will be examining

Re: [sqlite] SQLite3.dll for Win 64

Done! Probably something with gawk was occurring because after i've copy all gawk files inside the folder of the source they work out. Thanks to all. 2011/7/25 Shane Harrelson > The makefile builds lemon.exe from lemon.c as part of the build process, so > make sure

Re: [sqlite] SQLite3.dll for Win 64

Hey people let's consider provide 64bit binaries in sqlite.org 2011/7/26 Everton Vieira > Done! Probably something with gawk was occurring because after i've copy > all gawk files inside the folder of the source they work out. > > Thanks to all. > > 2011/7/25 Shane

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

> 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

That's because they have 2 completely different query plans. I created the table so that id,a,b,c all had the same values so the indexing would be indentical. #include main() { int i; for(i=1;i<=10;++i) { char sql[4096]; sprintf(sql,"insert into abctable(a,b,c)

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

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' AND computer=1 and id > 7070636 LIMIT 100; there are 3_022_148 identical

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

Hi, How to write a trigger so that it will log the updates on all tables in database into a auditlog tables?. For Example: The database contains 3 tables 1)country 2) state 3) auditlog List of fields on each table Country table: Cid name Sate table: Sid name

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

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] New madIS v1.3 release

I've mainly used JSON because it is a well defined and widely used standard. JSON also contains associative arrays (which currently are not used in madIS). From what little i've read about Tcl lists, i believe that JSON lists are better for the eye. Compare this: ["this is the first",

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

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

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

It's all about caching. If OS has already cached index data query executes in less than a second time. To understand what is actually happening you should monitor reading count of SQLITE console process (or your app that is executing). There shouldn't be reading of more than 1 Mb if SQLITE is

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

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 rowid Any particular reason it can't be included in an index?

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

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

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

[sqlite] Possibility to use a in-mmeory database with two sqlite handles/connections

Hi, is there a way to create more then one sqlite connection hadles for the same in-memory database? I know that I could share the connection pointer, but I would prefer to to have differrent indipendent connections. There was a proposal:

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] Possibility to use a in-mmeory database with two sqlite handles/connections

> is there a way to create more then one sqlite connection hadles for the > same in-memory database? No. > There was a proposal: > http://www.mail-archive.com/sqlite-users@sqlite.org/msg35438.html > from Markus Lehmann. > Is this a safe sollution? I guess if it's not in the mainline SQLite it's

Re: [sqlite] trying to build from sqlite-3.6.7 on ubuntu 11.04, missing sqlite3.c

>  as part of an embedded system build, i'm building a number of tools > for the *host* system, including sqlite-3.6.7 from the tarball (along > with a few patches which i will be examining shortly). Apparently you are building from canonical sources. Why don't you use amalgamation? It's much

[sqlite] PRAGMA temp_store_directory not releasing resources

We've just introduced some memory leak detection into our code and have discovered that this pragma call is not having its resources cleaned up at shutdown. It's not a critical leak since it's only called once at program start, but it would be nice to have the system report zero memory leaks on

Re: [sqlite] trying to build from sqlite-3.6.7 on ubuntu 11.04, missing sqlite3.c

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 > after a quick inspection, i can see (i think) that part of the > configuration and build process is to *create* the sqlite3.c source > file to be used as part of the compilation, is that correct? Yes as Pavel mentioned. The single file is known