[sqlite] large table performance

[Daniel Franke]

Hi all.

I spent the last days bragging that a single database file as provided by 
sqlite is a far better approach to store data than -literally- thousands of 
flat files. Now, I got a small amount of testing data an wow ... I'm stuck.

Area: Bioinformatics. Imagine a matrix of data: genetic marker names 
(attribute A) in columns and individuals (attribute B) in rows. Since the 
number of features per attribute varies between projects, I decided to create 
three tables:

 * Table markers: the genetic markers (attribute A), e.g. 100.000 rows
 * Table individuals: individual ids (attribute B), e.g. 1.000 rows
 * Table genotypes:   the genetic data

Tables "markers" and "individuals" have 2 and 6 columns respectively, a unique 
primary key, and the (basically) the name of the feature, "genotypes" holds 
foreign keys to "markers"/"individuals" respectively as well as the genotype 
column(s), see below.

Genotypes are inserted by:

INSERT OR ROLLBACK INTO genotypes VALUES ((SELECT id FROM marker WHERE 
name='$markername$'), (SELECT id FROM individuals WHERE pedigree='$pedigree$' 
AND person='$person$'), $genA$, $genB$);

Where $markername$, ..., $genB$ are replaced with the appropiate values.

Given the schema below, feeding a million INSERTs into the database by 
sqlite3_exec() takes about 30 minutes (this includes transactions, indices 
and "PRAGMA synchronous=off" as well as a fully optimized build of sqlite). 

Is there any chance to speed this up? Production datasets could easily bring a 
billion genotypes ...


Any pointer would be appreciated!

With kind regards

Daniel Franke


--

The database schema:


CREATE TABLE marker (
id integer PRIMARY KEY AUTOINCREMENT,
name varchar UNIQUE);

CREATE INDEX markernameidx on marker(name);


CREATE TABLE individuals (
id integer PRIMARY KEY AUTOINCREMENT,
pedigree varchar NOT NULL,
person varchar NOT NULL,
father varchar,
mother varchar,
sex integer NOT NULL,
UNIQUE(pedigree, person));

CREATE INDEX individualidx ON individuals (pedigree, person);


CREATE TABLE genotypes(
markerid integer NOT NULL REFERENCES marker(id),
individualid integer NOT NULL REFERENCES individuals(id),
genA integer,
genB integer,
UNIQUE(markerid, individualid));

CREATE INDEX genotypeidx ON genotypes(markerid, individualid);

Re: [sqlite] Using SQLite on networked drive

[Rolf Schaeuble]

Just out of personal interest:
Could you detail on which versions of Windows it works, and on which it 
doesn't?

Thanks

Rolf


[EMAIL PROTECTED] wrote:

"Jay Sprenkle" <[EMAIL PROTECTED]> wrote:
  

On 3/2/06, Vishal Kashyap <[EMAIL PROTECTED]> wrote:


Dear All ,

Wanted to know if we can use sqlite database from network drives ?
  

It works if your operating system correctly handles locking.
If I remember correctly there are some problems with windows drives,
but NFS works.
Check the documentation section of the web site.

.



It works on some, but not all, versions of windows.  And it
works on some, but not all, version of NFS. On those operating
systems where network file locking is not implemented correctly
the result will be corrupt database files.  


The performance of SQLite over a network filesystem is
consistent to the performance of the network filesystem
itself - very slow.

SQLite will, in theory, work over a network filesystem.
But for the reasons cited above, it is not recommended.

That said, I should add that for the reasons cited above,
the use of a network filesystem itself is not recommended.
But that is a whole other story

--
D. Richard Hipp   <[EMAIL PROTECTED]>


  

RE: [sqlite] ORDER BY of UNION?

[Boris Popov]

Appreciate you looking into this in such detail for me, at least I wasn't
going completely crazy thinking that it should have worked. I'll go ahead
and try to change the code generator to use your suggested workaround, but
will keep an eye on the new ticket 1700:

http://www.sqlite.org/cvstrac/tktview?tn=1700

On a slightly unrelated note, how does one go about getting a login for a
tracking system? Should I stay anonymous?

