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',
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
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 sqlite reads
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 does
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.
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' column to all
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
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
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 shane.harrel...@gmail.com
The makefile builds lemon.exe from lemon.c as part of the build process, so
make sure it
Hey people let's consider provide 64bit binaries in sqlite.org
2011/7/26 Everton Vieira tonvie...@gmail.com
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
On Tue, Jul 26, 2011 at 2:18 PM, Simon Slavin slav...@bigfraud.org 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
consecutively.
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 it
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 stdio.h
main()
{
int i;
for(i=1;i=10;++i) {
char sql[4096];
sprintf(sql,insert into abctable(a,b,c)
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
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' AND computer=1
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
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 'info,1' in your
2011/7/26 Black, Michael (IS) michael.bla...@ngc.com
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 with my
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, second,
On 26 Jul 2011, at 1:56pm, narmada.jamm...@wipro.com
narmada.jamm...@wipro.com 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
If I run your sql.txt script with the normalization of kind my first time query
shows 0 seconds.
D:\xsqlite3 sq1 sql.txt
0|0|0|SEARCH TABLE log USING INDEX idxlog_kind_computer (kind=? AND computer=?)
(~406234 rows)
Seconds elapsed: 0
Does yours show a longer time than that and/or a
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
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
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?
Michael
On Tue, Jul 26, 2011 at 8:50 PM, Григорий Григоренко grigore...@mail.ruwrote:
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
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 rowid
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 a field and copied rowid
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:
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 query to be
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 not
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 easier
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
-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 as
33 matches
Mail list logo
