Re: [sqlite] Online/Hot backup of WAL journalling mode database

On 12/11/2014 05:49 AM, Nick wrote: On 10 Dec 2014, at 07:35, Dan Kennedy wrote: Strictly speaking the database file may not be well-formed even if there is no ongoing checkpoint. If: a) process A opens a read transaction, b) process B opens and commits a write transaction to the database

Re: [sqlite] Online/Hot backup of WAL journalling mode database

On 10 Dec 2014, at 10:40pm, Nick wrote: > All the processes would have automatic checkpointing disabled. Just the > backup process would perform the checkpoint. I don't know enough about the internals of SQLite to be sure, but various parts of me are concerned that this is a bad idea. I don'

[sqlite] How to speed up database open

Hello. In my specific case I need to open database as fast as possible. Usual working cycle: open -> select small data set -> close. It is irrelevant how much time it takes to open database when data is being added or updated, since it happens not too often. But for selects it's a different story

Re: [sqlite] How to speed up database open

On 11 Dec 2014, at 11:51am, Paul wrote: > I understand, that having them is a must for a decent performance. > In my specific case I have millions of individual database files. > This is one, among other reasons that I can't keep them open all the time. > Just too many of them. These database

Re: [sqlite] How to speed up database open

On Thu, Dec 11, 2014 at 6:51 AM, Paul wrote: > > Hello. > > In my specific case I need to open database as fast as possible. > Usual working cycle: open -> select small data set -> close. > It is irrelevant how much time it takes to open database when > data is being added or updated, since it ha

Re: [sqlite] How to speed up database open

Hello, Simon. > > On 11 Dec 2014, at 11:51am, Paul wrote: > > > I understand, that having them is a must for a decent performance. > > In my specific case I have millions of individual database files. > > This is one, among other reasons that I can't keep them open all the time. > > Just too

[sqlite] backup fails despite large timeout

Hi, I'm using the following code to backup a 300MB database nightly: #!/bin/bash sqlite3 {dbfile}

Re: [sqlite] replace many rows with one

On 10 Dec 2014, at 3:40pm, RSmith wrote: > INSERT INTO s2merged SELECT a, b, sum(theCount) FROM s2 GROUP BY a,b; Thanks to Martin, Hick and R for this solution. It was just what I was looking for. > Not sure if your theCount field already contains totals or if it just has > 1's... how did

Re: [sqlite] backup fails despite large timeout

That'd depend on the size of the database, the medias speed you're writing to, and what kind of actions are happening during the backup. If your file size is in the GB range, and you're transferring over a 100mbit switch, if you have a SINGLE write per minute, your backup is going to restart. Rea

Re: [sqlite] How to speed up database open

Hello, Richard, thanks for quick reply. Unfortunately, no, there is no way. On our servers we have big number of entities that represent client data. Data for different clients can be read at any given point of time by clients and by a bunch of daemons responsible for different maintenance jobs.

Re: [sqlite] How to speed up database open