Cheers!

-Boris

-- 
+1.604.689.0322
DeepCove Labs Ltd.
4th floor 595 Howe Street
Vancouver, Canada V6C 2T5

[EMAIL PROTECTED]

CONFIDENTIALITY NOTICE

This email is intended only for the persons named in the message
header. Unless otherwise indicated, it contains information that is
private and confidential. If you have received it in error, please
notify the sender and delete the entire message including any
attachments.

Thank you.

-Original Message-
From: Dennis Cote [mailto:[EMAIL PROTECTED] 
Sent: Thursday, March 02, 2006 1:52 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] ORDER BY of UNION?

Boris Popov wrote:

>Looks like I'm on my own with this one :)
>
>After a bit of fiddling with it this morning, I figured I had to do this
>
>SELECT DISTINCT * FROM (SELECT t1.ID
> FROM GR_ADDRESS t1
> WHERE t1.ID > 1 UNION ALL SELECT t1.ID
>FROM PERSON t1 ORDER BY t1.ID DESC) 
>
>instead of
>
>SELECT DISTINCT * FROM (SELECT t1.ID
> FROM GR_ADDRESS t1
> WHERE t1.ID > 1 UNION ALL SELECT t1.ID
> FROM PERSON t1) t1 ORDER BY t1.ID DESC
>
>Although it bugs me that the latter seems to be perfectly valid chunk of
SQL
>as far as SQLite documentation is concerned and as far as other DBs are
used
>to doing things. Is this intentional or just one little quirk?
>
>Cheers!
>
>-Boris
>
>  
>
Boris,

I think you have found another example of the problems SQLite has 
handling columns names.

The following log first shows what SQLite thinks the column name is for 
the query without the order by clause (i.e. t1.ID). Then we try to order 
by that column name, with or without the table alias. Both cases result 
in an error. Finally there is a work around that you could use that 
applies an alias to the selected columns in the two tables that are 
combined by the union operation.

SQLite version 3.3.2
Enter ".help" for instructions
sqlite> create table GR_ADDRESS(id, data);
sqlite> create table PERSON(id, data);
sqlite> .mode column
sqlite> .header on
sqlite> insert into gr_address values(1, 10);
sqlite> insert into person values(2, 20);
sqlite> insert into gr_address values(3, 30);
sqlite> SELECT DISTINCT *
   ...> FROM
   ...> (SELECT t1.ID
   ...> FROM GR_ADDRESS t1
   ...> WHERE t1.ID > 1
   ...> UNION ALL
   ...> SELECT t1.ID
   ...> FROM PERSON t1)
   ...> t1;
t1.ID
--
3
2
sqlite> SELECT DISTINCT *
   ...> FROM
   ...> (SELECT t1.ID
   ...> FROM GR_ADDRESS t1
   ...> WHERE t1.ID > 1
   ...> UNION ALL
   ...> SELECT t1.ID
   ...> FROM PERSON t1)
   ...> t1 ORDER BY t1.ID DESC;
SQL error: no such column: t1.ID
sqlite> SELECT DISTINCT *
   ...> FROM
   ...> (SELECT t1.ID
   ...> FROM GR_ADDRESS t1
   ...> WHERE t1.ID > 1
   ...> UNION ALL
   ...> SELECT t1.ID
   ...> FROM PERSON t1)
   ...> t1 ORDER BY ID DESC;
SQL error: no such column: ID
sqlite> SELECT DISTINCT *
   ...> FROM
   ...> (SELECT t1.ID as ID
   ...> FROM GR_ADDRESS t1
   ...> WHERE t1.ID > 1
   ...> UNION ALL
   ...> SELECT t1.ID as ID
   ...> FROM PERSON t1)
   ...> t1 ORDER BY t1.ID DESC;
ID
--
3
2




You might want to file a bug report ticket. You may also be interested 
in the discussion of a similar problem under ticket 1688.

HTH
Dennis Cote


smime.p7s
Description: S/MIME cryptographic signature

Re: [sqlite] ORDER BY of UNION?

[Dennis Cote]

Boris Popov wrote:


