[sqlite] Query Planner for Virtual Tables: link table evaluation & transitive property of constraints not used

2014-12-12 Thread Josef Kučera
Hello, I am trying to use SQLite's marvellous Virtual Table mechanism as a SQL layer for querying an in memory storage. This works good, but I have a problem with more complex queries. When querying a real SQLite database it correctly moves the constant conditions across joined tables to optimize

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

2014-12-12 Thread Clemens Ladisch
Simon Slavin wrote: > On 12 Dec 2014, at 10:27am, Clemens Ladisch wrote: >> If you write your own backup tool that simply calls >> "sqlite3_backup_step(b, -1)", the entire database is copied in >> a single atomic transaction. > > OP's problem is that he runs several processes

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

2014-12-12 Thread Simon Slavin
On 12 Dec 2014, at 10:27am, Clemens Ladisch wrote: > If you write your own backup tool that simply calls > "sqlite3_backup_step(b, -1)", the entire database is copied in > a single atomic transaction. OP's problem is that he runs several processes which are constantly

Re: [sqlite] Counting rows

2014-12-12 Thread Eduardo Morras
On Thu, 11 Dec 2014 15:19:26 + 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

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

2014-12-12 Thread Clemens Ladisch
Nick wrote: > 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

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

2014-12-12 Thread Dan Kennedy
On 12/12/2014 03:31 AM, Nick wrote: 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

Re: [sqlite] How to speed up database open

2014-12-12 Thread Paul
> > 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