PART 2 INSERT INTO sqlite_stat4 VALUES('LLL','LLL_idx','676 1 1','0 330 330','0 327 330',X'0408040253be558403a9'); INSERT INTO sqlite_stat4 VALUES('LLL','LLL_idx','676 1 1','0 661 661','0 655 661',X'04080402547bf6900b13'); INSERT INTO sqlite_stat4 VALUES('LLL','LLL_idx','1093 5 1','676 720 723',

Re: [sqlite] How to speed up database open

On 2014/12/11 13:51, Paul wrote: In my specific case I need to open database as fast as possible. Usual working cycle: open -> select small data set -> close. It is irrelevant how much time it takes to open database when data is being added or updated, since it happens not too often. /Snipped

[sqlite] Counting rows

In my table which had about 300 million (sic.) rows I did this SELECT count(*) FROM myTable; to count the number of rows. After half an hour it was still processing and I had to kill it. I know that the internal structure of a table means that this number isn't simple to produce. But is ther

Re: [sqlite] How to speed up database open

Hi Ryan, thanks for reply. > > On 2014/12/11 13:51, Paul wrote: > > In my specific case I need to open database as fast as possible. > > Usual working cycle: open -> select small data set -> close. > > It is irrelevant how much time it takes to open database when > > data is being added or update

Re: [sqlite] Counting rows

would count _rowid_ from mytable be quicker Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit -Forensic Toolkit for SQLite http://sandersonforensics.com/forum/content.php?168-

[sqlite] Select count(*)

I asked a similar question some time ago... See here: https://www.mail-archive.com/sqlite-users@sqlite.org/msg77488.html Gabriel ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to speed up database open

On Thu, Dec 11, 2014 at 10:58 AM, Paul wrote: > > I have yet to try and test if dropping stat tables worth the effort. > Most of the work is involved in loading sqlite_stat4. On the other hand, most of the benefit comes from sqlite_stat1. So consider compiling without SQLITE_ENABLE_STAT4. You

Re: [sqlite] Counting rows

On 2014/12/11 17:19, Simon Slavin wrote: In my table which had about 300 million (sic.) rows I did this SELECT count(*) FROM myTable; to count the number of rows. After half an hour it was still processing and I had to kill it. I know that the internal structure of a table means that this

Re: [sqlite] Counting rows

Simon Slavin wrote on Thursday, December 11, 2014 10:19 AM > I know that the internal structure of a table means that this number > isn't simple to produce. But is there really no faster way ? This > table is going to have about six times that amount soon. I really > can't count the rows in less

Re: [sqlite] Select count(*)

On Thu, Dec 11, 2014 at 5:03 PM, Gabriel Corneanu wrote: > I asked a similar question some time ago... > See here: > https://www.mail-archive.com/sqlite-users@sqlite.org/msg77488.html >> not a problem for rowid/pk (which are not allowed to be NULL), but it >> matters a lot in the general case.

Re: [sqlite] How to speed up database open

> On Thu, Dec 11, 2014 at 10:58 AM, Paul wrote: > > > > > I have yet to try and test if dropping stat tables worth the effort. > > > > Most of the work is involved in loading sqlite_stat4. On the other hand, > most of the benefit comes from sqlite_stat1. So consider compiling without > SQLITE_EN

Re: [sqlite] Select count(*)

I believe that when NULLs are allowed as PKs, they are all distinct. So, you can multiple rows with a NULL value as the PK. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Dominique Devienne Sent: Thursday, December 11, 2014

Re: [sqlite] How to speed up database open

On 2014/12/11 17:58, Paul wrote: On 2014/12/11 13:51, Paul wrote: I have yet to try and test if dropping stat tables worth the effort. Some databases in fact can grow pretty big, up to few hundred of megabytes// In that case maybe keep the Stat1 tables and there is also the option of u

Re: [sqlite] Select count(*)

On Thu, Dec 11, 2014 at 11:27 AM, Dominique Devienne wrote: > > So why couldn't sqlite using the PK index to reduce the IO when doing a > "select count(*) from t_with_non_int_pk", to avoid scanning the table? > It does. -- D. Richard Hipp d...@sqlite.org

Re: [sqlite] Counting rows

On 11 Dec 2014, at 3:58pm, Paul Sanderson wrote: > would count _rowid_ from mytable be quicker Hmm. Given that these tables have the normal use of rowid, and that rows in this table are only inserted, never deleted, I wonder whether SELECT max(rowid) FROM myTable would have given the right

Re: [sqlite] Counting rows

On Thu, Dec 11, 2014 at 4:19 PM, Simon Slavin wrote: > In my table which had about 300 million (sic.) rows I did this > SELECT count(*) FROM myTable; > to count the number of rows. After half an hour it was still processing > and I had to kill it. > I have a little utility that connects to Orac

Re: [sqlite] Counting rows

On 11 Dec 2014, at 4:39pm, Dominique Devienne wrote: > I have a little utility that connects to Oracle, and does a big UNION ALL > query to get the counts of all my tables (82 currently): Yeah, it's easy in Oracle. The problem is that SQLite3 uses a tree to store lists, and it does not store

Re: [sqlite] Counting rows

>From previous reading (years ago on this list) I normally do select count(1) from tableName ; to count the rows in a table. as an alternate, select count(primary_key_or_SomeIndexName) from tableName when trying to get an actual count. beware: select count(someField) from table; will not count r

Re: [sqlite] How to speed up database open

> > On 2014/12/11 17:58, Paul wrote: > > > >> On 2014/12/11 13:51, Paul wrote: > >> I have yet to try and test if dropping stat tables worth the effort. Some > >> databases in fact can grow pretty big, up to few > >> hundred of megabytes// > > In that case maybe keep the Stat1 tables and

Re: [sqlite] Online/Hot backup of WAL journalling mode database

On 11 Dec 2014, at 10:08, Dan Kennedy wrote: > On 12/11/2014 05:49 AM, Nick wrote: >> On 10 Dec 2014, at 07:35, Dan Kennedy wrote: >> >>> Strictly speaking the database file may not be well-formed even if there is >>> no ongoing checkpoint. If: >>> >>> a) process A opens a read transaction, >

Re: [sqlite] Online/Hot backup of WAL journalling mode database

On 11 Dec 2014, at 10:43, Simon Slavin wrote: > > I don't know enough about the internals of SQLite to be sure, but various > parts of me are concerned that this is a bad idea. I don't know what WAL > mode would be like without checkpointing but there has to be a reason for > checkpointing a

Re: [sqlite] Online/Hot backup of WAL journalling mode database

Why are you trying to hard to avoid using the backup API? It sounds like it does exactly what you want On 11 Dec 2014, at 12:36, Nick wrote: > > On 11 Dec 2014, at 10:43, Simon Slavin wrote: > >> >> I don't know enough about the internals of SQLite to be sure, but various >> parts of me a

Re: [sqlite] Online/Hot backup of WAL journalling mode database

On 11 Dec 2014, at 20:39, David King wrote: > Why are you trying to hard to avoid using the backup API? It sounds like it > does exactly what you want Backup API works great if you have periods of no writing. However, if a process writes during the backup then the API would stop and start over

Re: [sqlite] Bugreport - slowdown in sqlite after the ANALYZE statement

On Wed, 10 Dec 2014 08:49:21 +0100 Eduardo Morras wrote: > > Why ORDER BY on INSERT? Does it work better? I would expect the > > unnecessary sort to be pure overhead. > > If you insert in correct index order, the index update phase is > faster because it don't need rebalance the b-tree so of

Re: [sqlite] How do I update multiple rows in a single sql statement

On Tue, 09 Dec 2014 10:46:23 -0500 Igor Tandetnik wrote: > On 12/9/2014 10:38 AM, James K. Lowden wrote: > > If the subquery to the right of the SET clause produces > > more than one row, the statement fails. > > Are you sure? Normally, a scalar subquery doesn't fail when the > resultset contai