Looks like I'm on my own with this one :)

After a bit of fiddling with it this morning, I figured I had to do this

SELECT DISTINCT * FROM (SELECT t1.ID
FROM GR_ADDRESS t1
WHERE t1.ID > 1 UNION ALL SELECT t1.ID
FROM PERSON t1 ORDER BY t1.ID DESC) 


instead of

SELECT DISTINCT * FROM (SELECT t1.ID
FROM GR_ADDRESS t1
WHERE t1.ID > 1 UNION ALL SELECT t1.ID
FROM PERSON t1) t1 ORDER BY t1.ID DESC

Although it bugs me that the latter seems to be perfectly valid chunk of SQL
as far as SQLite documentation is concerned and as far as other DBs are used
to doing things. Is this intentional or just one little quirk?

Cheers!

-Boris

 


Boris,

I think you have found another example of the problems SQLite has 
handling columns names.


The following log first shows what SQLite thinks the column name is for 
the query without the order by clause (i.e. t1.ID). Then we try to order 
by that column name, with or without the table alias. Both cases result 
in an error. Finally there is a work around that you could use that 
applies an alias to the selected columns in the two tables that are 
combined by the union operation.


SQLite version 3.3.2
Enter ".help" for instructions
sqlite> create table GR_ADDRESS(id, data);
sqlite> create table PERSON(id, data);
sqlite> .mode column
sqlite> .header on
sqlite> insert into gr_address values(1, 10);
sqlite> insert into person values(2, 20);
sqlite> insert into gr_address values(3, 30);
sqlite> SELECT DISTINCT *
  ...> FROM
  ...> (SELECT t1.ID
  ...> FROM GR_ADDRESS t1
  ...> WHERE t1.ID > 1
  ...> UNION ALL
  ...> SELECT t1.ID
  ...> FROM PERSON t1)
  ...> t1;
t1.ID
--
3
2
sqlite> SELECT DISTINCT *
  ...> FROM
  ...> (SELECT t1.ID
  ...> FROM GR_ADDRESS t1
  ...> WHERE t1.ID > 1
  ...> UNION ALL
  ...> SELECT t1.ID
  ...> FROM PERSON t1)
  ...> t1 ORDER BY t1.ID DESC;
SQL error: no such column: t1.ID
sqlite> SELECT DISTINCT *
  ...> FROM
  ...> (SELECT t1.ID
  ...> FROM GR_ADDRESS t1
  ...> WHERE t1.ID > 1
  ...> UNION ALL
  ...> SELECT t1.ID
  ...> FROM PERSON t1)
  ...> t1 ORDER BY ID DESC;
SQL error: no such column: ID
sqlite> SELECT DISTINCT *
  ...> FROM
  ...> (SELECT t1.ID as ID
  ...> FROM GR_ADDRESS t1
  ...> WHERE t1.ID > 1
  ...> UNION ALL
  ...> SELECT t1.ID as ID
  ...> FROM PERSON t1)
  ...> t1 ORDER BY t1.ID DESC;
ID
--
3
2




You might want to file a bug report ticket. You may also be interested 
in the discussion of a similar problem under ticket 1688.


HTH
Dennis Cote

Re: [sqlite] Using SQLite on networked drive

[drh]

"Jay Sprenkle" <[EMAIL PROTECTED]> wrote:
> On 3/2/06, Vishal Kashyap <[EMAIL PROTECTED]> wrote:
> > Dear All ,
> >
> > Wanted to know if we can use sqlite database from network drives ?
> 
> It works if your operating system correctly handles locking.
> If I remember correctly there are some problems with windows drives,
> but NFS works.
> Check the documentation section of the web site.
> 
> .

It works on some, but not all, versions of windows.  And it
works on some, but not all, version of NFS. On those operating
systems where network file locking is not implemented correctly
the result will be corrupt database files.  

The performance of SQLite over a network filesystem is
consistent to the performance of the network filesystem
itself - very slow.

SQLite will, in theory, work over a network filesystem.
But for the reasons cited above, it is not recommended.

That said, I should add that for the reasons cited above,
the use of a network filesystem itself is not recommended.
But that is a whole other story

