[sqlite] Indexes on columns

[logan...@gmail.com]

Hello,

My understanding is that an index is automatically created on any column
that is used in the primary key (or a composite index is created if the key
is composed of different columns). If this is correct then why don't I see
indexes for those in my table (I'm using SQLite Administrator and Firefox
plugin based SQLite manager). I do see indexes for the columns that I added
a unique constraint upon.

Is the above just a GUI error in these tools or an index need to be created
separately on the columns used in primary keys?

Thanks,
Hitesh
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Query with UNION on large table

[Max Vlasov]

On Thu, Jun 23, 2011 at 10:20 PM, Rense Corten  wrote:

> Wow, I'm almost embarrassed by how helpful you are, gentlemen. Thanks a
> lot.
>
> As to RAM: I'm trying this on two different machines, one with 12 Gb
> and one with 32 Gb RAM. I won't be able to get more in the near
> future. Something that might be relevant is that the case of the 32Gb
> machine, the database is on an AFS.
>
>
Hmm, Jan suggested dedicating the memory to the cache, but if you can use
64-bit sqlite shell, and giving that you have 800 millions (or less) rows,
maybe it's reasonable to switch completely to memory temporary storage?
(PRAGMA temp_store=2;). In best scenario no disk will be involved until
sqlite will be merging two b-trees for your "create table as"

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

Re: [sqlite] Query with UNION on large table

[Martin Gadbois]

On Thu, Jun 23, 2011 at 5:59 PM, Simon Slavin  wrote:

>
> On 23 Jun 2011, at 10:56pm, Rense Corten wrote:
>
> > Simon: no, AFS=Andrew File System :). Both my servers run Ubuntu
> > (64-bit). In case of the 12 Gb RAM machine, everything is done
> > locally. On the 32 Gb machine, I'm afraid I can't do that. And in fact
> > I've been using the command-line tool all along.
>
> Oh that's a relief.  Okay, well that got rid of several possible sources of
> problems.
>
>
>From http://en.wikipedia.org/wiki/Andrew_File_System:
"The *Andrew File System* (*AFS*) is a distributed networked file
system
"


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

Re: [sqlite] Query with UNION on large table

[Simon Slavin]

On 23 Jun 2011, at 10:56pm, Rense Corten wrote:

> Simon: no, AFS=Andrew File System :). Both my servers run Ubuntu
> (64-bit). In case of the 12 Gb RAM machine, everything is done
> locally. On the 32 Gb machine, I'm afraid I can't do that. And in fact
> I've been using the command-line tool all along.

Oh that's a relief.  Okay, well that got rid of several possible sources of 
problems.

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

Re: [sqlite] Query with UNION on large table

[Rense Corten]

Simon: no, AFS=Andrew File System :). Both my servers run Ubuntu
(64-bit). In case of the 12 Gb RAM machine, everything is done
locally. On the 32 Gb machine, I'm afraid I can't do that. And in fact
I've been using the command-line tool all along.

On Thu, Jun 23, 2011 at 2:45 PM, Simon Slavin  wrote:
>
> On 23 Jun 2011, at 7:20pm, Rense Corten wrote:
>
>> As to RAM: I'm trying this on two different machines, one with 12 Gb
>> and one with 32 Gb RAM. I won't be able to get more in the near
>> future. Something that might be relevant is that the case of the 32Gb
>> machine, the database is on an AFS.
>
> Wait … AFS == Apple File Service ?  You're doing this on Macintoshes 
> accessing storage over a network ?  Okay, then the cause of your speed 
> problems is the network.  Please try creating the INDEX locally on the 
> server.  You don't need to write a program to do this, you can use the 
> command-line tool which you'll find at
>
> /usr/bin/sqlite3
>
> Simon.
> ___
> 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

Re: [sqlite] Query with UNION on large table

[Simon Slavin]

On 23 Jun 2011, at 7:20pm, Rense Corten wrote:

> As to RAM: I'm trying this on two different machines, one with 12 Gb
> and one with 32 Gb RAM. I won't be able to get more in the near
> future. Something that might be relevant is that the case of the 32Gb
> machine, the database is on an AFS.

Wait … AFS == Apple File Service ?  You're doing this on Macintoshes accessing 
storage over a network ?  Okay, then the cause of your speed problems is the 
network.  Please try creating the INDEX locally on the server.  You don't need 
to write a program to do this, you can use the command-line tool which you'll 
find at

/usr/bin/sqlite3

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

Re: [sqlite] Query with UNION on large table

[Simon Slavin]

On 23 Jun 2011, at 9:31pm, Jan Hudec wrote:

> You also need to make sure you are using
> 64-bit build of SQLite (32-bit build can only use between 2 and 3 GB and
> that's not enough for such huge database).

If you try to use a 32-bit compilation of SQLite to open a bigger database, 
does sqlite3_open() issue an error message ?

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

Re: [sqlite] Ordered group by

[J Decker]

On Thu, Jun 23, 2011 at 8:37 AM, Jan Hudec  wrote:
> Hello All,
>
> Is there a way to do group by query with well defined order of calls to the
> aggregate function?
>
> The problem is as follows. I have some objects in a database identified with
> "obj_id". For each object, there are some arrays, represented in the
> normalized form like:
>
>    create table array (
>            obj_id integer,
>            seqnr integer,
>            value integer,
>            primary key (obj_id, seqnr))
>
> For each object there are *several* arrays like this (and they are actually
> queries/view, not simple tables).
>
> Now I need to read all the objects, one at a time, with all the arrays
> relating to it, and process it with in the application. Instead of running
> several
>
>    select value from array where obj_id = :id order by seqnr
>
> for each object, which would take quite a lot of code and tends to perform
> poorly especially since there is non-trivial join in place of "array". So
> I want to denormalize the database by doing:
>
>    create temporary table packed_array (
>            obj_id integer primary key,
>            values text);
>    insert into packed_array (obj_id, values)
>        select
>            obj_id,
>            group_concat(value)
>        from array
>        group by obj_id;
>
> and than join these tables. This is faster because the aggregating selects
> read fewer tables individually and the final join is fast, because it's
> joining by integer primary keys. It is also easier, because it does not need
> any stateful reading code.
>
> Unfortunately it does not work, because I need the array ordered by seqnr.
> I tried:
>
>    insert into packed_array (obj_id, values)
>        select obj_id, group_concat(value)
>        from (
>            select obj_id, value
>            order by obj_id, seqnr
>        )
>        group by obj_id;
>
> But it did not seem to work right and I am not sure whether it should or not.
>
> Is there any way to do this in SQL, or will I have to aggregate it in code?
>

I thought sql shoudl do this itself, but other sql engines (mysql,
mssql) also don't do order and group by 'correctly' so I've ended up
either doing a temporary table or a self join to the first table with
the group by to get the min/max of the other columns that I wanted to
have ordered...

> --
>                                                 Jan 'Bulb' Hudec 
> ___
> 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

Re: [sqlite] Query with UNION on large table

[Jan Hudec]

On Thu, Jun 23, 2011 at 11:20:22 -0700, Rense Corten wrote:
> Wow, I'm almost embarrassed by how helpful you are, gentlemen. Thanks a lot.
> 
> As to RAM: I'm trying this on two different machines, one with 12 Gb
> and one with 32 Gb RAM. I won't be able to get more in the near
> future. Something that might be relevant is that the case of the 32Gb
> machine, the database is on an AFS.

I hope they are running 64-bit OS. You also need to make sure you are using
64-bit build of SQLite (32-bit build can only use between 2 and 3 GB and
that's not enough for such huge database).

Than you need to tell sqlite to use the memory for cache. The cache size is
specified in multiples of page size, so first ask sqlite to tell you what the
database's page size is by issuing

pragma page_size;

query and than set the cache_size by issuing

pragma cache_size = ;

where  is desired cache size divided by the page size. You need to
load about billion rows times two integers, so it will certainly have use for
8GB cache. Try giving it 8 GB on the 12 GB machine and perhaps 24 GB on the
32 GB one.

Since the setting is connection-local, you need to issue the pragma
cache_size command in the application before doing the big operation.


AFS (do I remember right that it's a network filesystem?) is likely slow for
this purpose. Trying on local disk may help.

> As for the ranges of n1 and n1: they are both roughly between 6
> and 1200 .
> 
> Here are the results of  EXPLAIN QUERY PLAN SELECT n1, n2 FROM table1
> Where n1 < n2 INTERSECT SELECT n2, n1 FROM table1 Where n2 < n1;
> 
> 1|0|0|SCAN TABLE table1 (~437976176 rows)
> 2|0|0|SCAN TABLE table1 (~437976176 rows)
> 0|0|0|COMPOUND SUBQUERIES 1 AND 2 USING TEMP B-TREE (INTERSECT)
> 
> I'm not sure what "reasonable" should look like :). I'm running this
> query right now, let's see what happens.

Well, it shows sqlite is indeed trying to create temporary index to do the
intersect, which can be reasonably fast if the data can fit in the cache
for sorting, but if it starts trashing the cache, it will be pretty slow.
8 GB would hopefully be enough, anything less definitely won't.

Reading such large tables in index order tends to be significantly slower
than reading them sequentially, but covering index (that is one containing
all columns needed, so in this case both n1 and n2) might help as that would
be read in sequential order. I am not sure how well the query planner will
manage to use it here though.

It's worth experimenting with different ways to write the query and indices,
perhaps on a smaller sample first. However creating index, temporary or
regular, will always need huge amount of cache and be unusably slow
otherwise, so setting the cache is probably the most important thing you have
to do.

> In my database, the mirrored pairs vastly outnumber the non-mirrored
> ones, to the extent that the non-mirrored pairs are actually extremely
> rare (but relevant).
> 
> Generally, is there a way to figure out if sqlite is still doing
> anything, or whether it got somehow stuck?

It's possible to install a hook using the
http://sqlite.org/c3ref/progress_handler.html API, which will be called every
N virtual machine instructions, but there is no estimate how many
instructions will be needed overall nor how many instructions are left until
query completion and different instructions (in different phases of the
query) may take different amount of time.

> One thing I noticed is that
> it is not writing to the database while my problematic queries are
> running. Should it be?

Not yet. According to the above query plan, it's first going to do the first
subselect and sort it's output and until that is done, it won't have any
output and thus won't write anything to the database. Than it would probably
be more efficient to sort the second subselect too and merge the two lists,
but I think it's actually not going to.

-- 
 Jan 'Bulb' Hudec 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Ordered group by

[Jan Hudec]

On Thu, Jun 23, 2011 at 12:48:51 -0400, Igor Tandetnik wrote:
> On 6/23/2011 11:37 AM, Jan Hudec wrote:
> > Is there a way to do group by query with well defined order of calls to the
> > aggregate function?
> 
> Not reliably.
> 
> > So I want to denormalize the database by doing:
> >
> >  create temporary table packed_array (
> >  obj_id integer primary key,
> >  values text);
> >  insert into packed_array (obj_id, values)
> >  select
> >  obj_id,
> >  group_concat(value)
> >  from array
> >  group by obj_id;
> 
> Can't you materialize the "array" table shown earlier:
> 
>  create table array (
>  obj_id integer,
>  seqnr integer,
>  value integer,
>  primary key (obj_id, seqnr))

It is materialized. The problem is that there is not one, but several of them
and the last step needs to do

select *
from packed_array1
natural join packed_array2
natural join packed_array3

where the arrays don't correspond to each other, so the group bys can't be
rearranged to a single common group-by. So I need to do each of the group bys
separately into either temporary tables or views (it seems temporary table
performance is better in this case, but generally it depends on what the
access pattern ends up to be, which depends on exact query) which is what
this is trying to do.

> > Unfortunately it does not work, because I need the array ordered by seqnr.
> > I tried:
> >
> >  insert into packed_array (obj_id, values)
> >  select obj_id, group_concat(value)
> >  from (
> >  select obj_id, value
> >  order by obj_id, seqnr
> >  )
> >  group by obj_id;
> 
> There should be a "from array" in there somewhere.
Yes, just missed it when writing it (this is obivously not the real query,
but a simplification of it -- the real query is a 3 table join.

> > But it did not seem to work right and I am not sure whether it should or 
> > not.
> 
> It is not guaranteed, but it'll probably work.

Unfortunately it does not. I am currently doing a simple group by (the one at
the begining), but it relies on the particular query plan and the fact, that
the table is being written in order. I accidentally dropped an index it was
using and the order changed and rewriting to this form did *not* help. Which
is why I came to ask in the first place.

-- 
 Jan 'Bulb' Hudec 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Query with UNION on large table

[Black, Michael (IS)]

Come to think of itwhy don't you just write code to walk through the table 
just once?

You can then print out your values just like you want.



Also...it sounds as though you might be able to insert your values "in order". 
i.e. always put the smaller value in the first field (I happen to have an 
application that does just that as order is not important).  Your query seems 
to indicate that order is not important.



Then it's just 1 simple query and 1 SCAN TABLE which would be equivalent to 
walking through the table once.

sqlite> explain query plan select n1,n2 from table2;
sele  order  from  deta
  -    
0 0  0 SCAN TABLE table2 (~100 rows)



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 Black, Michael (IS) [michael.bla...@ngc.com]
Sent: Thursday, June 23, 2011 2:03 PM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Query with UNION on large table

Any reason you can't add another field to your database?

0=equal

1=n1n2



The create an index on that field.





Then your query plan would look like this:

sqlite> explain query plan select n1,n2 from table2 where flag = 1 intersect 
select n2,n1 from table2 where flag = 2;
sele  order  from  deta
  -    
1 0  0 SEARCH TABLE table2 USING INDEX idxn4 (flag=?) (~10 
rows)
2 0  0 SEARCH TABLE table2 USING INDEX idxn4 (flag=?) (~10 
rows)
0 0  0 COMPOUND SUBQUERIES 1 AND 2 USING TEMP B-TREE 
(INTERSECT)



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 Rense Corten [rcor...@gmail.com]
Sent: Thursday, June 23, 2011 1:20 PM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Query with UNION on large table

Wow, I'm almost embarrassed by how helpful you are, gentlemen. Thanks a lot.

As to RAM: I'm trying this on two different machines, one with 12 Gb
and one with 32 Gb RAM. I won't be able to get more in the near
future. Something that might be relevant is that the case of the 32Gb
machine, the database is on an AFS.

As for the ranges of n1 and n1: they are both roughly between 6
and 1200 .

Here are the results of  EXPLAIN QUERY PLAN SELECT n1, n2 FROM table1
Where n1 < n2 INTERSECT SELECT n2, n1 FROM table1 Where n2 < n1;

1|0|0|SCAN TABLE table1 (~437976176 rows)
2|0|0|SCAN TABLE table1 (~437976176 rows)
0|0|0|COMPOUND SUBQUERIES 1 AND 2 USING TEMP B-TREE (INTERSECT)

I'm not sure what "reasonable" should look like :). I'm running this
query right now, let's see what happens.

In my database, the mirrored pairs vastly outnumber the non-mirrored
ones, to the extent that the non-mirrored pairs are actually extremely
rare (but relevant).

Generally, is there a way to figure out if sqlite is still doing
anything, or whether it got somehow stuck? One thing I noticed is that
it is not writing to the database while my problematic queries are
running. Should it be?

Rense

On Thu, Jun 23, 2011 at 9:33 AM, Jan Hudec  wrote:
> On Wed, Jun 22, 2011 at 10:25:52 -0700, Rense Corten wrote:
>> CREATE TABLE table2 AS SELECT n1,n2 FROM (SELECT n1, n2 FROM table1
>> UNION SELECT n2 AS n1, n1 AS n2 FROM table1) WHERE(n1> n1,n2;
>
> Have you tried explaining it (prefix the whole query with "explain query
> plan" and run it)? Does the result look reasonable?
>
>> This has the desired result on a small example, but when I try this on
>> my actual table which has about 800 million rows, the query never
>> seems to complete. It has been running for a couple of days now, and
>> it doesn't seem sqlite is still doing anything (cpu usage dropped to
>> almost zero), but I get no error messages.
>
> Is the disk busy? It would mean you are trashing the caches, which is quite
> likely. For this size of database, couple of gigabytes of cache would
> probably be in order. Try giving it as much cache as possible given your
> available memory using 'PRAGMA cache_size'.
>
> --
> Jan 'Bulb' Hudec 
> ___
> 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
___
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

Re: [sqlite] Query with UNION on large table

[Black, Michael (IS)]

Any reason you can't add another field to your database?

0=equal

1=n1n2



The create an index on that field.





Then your query plan would look like this:

sqlite> explain query plan select n1,n2 from table2 where flag = 1 intersect 
select n2,n1 from table2 where flag = 2;
sele  order  from  deta
  -    
1 0  0 SEARCH TABLE table2 USING INDEX idxn4 (flag=?) (~10 
rows)
2 0  0 SEARCH TABLE table2 USING INDEX idxn4 (flag=?) (~10 
rows)
0 0  0 COMPOUND SUBQUERIES 1 AND 2 USING TEMP B-TREE 
(INTERSECT)



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 Rense Corten [rcor...@gmail.com]
Sent: Thursday, June 23, 2011 1:20 PM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Query with UNION on large table

Wow, I'm almost embarrassed by how helpful you are, gentlemen. Thanks a lot.

As to RAM: I'm trying this on two different machines, one with 12 Gb
and one with 32 Gb RAM. I won't be able to get more in the near
future. Something that might be relevant is that the case of the 32Gb
machine, the database is on an AFS.

As for the ranges of n1 and n1: they are both roughly between 6
and 1200 .

Here are the results of  EXPLAIN QUERY PLAN SELECT n1, n2 FROM table1
Where n1 < n2 INTERSECT SELECT n2, n1 FROM table1 Where n2 < n1;

1|0|0|SCAN TABLE table1 (~437976176 rows)
2|0|0|SCAN TABLE table1 (~437976176 rows)
0|0|0|COMPOUND SUBQUERIES 1 AND 2 USING TEMP B-TREE (INTERSECT)

I'm not sure what "reasonable" should look like :). I'm running this
query right now, let's see what happens.

In my database, the mirrored pairs vastly outnumber the non-mirrored
ones, to the extent that the non-mirrored pairs are actually extremely
rare (but relevant).

Generally, is there a way to figure out if sqlite is still doing
anything, or whether it got somehow stuck? One thing I noticed is that
it is not writing to the database while my problematic queries are
running. Should it be?

Rense

On Thu, Jun 23, 2011 at 9:33 AM, Jan Hudec  wrote:
> On Wed, Jun 22, 2011 at 10:25:52 -0700, Rense Corten wrote:
>> CREATE TABLE table2 AS SELECT n1,n2 FROM (SELECT n1, n2 FROM table1
>> UNION SELECT n2 AS n1, n1 AS n2 FROM table1) WHERE(n1> n1,n2;
>
> Have you tried explaining it (prefix the whole query with "explain query
> plan" and run it)? Does the result look reasonable?
>
>> This has the desired result on a small example, but when I try this on
>> my actual table which has about 800 million rows, the query never
>> seems to complete. It has been running for a couple of days now, and
>> it doesn't seem sqlite is still doing anything (cpu usage dropped to
>> almost zero), but I get no error messages.
>
> Is the disk busy? It would mean you are trashing the caches, which is quite
> likely. For this size of database, couple of gigabytes of cache would
> probably be in order. Try giving it as much cache as possible given your
> available memory using 'PRAGMA cache_size'.
>
> --
> Jan 'Bulb' Hudec 
> ___
> 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
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Query with UNION on large table

[Rense Corten]

Yes, I have run ANALYZE.

On Thu, Jun 23, 2011 at 11:56 AM, Simon Slavin  wrote:
>
> On 23 Jun 2011, at 7:20pm, Rense Corten wrote:
>
>> Here are the results of  EXPLAIN QUERY PLAN SELECT n1, n2 FROM table1
>> Where n1 < n2 INTERSECT SELECT n2, n1 FROM table1 Where n2 < n1;
>>
>> 1|0|0|SCAN TABLE table1 (~437976176 rows)
>> 2|0|0|SCAN TABLE table1 (~437976176 rows)
>> 0|0|0|COMPOUND SUBQUERIES 1 AND 2 USING TEMP B-TREE (INTERSECT)
>
> Have you run ANALYZE ?
>
> http://www.sqlite.org/lang_analyze.html
>
> Simon.
> ___
> 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

Re: [sqlite] Critical issue

[Cyrille]

I am currently making a testing with the C++ Express version. I thought 
it was not possible to use it because it just allow the access to the 
project "SQLite.Interop.2010" of the solution. However, I tried to 
compile applyng the settings you described. I am going to make some test 
and I will come back in few days to share the results.

Again, thanks for your support
Regards,
Cyrille


Le 23/06/2011 18:38, Random Coder a écrit :
> On Thu, Jun 23, 2011 at 9:27 AM, Cyrille  wrote:
>> Thank you for these guidelines. Unfortunately, it seems that Visual
>> Studio is necessary and I just have the Express version. Do you confirm
>> that with the Express version, rebuilding is not possible?
> Sorry, I'd think Visual C++ 2010 Express is good enough, but I don't
> have a machine I can test that theory on.
> ___
> 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

Re: [sqlite] Query with UNION on large table

[Simon Slavin]

On 23 Jun 2011, at 7:20pm, Rense Corten wrote:

> Here are the results of  EXPLAIN QUERY PLAN SELECT n1, n2 FROM table1
> Where n1 < n2 INTERSECT SELECT n2, n1 FROM table1 Where n2 < n1;
> 
> 1|0|0|SCAN TABLE table1 (~437976176 rows)
> 2|0|0|SCAN TABLE table1 (~437976176 rows)
> 0|0|0|COMPOUND SUBQUERIES 1 AND 2 USING TEMP B-TREE (INTERSECT)

Have you run ANALYZE ?

http://www.sqlite.org/lang_analyze.html

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

Re: [sqlite] Query with UNION on large table

[Rense Corten]

Wow, I'm almost embarrassed by how helpful you are, gentlemen. Thanks a lot.

As to RAM: I'm trying this on two different machines, one with 12 Gb
and one with 32 Gb RAM. I won't be able to get more in the near
future. Something that might be relevant is that the case of the 32Gb
machine, the database is on an AFS.

As for the ranges of n1 and n1: they are both roughly between 6
and 1200 .

Here are the results of  EXPLAIN QUERY PLAN SELECT n1, n2 FROM table1
Where n1 < n2 INTERSECT SELECT n2, n1 FROM table1 Where n2 < n1;

1|0|0|SCAN TABLE table1 (~437976176 rows)
2|0|0|SCAN TABLE table1 (~437976176 rows)
0|0|0|COMPOUND SUBQUERIES 1 AND 2 USING TEMP B-TREE (INTERSECT)

I'm not sure what "reasonable" should look like :). I'm running this
query right now, let's see what happens.

In my database, the mirrored pairs vastly outnumber the non-mirrored
ones, to the extent that the non-mirrored pairs are actually extremely
rare (but relevant).

Generally, is there a way to figure out if sqlite is still doing
anything, or whether it got somehow stuck? One thing I noticed is that
it is not writing to the database while my problematic queries are
running. Should it be?

Rense

On Thu, Jun 23, 2011 at 9:33 AM, Jan Hudec  wrote:
> On Wed, Jun 22, 2011 at 10:25:52 -0700, Rense Corten wrote:
>> CREATE TABLE table2 AS SELECT n1,n2 FROM (SELECT n1, n2 FROM table1
>> UNION SELECT n2 AS n1, n1 AS n2 FROM table1) WHERE(n1> n1,n2;
>
> Have you tried explaining it (prefix the whole query with "explain query
> plan" and run it)? Does the result look reasonable?
>
>> This has the desired result on a small example, but when I try this on
>> my actual table which has about 800 million rows, the query never
>> seems to complete. It has been running for a couple of days now, and
>> it doesn't seem sqlite is still doing anything (cpu usage dropped to
>> almost zero), but I get no error messages.
>
> Is the disk busy? It would mean you are trashing the caches, which is quite
> likely. For this size of database, couple of gigabytes of cache would
> probably be in order. Try giving it as much cache as possible given your
> available memory using 'PRAGMA cache_size'.
>
> --
>                                                 Jan 'Bulb' Hudec 
> ___
> 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

Re: [sqlite] Ordered group by

[Igor Tandetnik]

On 6/23/2011 11:37 AM, Jan Hudec wrote:
> Is there a way to do group by query with well defined order of calls to the
> aggregate function?

Not reliably.

> So I want to denormalize the database by doing:
>
>  create temporary table packed_array (
>  obj_id integer primary key,
>  values text);
>  insert into packed_array (obj_id, values)
>  select
>  obj_id,
>  group_concat(value)
>  from array
>  group by obj_id;

Can't you materialize the "array" table shown earlier:

 create table array (
 obj_id integer,
 seqnr integer,
 value integer,
 primary key (obj_id, seqnr))

> Unfortunately it does not work, because I need the array ordered by seqnr.
> I tried:
>
>  insert into packed_array (obj_id, values)
>  select obj_id, group_concat(value)
>  from (
>  select obj_id, value
>  order by obj_id, seqnr
>  )
>  group by obj_id;

There should be a "from array" in there somewhere.

> But it did not seem to work right and I am not sure whether it should or not.

It is not guaranteed, but it'll probably work.
-- 
Igor Tandetnik

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

Re: [sqlite] Critical issue

[Random Coder]

On Thu, Jun 23, 2011 at 9:27 AM, Cyrille  wrote:
> Thank you for these guidelines. Unfortunately, it seems that Visual
> Studio is necessary and I just have the Express version. Do you confirm
> that with the Express version, rebuilding is not possible?

Sorry, I'd think Visual C++ 2010 Express is good enough, but I don't
have a machine I can test that theory on.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Query with UNION on large table

[Jan Hudec]

On Wed, Jun 22, 2011 at 10:25:52 -0700, Rense Corten wrote:
> CREATE TABLE table2 AS SELECT n1,n2 FROM (SELECT n1, n2 FROM table1
> UNION SELECT n2 AS n1, n1 AS n2 FROM table1) WHERE(n1 n1,n2;

Have you tried explaining it (prefix the whole query with "explain query
plan" and run it)? Does the result look reasonable?

> This has the desired result on a small example, but when I try this on
> my actual table which has about 800 million rows, the query never
> seems to complete. It has been running for a couple of days now, and
> it doesn't seem sqlite is still doing anything (cpu usage dropped to
> almost zero), but I get no error messages.

Is the disk busy? It would mean you are trashing the caches, which is quite
likely. For this size of database, couple of gigabytes of cache would
probably be in order. Try giving it as much cache as possible given your
available memory using 'PRAGMA cache_size'.

-- 
 Jan 'Bulb' Hudec 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Critical issue

[Cyrille]

Thank you for these guidelines. Unfortunately, it seems that Visual 
Studio is necessary and I just have the Express version. Do you confirm 
that with the Express version, rebuilding is not possible?

Best regards,
Cyrille


Le 23/06/2011 17:55, Random Coder a écrit :
> On Wed, Jun 22, 2011 at 10:48 PM, Cyrille  wrote:
>>> Alternatively, you could recompile SQLite.Interop.dll to use the
>>> static CRT library (/MT).
>> Could you please let me know how to proceed to do this?
> Download the source from
> http://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki
>
> Decompress the archive
>
> Open the sqlite-dotnetsrc-1007300\SQLite.NET.2010.sln solution file
>
> Ensure "ReleaseNativeOnly" is the currently selected solution configuration.
>
> Right click on the SQLite.Interop.2010 project and select Properties
>
> Again, ensure "ReleaseNativeOnly" is the currently selected solution
> configuration, and Win32 is the platform
>
> Under Configuration Properties ->  C/C++ ->  Code Generation, change the
> Runtime Library to Multi-threaded (/MT), and click OK
>
> Right click on the project again, and select build.
>
> Test the output,
> sqlite-dotnetsrc-1007300\bin\Win32\ReleaseNativeOnly\SQLite.Interop.dll
> , in your project.
> ___
> 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

Re: [sqlite] Critical issue

[Random Coder]

On Wed, Jun 22, 2011 at 10:48 PM, Cyrille  wrote:
>> Alternatively, you could recompile SQLite.Interop.dll to use the
>> static CRT library (/MT).
> Could you please let me know how to proceed to do this?

Download the source from
http://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki

Decompress the archive

Open the sqlite-dotnetsrc-1007300\SQLite.NET.2010.sln solution file

Ensure "ReleaseNativeOnly" is the currently selected solution configuration.

Right click on the SQLite.Interop.2010 project and select Properties

Again, ensure "ReleaseNativeOnly" is the currently selected solution
configuration, and Win32 is the platform

Under Configuration Properties -> C/C++ -> Code Generation, change the
Runtime Library to Multi-threaded (/MT), and click OK

Right click on the project again, and select build.

Test the output,
sqlite-dotnetsrc-1007300\bin\Win32\ReleaseNativeOnly\SQLite.Interop.dll
, in your project.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Ordered group by

[Jan Hudec]

Hello All,

Is there a way to do group by query with well defined order of calls to the
aggregate function?

The problem is as follows. I have some objects in a database identified with
"obj_id". For each object, there are some arrays, represented in the
normalized form like:

create table array (
obj_id integer,
seqnr integer,
value integer,
primary key (obj_id, seqnr))

For each object there are *several* arrays like this (and they are actually
queries/view, not simple tables).

Now I need to read all the objects, one at a time, with all the arrays
relating to it, and process it with in the application. Instead of running
several

select value from array where obj_id = :id order by seqnr

for each object, which would take quite a lot of code and tends to perform
poorly especially since there is non-trivial join in place of "array". So
I want to denormalize the database by doing:

create temporary table packed_array (
obj_id integer primary key,
values text);
insert into packed_array (obj_id, values)
select
obj_id,
group_concat(value)
from array
group by obj_id;

and than join these tables. This is faster because the aggregating selects
read fewer tables individually and the final join is fast, because it's
joining by integer primary keys. It is also easier, because it does not need
any stateful reading code.

Unfortunately it does not work, because I need the array ordered by seqnr.
I tried:

insert into packed_array (obj_id, values)
select obj_id, group_concat(value)
from (
select obj_id, value
order by obj_id, seqnr
)
group by obj_id;

But it did not seem to work right and I am not sure whether it should or not.

Is there any way to do this in SQL, or will I have to aggregate it in code?

-- 
 Jan 'Bulb' Hudec 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite3 WAL (file is encrypted or is not a database, database table is locked, database disk image is malformed') at high load

[Anoop K]

I am sorry I won't able to share the complete program. Also it is quite some
work to extract that piece alone.

*Some good news. As a last resort I downloaded latest sqlite source,
compiled and ran the tests. It never failed. The problem seems **to be fixed
in 3.7.6.3.*

But I am still curious to know the root cause in 3.7.0.1. Are there things
which should be taken care by the applications while doing BULK inserts and
concurrent SELECT/INSERTS/UPDATES  to avoid such 'errors' from sqlite3 ?

Thanks
Anoop


On Thu, Jun 23, 2011 at 7:12 PM, Dan Kennedy  wrote:

> On 06/23/2011 12:11 PM, Anoop K wrote:
> > I am using sqlite3(sqlite-3.7.0.1) with WAL enabled as storage for a
> > multiprocessing daemon(python). On doing a BULK insert of .5 million rows
> > each of size 230 bytes in batches of 500 where each batch is a
> transaction,
> > following errors happen in other processes which perform (<10) SELECTS
> and
> > INSERTS.
> >
> > These errors does not happen always and database do function even after
> > these errors. SELECTS, INSERTS queries after the BULK insert operation do
> > succeed after the batch operation is completed.
> >
> > - file is encrypted or is not a database
> > - database table is locked
> >
> > Sqlite3 configuration
> >
> > - 'PRAGMA synchronous=OFF'
> > - 'PRAGMA journal_mode=wal'
> > - 'PRAGMA wal_checkpoint=FULL' Rest of the configuration parameters
> have
> > have default values
> >
> > If I continue BULK inserts with a sleep of 30 sec, after 10+ runs I
> > see *'database
> > disk image is malformed' . *After that the table to which BULK inserts
> were
> > made does not work. They fail with same error '*database disk image is
> > malformed'.*. Other tables do respond to queries.
> >
> > Any clues on the root cause ? Is this a Sqlite3 WAL bug ?
>
> Do you have a program you can share that exhibits this bug?
>
> Dan.
> ___
> 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

Re: [sqlite] lol: sqlite3 db smaller than the sum of its contents...

[Stephan Beal]

On Thu, Jun 23, 2011 at 4:49 PM, Jean-Denis Muys wrote:

> Let me add two other drawbacks as well:
>
> - incremental backups: now everytime you change one small file, the whole
> database needs to be backed up, increasing needlessly storage size, and
> backup time. This applies to system that do versioning as well as backups
> (such as Time Machine).
>

In this case that's not the issue - the application using the db _is_ the
wiki back-end/manager, so the pages have to be in the db. i only have them
as files because i imported them from a GoogleCode project.

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] lol: sqlite3 db smaller than the sum of its contents...

[Mr. Puneet Kishor]

On Jun 23, 2011, at 10:49 AM, Jean-Denis Muys  wrote:

> 
> On 23 juin 2011, at 16:22, Mr. Puneet Kishor wrote:
> 
>> 
>> 
>> 
>> On Jun 23, 2011, at 10:18 AM, Stephan Beal  wrote:
>> 
>>> Hi, all!
>>> 
>>> Today i saw a curious thing: i store 440kb of wiki files in an sqlite3 db
>>> and the db file is only 400kb.
>>> 
>>> HTF can that possibly be?
>>> 
>>> After poking around i found that the wiki files actually total 360kb (when i
>>> added up their sizes manually, as opposed to using 'df' to get it), and the
>>> extra 80kb were from the hard drive's large block size (slack space reported
>>> by 'df').
>>> 
>>> Kinda funny, though, that sqlite3 actually decreases the amount of storage
>>> required in this case.
>> 
>> 
>> Lots of small files will take up more space because of the fixed minimum 
>> block size. For large corpuses this won't matter. Putting them all in one db 
>> makes logistical management easier, but you will lose the ability to update 
>> just a single file individually. I used to store all my wiki files 
>> (punkish.org) in one SQLite db, but now I have them as separate files which 
>> allows me to just ssh and edit a single file easily. Six of one, and all 
>> that.
>> 
> 
> Let me add two other drawbacks as well:
> 
> - incremental backups: now everytime you change one small file, the whole 
> database needs to be backed up, increasing needlessly storage size, and 
> backup time. This applies to system that do versioning as well as backups 
> (such as Time Machine).
> 
> - system level indexing: it now becomes much more difficult, if not 
> impossible, to do system level indexing and searching (as eg in Spotlight). 
> This is the reason why Apple stopped using a monolithic database for its 
> email application, now storing each mail individually: so that system-wide 
> user search can hit emails too.


Yup. Very good points, both of them.

I still use the db for metadata, but my files are stored in a tree directory 
structure much like CPAN's directories -- /path/<1>/<12>/<123>/filename.txt 
where 1, 2, and 3 are the first, second and third letters of the filename. I 
could store the metadata per file within each file, however, I haven't yet 
found a way to "find the ten most recently edited files" or "find all files 
edited by ".


> 
> These two drawbacks may or may not apply to your situation.
> 
> Jean-Denis
> 
> ___
> 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

Re: [sqlite] lol: sqlite3 db smaller than the sum of its contents...

[Jean-Denis Muys]

On 23 juin 2011, at 16:22, Mr. Puneet Kishor wrote:

> 
> 
> 
> On Jun 23, 2011, at 10:18 AM, Stephan Beal  wrote:
> 
>> Hi, all!
>> 
>> Today i saw a curious thing: i store 440kb of wiki files in an sqlite3 db
>> and the db file is only 400kb.
>> 
>> HTF can that possibly be?
>> 
>> After poking around i found that the wiki files actually total 360kb (when i
>> added up their sizes manually, as opposed to using 'df' to get it), and the
>> extra 80kb were from the hard drive's large block size (slack space reported
>> by 'df').
>> 
>> Kinda funny, though, that sqlite3 actually decreases the amount of storage
>> required in this case.
> 
> 
> Lots of small files will take up more space because of the fixed minimum 
> block size. For large corpuses this won't matter. Putting them all in one db 
> makes logistical management easier, but you will lose the ability to update 
> just a single file individually. I used to store all my wiki files 
> (punkish.org) in one SQLite db, but now I have them as separate files which 
> allows me to just ssh and edit a single file easily. Six of one, and all that.
> 

Let me add two other drawbacks as well:

- incremental backups: now everytime you change one small file, the whole 
database needs to be backed up, increasing needlessly storage size, and backup 
time. This applies to system that do versioning as well as backups (such as 
Time Machine).

- system level indexing: it now becomes much more difficult, if not impossible, 
to do system level indexing and searching (as eg in Spotlight). This is the 
reason why Apple stopped using a monolithic database for its email application, 
now storing each mail individually: so that system-wide user search can hit 
emails too.

These two drawbacks may or may not apply to your situation.

Jean-Denis

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

Re: [sqlite] lol: sqlite3 db smaller than the sum of its contents...

[Mr. Puneet Kishor]

On Jun 23, 2011, at 10:18 AM, Stephan Beal  wrote:

> Hi, all!
> 
> Today i saw a curious thing: i store 440kb of wiki files in an sqlite3 db
> and the db file is only 400kb.
> 
> HTF can that possibly be?
> 
> After poking around i found that the wiki files actually total 360kb (when i
> added up their sizes manually, as opposed to using 'df' to get it), and the
> extra 80kb were from the hard drive's large block size (slack space reported
> by 'df').
> 
> Kinda funny, though, that sqlite3 actually decreases the amount of storage
> required in this case.


Lots of small files will take up more space because of the fixed minimum block 
size. For large corpuses this won't matter. Putting them all in one db makes 
logistical management easier, but you will lose the ability to update just a 
single file individually. I used to store all my wiki files (punkish.org) in 
one SQLite db, but now I have them as separate files which allows me to just 
ssh and edit a single file easily. Six of one, and all that.


> 
> -- 
> - stephan beal
> http://wanderinghorse.net/home/stephan/
> ___
> 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

[sqlite] lol: sqlite3 db smaller than the sum of its contents...

[Stephan Beal]

Hi, all!

Today i saw a curious thing: i store 440kb of wiki files in an sqlite3 db
and the db file is only 400kb.

HTF can that possibly be?

After poking around i found that the wiki files actually total 360kb (when i
added up their sizes manually, as opposed to using 'df' to get it), and the
extra 80kb were from the hard drive's large block size (slack space reported
by 'df').

Kinda funny, though, that sqlite3 actually decreases the amount of storage
required in this case.

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite3 WAL (file is encrypted or is not a database, database table is locked, database disk image is malformed') at high load

[Dan Kennedy]

On 06/23/2011 12:11 PM, Anoop K wrote:
> I am using sqlite3(sqlite-3.7.0.1) with WAL enabled as storage for a
> multiprocessing daemon(python). On doing a BULK insert of .5 million rows
> each of size 230 bytes in batches of 500 where each batch is a transaction,
> following errors happen in other processes which perform (<10) SELECTS and
> INSERTS.
>
> These errors does not happen always and database do function even after
> these errors. SELECTS, INSERTS queries after the BULK insert operation do
> succeed after the batch operation is completed.
>
> - file is encrypted or is not a database
> - database table is locked
>
> Sqlite3 configuration
>
> - 'PRAGMA synchronous=OFF'
> - 'PRAGMA journal_mode=wal'
> - 'PRAGMA wal_checkpoint=FULL' Rest of the configuration parameters have
> have default values
>
> If I continue BULK inserts with a sleep of 30 sec, after 10+ runs I
> see *'database
> disk image is malformed' . *After that the table to which BULK inserts were
> made does not work. They fail with same error '*database disk image is
> malformed'.*. Other tables do respond to queries.
>
> Any clues on the root cause ? Is this a Sqlite3 WAL bug ?

Do you have a program you can share that exhibits this bug?

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

Re: [sqlite] Sqlite3 WAL (file is encrypted or is not a database, database table is locked, database disk image is malformed') at high load

[Simon Slavin]

On 23 Jun 2011, at 2:22pm, Anoop K wrote:

> By the way I
> did change it to 'NORMAL' and tried. The issues mentioned in the mail still
> happened

Okay, that's useful information.

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

Re: [sqlite] Sqlite3 WAL (file is encrypted or is not a database, database table is locked, database disk image is malformed') at high load

[Anoop K]

I had to turn synchronous=OFF to achieve required performance. By the way I
did change it to 'NORMAL' and tried. The issues mentioned in the mail still
happened

Some more info regarding the problem. At this high load IO Wait was about
50-60%.

Thanks
Anoop

On Thu, Jun 23, 2011 at 5:42 PM, Simon Slavin  wrote:

>
> On 23 Jun 2011, at 6:11am, Anoop K wrote:
>
> > Sqlite3 configuration
> >
> >   - 'PRAGMA synchronous=OFF'
>
> Remove that and try it again.
>
> Simon.
> ___
> 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

Re: [sqlite] Sqlite3 WAL (file is encrypted or is not a database, database table is locked, database disk image is malformed') at high load

[Simon Slavin]

On 23 Jun 2011, at 6:11am, Anoop K wrote:

> Sqlite3 configuration
> 
>   - 'PRAGMA synchronous=OFF'

Remove that and try it again.

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

Re: [sqlite] Query with UNION on large table

[Tom Holden]

Is the Hard Drive thrashing? Could be that most everything is being done in 
swap files. Given the size of the table, a lot more RAM would help.

Tom 

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

Re: [sqlite] Problem with Read/Write concurency

[Igor Tandetnik]

Natusch, Paul  wrote:
> In another process, I have a sqlite command line connected to the same
> database, I periodically do select * from tableName;
> 
> This causes my write to get a busy response.  How do I enable
> shared_cache for the command line?

Shared cache works only for multiple connections within the same process. It 
doesn't work across processes.

Consider using Write-Ahead Logging (WAL):

http://www.sqlite.org/wal.html

With WAL, readers never block writers and vice versa (two writers still block 
each other).

> Is it true that if the read uncommitted pragma and shared_cache are
> enabled that the select will not take any locks?

It will not take any in-memory locks within the shared cache. It will still 
take the regular SHARED locks on the database file.
-- 
Igor Tandetnik

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

Re: [sqlite] openmpi parallel problem

[Thorsten Kersting]

i finalizeevery statement right before closing the database, and every 
finalize returns sqlite_ok. but then closing returns sqlite_busy.

Am 22.06.2011 19:04, schrieb Pavel Ivanov:
>> there are no statements open, as far as i can see.
> Your eyes can miss something. Does sqlite3_close return SQLITE_OK or
> something else?
>
>
> Pavel
>
>
> On Wed, Jun 22, 2011 at 12:36 PM, Thorsten Kersting
>   wrote:
>> there are no statements open, as far as i can see.
>>
>> On 06/22/2011 05:29 PM, Pavel Ivanov wrote:
>>> Do you check result code of connection closing? Is it successful?
>>> If it's not successful then some statements are still active and
>>> reading transaction is still open. That could be the reason of error
>>> in write process.
>>>
>>>
>>> Pavel
>>>
>>>
>>> On Wed, Jun 22, 2011 at 10:58 AM, Thorsten Kersting
>>> wrote:
 i use openmpi fo parallelization, an c++ as the interface to sqlite.
 in my program one process only writes into the database, and the other
 processes read at the beginning, and then never do anything with it. But
 even when i close the database-connection of the non-writing processes,
 my write process says, that the database is locked. And this lock never
 gets released.
 I don't understand, why there is a lock on the database, when no other
 connection exists. Is there a way, that i can see, which connection has
 the lock?
 i tried this in a testprogramm, and there it worked, but not in the
 programm i want to use.

 thanks for any help
 ___
 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
>>>
>> ___
>> 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
>
>

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

[sqlite] Problem with Read/Write concurency

[Natusch, Paul]

I have an application for which,  I have one process which is writing to
the database pretty frequently.  I believe that I have enabled
share_cache

And read uncommitted.   How to I verify this?  

 

 

In another process, I have a sqlite command line connected to the same
database, I periodically do select * from tableName;

This causes my write to get a busy response.  How do I enable
shared_cache for the command line?

 

Can you help with my understanding?

 

Is it true that if the read uncommitted pragma and shared_cache are
enabled that the select will not take any locks?  If this is true and I
have only

One writer how can I get busy?  Without the command line doing selects,
the writes never fail. 

 

 

Thanks

Paul

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

[sqlite] Write ahead log

[Grzegorz Russek]

Hi

I've encountered a big problem which I failed to solve and I'm writing here
to get some advice.

But let's begin from the start. I use WAL journal mode in most of my
applications including WinCE 4/20 based devices. I know that it's not
supported, but using simple trick it actually works with C++ based
applications, because I get 36%+ performance when writing data. This is how
I modified winShmSystemLock function:

static int winShmSystemLock(
  winShmNode *pFile,/* Apply locks to this open shared-memory segment */
  int lockType, /* _SHM_UNLCK, _SHM_RDLCK, or _SHM_WRLCK */
  int ofst, /* Offset to first byte to be locked/unlocked */
  int nByte /* Number of bytes to lock or unlock */
){
// PATCH START: _WIN32_CE/Lock/Unlock file: Grzegorz Russek
  int rc = 0;   /* Result code form Lock/UnlockFileEx() */
#if SQLITE_OS_WINCE == 0
// PATCH END: _WIN32_CE/Lock/Unlock file: dr4cul4
  OVERLAPPED ovlp;
  DWORD dwFlags;
// PATCH START: _WIN32_CE/Lock/Unlock file: dr4cul4
  //int rc = 0;   /* Result code form Lock/UnlockFileEx() */
// PATCH END: _WIN32_CE/Lock/Unlock file:
dr4cul4
  /* Access to the winShmNode object is serialized by the caller */
  assert( sqlite3_mutex_held(pFile->mutex) || pFile->nRef==0 );

  /* Initialize the locking parameters */
  dwFlags = LOCKFILE_FAIL_IMMEDIATELY;
  if( lockType == _SHM_WRLCK ) dwFlags |= LOCKFILE_EXCLUSIVE_LOCK;

  memset(, 0, sizeof(OVERLAPPED));
  ovlp.Offset = ofst;

  /* Release/Acquire the system-level lock */
  if( lockType==_SHM_UNLCK ){
rc = UnlockFileEx(pFile->hFile.h, 0, nByte, 0, );
  }else{
rc = LockFileEx(pFile->hFile.h, dwFlags, 0, nByte, 0, );
  }

  if( rc!= 0 ){
rc = SQLITE_OK;
  }else{
pFile->lastErrno =  GetLastError();
rc = SQLITE_BUSY;
  }

  OSTRACE(("SHM-LOCK %d %s %s 0x%08lx\n",
   pFile->hFile.h,
   rc==SQLITE_OK ? "ok" : "failed",
   lockType==_SHM_UNLCK ? "UnlockFileEx" : "LockFileEx",
   pFile->lastErrno));

// PATCH START: _WIN32_CE/Lock/Unlock file: dr4cul4
#endif
// PATCH END: _WIN32_CE/Lock/Unlock file: dr4cul4
  return rc;
}

As you can see I just ignore locking and it works like a charm, but recently
I was writing new project in Compact Framework so I used System.Data.SQLite
provider. It was working quite well but we got a performance issue. Problem
is that we need to insert about 27000 rows into one table which is also
quite wide (about 25 columns, mostly text). Those rows come to application
from a web service which we can't replace with something else.

I modified compilation flags and added above modification and it doesn't
work... actually crashes. I wrote the same inserting code in C++ to check if
it's possible with that version of library and it works. So I decided to
write here because I cant find a reason for this behavior. SQLite throws
error in one of the first statements (randomly sometimes first sometimes
third). I pinpointed location in code:

Error occurs in winShmMap function. System function CreateFileMapping
returns NULL and GetLastError() returns 6 (Invalid Handle) which results
in SQLITE_IOERR error. This happens on 3.7.0.1 and 3.7.6.3, but I suppose
issue will also happen on all other versions.

To specify my needs:

   1. Is there a way to overcome this obstacle and enable WAL journal mode
   using >NET Compact Framework provider (target platform is still WinCE 4.20,
   so I can't switch to WinCE 5)
   2. Is there a way to insert 27000 rows under 3 minutes (200MHz Motorolla
   device - daemon of slowness, both CPU and Flash)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Sqlite3 WAL DatabaseError: file is encrypted or is not a database

[Anoop K]

I am using sqlite3(sqlite-3.7.0.1) with WAL enabled as storage for a
multiprocessing daemon(python). On doing a BULK insert of .5 million rows
each of size 230 bytes in batches of 500 where each batch is a transaction,
following errors happen in other processes which perform (<10) SELECTS and
INSERTS.

These errors does not happen always and database do function even after
these errors. SELECTS, INSERTS queries after the BULK insert operation do
succeed after the batch operation is completed.

   - DatabaseError: file is encrypted or is not a database
   - file is encrypted or is not a database
   - database table is locked

Sqlite3 configuration

   - 'PRAGMA synchronous=OFF'
   - 'PRAGMA journal_mode=wal'
   - 'PRAGMA wal_checkpoint=FULL' Rest of the configuration parameters have
   have default values

Any clues on the root cause ? Is this a Sqlite3 WAL bug ?


Thanks

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

Re: [sqlite] Query with UNION on large table

[Max Vlasov]

On Wed, Jun 22, 2011 at 9:25 PM, Rense Corten  wrote:

>
> CREATE TABLE table2 AS SELECT n1,n2 FROM (SELECT n1, n2 FROM table1
> UNION SELECT n2 AS n1, n1 AS n2 FROM table1) WHERE(n1 n1,n2;
>
> This has the desired result on a small example, but when I try this on
> my actual table which has about 800 million rows, the query never
> seems to complete. It has been running for a couple of days now, and
> it doesn't seem sqlite is still doing anything (cpu usage dropped to
> almost zero)...
>

Rense, looks like an interesting task.

What are the ranges of n1 and n2? We can take sqlite logic into account, but
regardless of the one, if the range is small comaring to the row count, the
result set will be small, so there's always possible to do full scan
maintaining the result as a small table (or memory array). I suppose sqlite
sometimes goes this way after the some guessing. But if the range is large
(so also a large result set is expected), there's no way other then prior
sorting of both sub-tables (or probably by creating two indexes). Looking at
how your query executed against my test data and seeing I/i read and write
statistics, sqlite probably does these temporary tables creation.

I noticed that fewer reads/writes will be with the following variant

SELECT * FROM (SELECT n1, n2 FROM table1 Where n1 < n2)
UNION
SELECT * FROM (SELECT n2, n1 FROM table1 Where n2 < n1)

since this one decreases the sizes of the tables that should be ordered.

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

[sqlite] reset WAL log

[Lukas Gebauer]

Hi all!

I have a silly question probably... maybe someone helps me.

I have a 33MB database with 100MB log. And log growing...

So, I try to access database by sqlite3.exe only. It is version 
3.7.6.3.  I call "PRAGMA wal_checkpoint(RESTART);" It immediately 
finish with "0|8|8". But WAL log file remains big and untouched. 
(Only db file and SHM are touched)

I typed '.exit' in the console, and now is WAL log deleted.

My question is - why I must close the connection to database for 
reduce WAL log?

Same problem is inside my program. WAL log remain big, until I close 
connection to database. Even I calling "PRAGMA 
wal_checkpoint(RESTART)" frequently and no concurrent access to 
database.

Can I flush WAL log and reduce log size without close database 
connection?

Thank you!


-- 
Lukas Gebauer.

http://synapse.ararat.cz/ - Ararat Synapse - TCP/IP Lib.
http://geoget.ararat.cz/ - Geocaching solution

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

Re: [sqlite] [BUG?] unexpected effect of view nesting on type conversion

[Mark Brand]

>> I've run into a phenomenon I don't understand where view nesting affects
>> types.
>> Seen in sqlite 3.7.6.3
> sqlite-3.6.23 does NOT show this behavior.

After further reflection, I am going to go out on a limb and suggest 
that the behavior described in the OP is a regression. The behavior 
started somewhere between versions 3.6.23 and 3.7.6.3. It can have 
serious consequences in the form of calculation errors.

regards,

Mark

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