--
D. Richard Hipp   <[EMAIL PROTECTED]>

Re: [sqlite] Using SQLite on networked drive

[Jay Sprenkle]

On 3/2/06, Vishal Kashyap <[EMAIL PROTECTED]> wrote:
> Dear All ,
>
> Wanted to know if we can use sqlite database from network drives ?

It works if your operating system correctly handles locking.
If I remember correctly there are some problems with windows drives,
but NFS works.
Check the documentation section of the web site.

Re: [sqlite] Using SQLite on networked drive

[Ray Mosley]

I would be interested in knowing how you handle simulatneous inserts and/or
updates...

On 3/2/06, Vishal Kashyap <[EMAIL PROTECTED]> wrote:
>
> Dear All ,
>
> Wanted to know if we can use sqlite database from network drives ?
>
> --
> With Best Regards,
> Vishal Kashyap.
> http://www.vishal.net.in
>



--
Ray Mosley

[sqlite] Using SQLite on networked drive

[Vishal Kashyap]

Dear All ,

Wanted to know if we can use sqlite database from network drives ?

--
With Best Regards,
Vishal Kashyap.
http://www.vishal.net.in

RE: [sqlite] ORDER BY of UNION?

[Boris Popov]

Looks like I'm on my own with this one :)

After a bit of fiddling with it this morning, I figured I had to do this

SELECT DISTINCT * FROM (SELECT t1.ID
 FROM GR_ADDRESS t1
 WHERE t1.ID > 1 UNION ALL SELECT t1.ID
FROM PERSON t1 ORDER BY t1.ID DESC) 

instead of

SELECT DISTINCT * FROM (SELECT t1.ID
 FROM GR_ADDRESS t1
 WHERE t1.ID > 1 UNION ALL SELECT t1.ID
 FROM PERSON t1) t1 ORDER BY t1.ID DESC

Although it bugs me that the latter seems to be perfectly valid chunk of SQL
as far as SQLite documentation is concerned and as far as other DBs are used
to doing things. Is this intentional or just one little quirk?

Cheers!

-Boris

-- 
+1.604.689.0322
DeepCove Labs Ltd.
4th floor 595 Howe Street
Vancouver, Canada V6C 2T5

[EMAIL PROTECTED]

CONFIDENTIALITY NOTICE

This email is intended only for the persons named in the message
header. Unless otherwise indicated, it contains information that is
private and confidential. If you have received it in error, please
notify the sender and delete the entire message including any
attachments.

Thank you.

-Original Message-
From: Boris Popov [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, March 01, 2006 11:59 PM
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] ORDER BY of UNION?

Okay that works, but is there a way to make it work with the t1 alias? I'm
porting an existing application to SQLite and changing all queries to not
use aliases may be problematic if you know what I mean. Looking at the
syntax page I don't see how using t1 is illegal and yet clearly as doesn't
work as expected. Any more hints, ideas or suggestions?

Thanks!

-Boris

-- 
+1.604.689.0322
DeepCove Labs Ltd.
4th floor 595 Howe Street
Vancouver, Canada V6C 2T5

[EMAIL PROTECTED]

CONFIDENTIALITY NOTICE

This email is intended only for the persons named in the message
header. Unless otherwise indicated, it contains information that is
private and confidential. If you have received it in error, please
notify the sender and delete the entire message including any
attachments.

Thank you.

-Original Message-
From: Darren Duncan [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, March 01, 2006 10:19 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] ORDER BY of UNION?

At 8:59 PM -0800 3/1/06, Boris Popov wrote:
>I can't seem to get unions to sort properly,
>
>SELECT DISTINCT * FROM (SELECT t1.ID
>FROM GR_ADDRESS t1 UNION ALL SELECT t1.ID
>FROM PERSON t1) t1 ORDER BY t1.ID DESC
>
>results in "no such column: t1.ID" error. How would I go about sorting the
>result set in this case?
>
>Cheers!
>-Boris

Try removing the "t1." from both inner select statements, so it just 
says "select id" in both places. -- Darren Duncan


smime.p7s
Description: S/MIME cryptographic signature

Re: [sqlite] Problem with (??) INNER JOIN

[Jay Sprenkle]

On 3/2/06, erw2 <[EMAIL PROTECTED]> wrote:
>
> Now I try to execute SQL query:
> SELECT PomiarBledow.IDpomiaru, Samochody.Samochod
> FROM (PomiarBledow INNER JOIN Modele ON PomiarBledow.IDmod =
> Modele.IDmod) INNER JOIN Samochody ON Modele.IDsam = Samochody.IDsam
> WHERE (((PomiarBledow.IDpomiaru)=2));
>
> and I get en error: "Error 1 - No such column: PomiarBledow.IDpomiaru"
> In MSAccess there is no problem to execute such query. What is wrong
> with it?
>

does this work?
SELECT PomiarBledow.IDpomiaru, Samochody.Samochod
   FROM PomiarBledow
 INNER JOIN Modele ON PomiarBledow.IDmod = Modele.IDmod
 INNER JOIN Samochody ON Modele.IDsam = Samochody.IDsam
WHERE PomiarBledow.IDpomiaru=2

[sqlite] Problem with (??) INNER JOIN

[erw2]

Hi sqlite-users,
I have a following problem. In my database there are 3 table:
CREATE TABLE [Samochody]
(
[IDsam] integer, 
[Samochod] varchar (50),
PRIMARY KEY ([IDsam])
);

CREATE TABLE [Modele]
(
[IDmod] integer,
[IDsam] integer, 
[Model] varchar (50),
PRIMARY KEY ([IDmod])
);
CREATE TABLE [PomiarBledow]
(
[IDpomiaru] integer, 
[IDmod] integer, 
[IDkol] integer,
PRIMARY KEY ([IDpomiaru])
);

In tables are some hypothetical data:
INSERT INTO [Samochody] ([Samochod]) VALUES ('Number1');  /-> IDsam=1;

INSERT INTO [Modele] ([IDsam], [Model]) VALUES ('1', 'Bus');   /->
IDmod=1;
INSERT INTO [Modele] ([IDsam], [Model]) VALUES ('1', 'Truck'); /->
IDmod=2;

INSERT INTO [PomiarBledow] ([IDmod], [IDkol]) VALUES ('1', '1');  /->
IDpomiaru=1;
INSERT INTO [PomiarBledow] ([IDmod], [IDkol]) VALUES ('1', '2');  /->
IDpomiaru=2;
INSERT INTO [PomiarBledow] ([IDmod], [IDkol]) VALUES ('2', '4');  /->
IDpomiaru=3;
INSERT INTO [PomiarBledow] ([IDmod], [IDkol]) VALUES ('2', '5');  /->
IDpomiaru=4;

Now I try to execute SQL query:
SELECT PomiarBledow.IDpomiaru, Samochody.Samochod
FROM (PomiarBledow INNER JOIN Modele ON PomiarBledow.IDmod =
Modele.IDmod) INNER JOIN Samochody ON Modele.IDsam = Samochody.IDsam
WHERE (((PomiarBledow.IDpomiaru)=2));  

and I get en error: "Error 1 - No such column: PomiarBledow.IDpomiaru"
In MSAccess there is no problem to execute such query. What is wrong
with it?

Regards
WojciechW.

Re: [sqlite] Create table IF NOT EXISTS

[Thomas Chust]

On Thu, 2 Mar 2006, Sripathi Raj wrote:


[...]
Is the IF NOT EXISTS constraint supported? When I try to use it, I get an
error message saying that there is a syntax error at 'NOT'? I'm using Sqlite
3 on Windows and I'm trying to connect to it from Perl.
[...]


Hello,

the information that you are using version 3 is not precise enough to 
answer this question with yes or no ;)


I know the IF [NOT] EXISTS syntax is *not* supported in SQLite 3.1.3 but 
it *is* supported in SQLite 3.3.4. A quick glance at the changelog says 
the syntax was introduced with 3.3.0 alpha.


However I recently ran into the pitfall, that with SQLite 3.3.4 the 
compilation of an IF [NOT] EXISTS qualified statement may result in a NULL 
statement handle being returned in case the IF [NOT] EXISTS clause does 
not apply. If this is a problem for you, you should use the current CVS 
version of SQLite3 which has that behaviour fixed.


cu,
Thomas

Re: [sqlite] Create table IF NOT EXISTS

[Eugen Stoianovici]

Sripathi Raj wrote:


Hi,

Is the IF NOT EXISTS constraint supported? When I try to use it, I get an
error message saying that there is a syntax error at 'NOT'? I'm using Sqlite
3 on Windows and I'm trying to connect to it from Perl.

Thanks,

Raj

 

I can't use it either..same error so i guessed it is not suported. I 
didn't pay much attention to it though. I use c++ to connect to my db 
and i just catch an exception if the table already exists.

Hope this helps

[sqlite] Create table IF NOT EXISTS

[Sripathi Raj]

Hi,

 Is the IF NOT EXISTS constraint supported? When I try to use it, I get an
error message saying that there is a syntax error at 'NOT'? I'm using Sqlite
3 on Windows and I'm trying to connect to it from Perl.

Thanks,

Raj

AW: [sqlite] Vacuum slow

[Eggert, Henri]

 

-Ursprüngliche Nachricht-
Von: Jay Sprenkle [mailto:[EMAIL PROTECTED] 
Gesendet: Donnerstag, 2. März 2006 17:21
An: sqlite-users@sqlite.org
Betreff: Re: [sqlite] Vacuum slow

> But I wonder :
>
> if I have a db ~ 1gb and I delete all the data in the tables ( db is
> than nearly empty )
> Issuing a vacuum command takes a long time ( several minutes ).
> Why ?
> Is there a way to "vacuum" faster ?

If you delete ALL the data you can just delete the database file
then create a new empty one.

I do not delete all the data but a great part of it.
And I want to preserve all the tables ( even empty ) in the db.
Recreating a new db means recreating all the tables , triggers , ...

Re: [sqlite] Vacuum slow

[drh]

"Eggert, Henri" <[EMAIL PROTECTED]> wrote:
> Hi all,
> 
> I use sqlite 3.3.4 and I am very happy with it - it is very fast.
> 
> But I wonder : 
> 
> if I have a db ~ 1gb and I delete all the data in the tables ( db is
> than nearly empty )
> Issuing a vacuum command takes a long time ( several minutes ).
> Why ?
> Is there a way to "vacuum" faster ?
> 

The VACUUM command does something very much like this:

sqlite3 olddb .dump | sqlite3 newdb; mv newdb olddb

I say "much like" the above because there are some
important differences.  The VACUUM command transfers
the data from the old and new database in binary
without having to convert it into text.  And the
whole operation is protected by a rollback journal
so that if a power failure occurs in the middle, the
operation will rollback.

But the point is this:  VACUUM recreates the whole
database from scratch.  The time required is proportional
to the amount of data that is in the database.

--
D. Richard Hipp   <[EMAIL PROTECTED]>

RE: [sqlite] Vacuum slow

[Allan, Mark]

> But I wonder : 
> 
> if I have a db ~ 1gb and I delete all the data in the tables ( db is
> than nearly empty )
> Issuing a vacuum command takes a long time ( several minutes ).
> Why ?
> Is there a way to "vacuum" faster ?

We found that vacuuming the database was also slow. We no longer vaccum the 
database at all, vaccumming only frees up unused pages, however new additions 
will re-use the free pages so there is no wastage from not vacuuming. If we 
need to delete all records from all tables from the database we simply delete 
the whole file from the filesystem and recreate a new empty one, this is much 
quicker.


DISCLAIMER:
This information and any attachments contained in this email message is 
intended only for the use of the individual or entity to which it is addressed 
and may contain information that is privileged, confidential, and exempt from 
disclosure under applicable law.  If the reader of this message is not the 
intended recipient, or the employee or agent responsible for delivering the 
message to the intended recipient, you are hereby notified that any 
dissemination, distribution, forwarding, or copying of this communication is 
strictly prohibited.  If you have received this communication in error, please 
notify the sender immediately by return email, and delete the original message 
immediately.

Re[2]: [sqlite] Vacuum slow

[Teg]

Hello Jay,

Thursday, March 2, 2006, 11:20:50 AM, you wrote:

>> But I wonder :
>>
>> if I have a db ~ 1gb and I delete all the data in the tables ( db is
>> than nearly empty )
>> Issuing a vacuum command takes a long time ( several minutes ).
>> Why ?
>> Is there a way to "vacuum" faster ?

JS> If you delete ALL the data you can just delete the database file
JS> then create a new empty one.

That's kind of not his point. If he deletes all but one record the same
thing applies. His question is valid if true. I've found vacuum to be
be slow also but, don't know how to quantify it.





-- 
Best regards,
 Tegmailto:[EMAIL PROTECTED]

Re: [sqlite] Vacuum slow

[Jay Sprenkle]

> But I wonder :
>
> if I have a db ~ 1gb and I delete all the data in the tables ( db is
> than nearly empty )
> Issuing a vacuum command takes a long time ( several minutes ).
> Why ?
> Is there a way to "vacuum" faster ?

If you delete ALL the data you can just delete the database file
then create a new empty one.

[sqlite] Vacuum slow

[Eggert, Henri]

Hi all,

I use sqlite 3.3.4 and I am very happy with it - it is very fast.

But I wonder : 

if I have a db ~ 1gb and I delete all the data in the tables ( db is
than nearly empty )
Issuing a vacuum command takes a long time ( several minutes ).
Why ?
Is there a way to "vacuum" faster ?

Thank's for any help.

Henri

Re: [sqlite] Help!!!

[Derrell . Lipman]

"Jay Sprenkle" <[EMAIL PROTECTED]> writes:

>> What I think you're really asking, though, is about the rows that are NOT
>> returned because there are null values.  To fix that, you're probably looking
>> for LEFT OUTER JOIN:
>
> Huh?  I thought left join and left outer join were equivalent.
>
> SQLite version 3.0.8
> Enter ".help" for instructions
> sqlite> create table x( id int, text char(8) );
> sqlite> insert into x values(1,'one');
> sqlite> insert into x values(2,'two');
> sqlite> insert into x values(3,'three');
> sqlite> create table y( id int, text char(8) );
> sqlite> insert into y values(3,'three');
> sqlite> select * from x inner join y on y.id = x.id;
> 3|three|3|three
> sqlite> select * from x left join y on y.id = x.id;
> 1|one||
> 2|two||
> 3|three|3|three
> sqlite> select * from x left outer join y on y.id = x.id;
> 1|one||
> 2|two||
> 3|three|3|three
> sqlite>

It looks like a bad recollection on my part.  I've always remembered that LEFT
JOIN was equivalent to LEFT INNER JOIN so I've always specified LEFT OUTER
JOIN when I wanted that.  (It's probably clearer to specify it anyway, I
guess.)

They say that memory is the second thing to go.  I can't remember what the
first is. :-)

Derrell

Re: [sqlite] Help!!!

[Jay Sprenkle]

> What I think you're really asking, though, is about the rows that are NOT
> returned because there are null values.  To fix that, you're probably looking
> for LEFT OUTER JOIN:

Huh?  I thought left join and left outer join were equivalent.

SQLite version 3.0.8
Enter ".help" for instructions
sqlite> create table x( id int, text char(8) );
sqlite> insert into x values(1,'one');
sqlite> insert into x values(2,'two');
sqlite> insert into x values(3,'three');
sqlite> create table y( id int, text char(8) );
sqlite> insert into y values(3,'three');
sqlite> select * from x inner join y on y.id = x.id;
3|three|3|three
sqlite> select * from x left join y on y.id = x.id;
1|one||
2|two||
3|three|3|three
sqlite> select * from x left outer join y on y.id = x.id;
1|one||
2|two||
3|three|3|three
sqlite>

Re: [sqlite] Help!!!

[Derrell . Lipman]

Roger <[EMAIL PROTECTED]> writes:

> Select p.name||p1.name||p2.name
> from people as p left join user as u as
> u.uid = p.pid left joun people as p2 on
> u.uid=p2.pid left join people as p3 on
> u.uid=p3.pid;
>
> The problem is that if one of the rows does not have a value, it returns
> and empty string.Now in my report i end up with lots of null columns.
> Please help, how do i return something at least from this query if one
> of the columns has a null value in it.

I think you're asking two different questions here.

Firstly, to replace an empty string with something non-empty, you can use
COALESCE():

  SELECT COALESCE(p.name, 'empty') ||
 COALESCE(p1.name, 'empty') ||
 COALESCE(p2.name, 'empty')
  FROM people p

  LEFT JOIN user u
ON u.uid = p.pid

  LEFT JOIN people p2
ON u.uid=p2.pid

  LEFT JOIN people p3
ON u.uid=p3.pid;

What I think you're really asking, though, is about the rows that are NOT
returned because there are null values.  To fix that, you're probably looking
for LEFT OUTER JOIN:

  SELECT p.name || p1.name || p2.name
FROM people p

LEFT OUTER JOIN user u
  ON u.uid = p.pid

LEFT OUTER JOIN people p2
  ON u.uid=p2.pid

LEFT OUTER JOIN people p3
  ON u.uid=p3.pid;

Derrell

ps. if you copy/paste the query from your source instead of retyping it,
you'll end up with many fewer typos, and will make it easier for people to
help you.  Your query was full of typos.

Re: [sqlite] Help!!!

[Martin Engelschalk]

Hi,

use coalesce(p1.name, 'empty') to substitute a text 'empty' for a null 
column.


Martin

Roger schrieb:


Please help me, i am running out of time.

I am building a web based application which runs with on sqlite as the
database.I am now creating my reports using Agata.

My problem is as follows.

I am trying to create a view so that my report tool queries directly
from the view as follows

Select p.name||p1.name||p2.name
from people as p left join user as u as
u.uid = p.pid left joun people as p2 on
u.uid=p2.pid left join people as p3 on
u.uid=p3.pid;

The problem is that if one of the rows does not have a value, it returns
and empty string.Now in my report i end up with lots of null columns.
Please help, how do i return something at least from this query if one
of the columns has a null value in it.

Regards

Roger

 

[sqlite] Help!!!

[Roger]

Please help me, i am running out of time.

I am building a web based application which runs with on sqlite as the
database.I am now creating my reports using Agata.

My problem is as follows.

I am trying to create a view so that my report tool queries directly
from the view as follows

Select p.name||p1.name||p2.name
from people as p left join user as u as
u.uid = p.pid left joun people as p2 on
u.uid=p2.pid left join people as p3 on
u.uid=p3.pid;

The problem is that if one of the rows does not have a value, it returns
and empty string.Now in my report i end up with lots of null columns.
Please help, how do i return something at least from this query if one
of the columns has a null value in it.

Regards

Roger

Re: [sqlite] Running App state in db?

[Elrond]

Thanks to everyone answering in this thread!

On Wed, Mar 01, 2006 at 05:44:45PM -0800, Nathaniel Smith wrote:
[...]
> I don't know enough about your app to know if this would work, but
> if you only have the one app instance managing locks, and locks expire
> whenever that app dies... perhaps you can just have that app, on
> startup, unconditionally erase all locks in the db?

I'll do that probably anyway. (I'm considering killing the
file and recreating the schema on "boot".)
My app is a server and forks off some childs for the
clients. So if the child crashes, those lock records should
go away.


Elrond

[sqlite] [OT] SQLite devpak

[Danilo]

Hi sqlite-users,
is now available the "devpak" for the IDE Dev-Cpp and Code::Blocks of the last 
release of SQLite 3.3.4.

http://www.digitazero.org/2006/03/02/development-packages-by-digitazeroorg-eng/

Cheers, Danilo.
Home Page: http://www.digitazero.org
giovedì 2 marzo 2006, 10.31




___ 
Yahoo! Messenger with Voice: chiama da PC a telefono a tariffe esclusive 
http://it.messenger.yahoo.com