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 will still probably get good query plans, but the > > startup time should be reduced. > > > > Thanks you for analysis of the data, Richard. > I am going to try your advice and post the results. > I've done different tests, and my results are: - Compiling without stat4(3) is equivalent to simply dropping sqlite_stat4(3) table, and as stated before, on average it takes 1.2 ms (with) vs 0.4 ms (without) to open database. That is very good, because I don't have to compile two different version of library. - It takes roughly same time to read sqlite_stat3 as it takes to read sqlite_stat4, sqlite_stat3 wins by a few microseconds. So if I'd ever choose to ignore additional open overhead, I'd definitely go for sqlite_stat4. - Compiling with USE_PREAD macro saves a dozen more microseconds. I'll stick to the advice and drop sqlite_stat4 table, leaving only sqlite_stat1. Thanks everyone for help! Best regards, Paul ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Online/Hot backup of WAL journalling mode database
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 read transaction, b) process B opens and commits a write transaction to the database, c) process C checkpoints the db, then the db file considered without the *-wal file may be corrupt. The problem comes about because process C can only checkpoint frames up until the start of B's transaction. And there is an optimization that will prevent it from copying any earlier frames for which there exists a frame in B's transaction that corresponds to the same database page. So it effectively copis only a subset of the modifications made by earlier transactions into the db file - not necessarily creating a valid db file. Can this corruption be detected by running PRAGMA quick_check / integrity_check? Having the occasional backup db corrupted would be tolerable. In many cases, but not generally. There would exist cases where a part of a committed transaction was lost, or the values in unindexed columns where replaced, that sort of thing. Ok. Presumably a SQLITE_CHECKPOINT_FULL or SQLITE_CHECKPOINT_RESTART checkpoint mode would ensure the db file is valid? That sounds right. A successful FULL or RESTART checkpoint will always copy entire transactions into the db. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Online/Hot backup of WAL journalling mode database
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 stop > and start over again. So if you have frequent writes then > theoretically the backup API would not complete. The ".backup" command of the sqlite3 command-line shell uses a step size of 100 pages, i.e., gives other processes opportunities to write regularly. 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. Regards, Clemens ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Counting rows
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 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 than a few hours ? Try SELECT count(myTable.indexed_column) FROM myTable; indexed_column is a column of your table that is the leftmost NOTNULL column in a index, for example the rowid. count(*) will look at every row in the table pages. The count(myTable.indexed_column) with the restrictions I said, should use the index, which (again) should use a lot less pages, minimizing the I/O. > Simon. --- --- Eduardo Morras ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Online/Hot backup of WAL journalling mode database
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 (every few seconds) writing to the database he needs to copy. So any operation which locks the database for a long period would mean that some data was not captured. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Online/Hot backup of WAL journalling mode database
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 which are constantly > (every few seconds) writing to the database he needs to copy. So any > operation which locks the database for a long period would mean that > some data was not captured. This is not a problem because he uses WAL mode. Regards, Clemens ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Query Planner for Virtual Tables: link table evaluation & transitive property of constraints not used
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 the execution plan (I think this was implemented in the 3.7.17 release). Unfortunately for virtual tables this does not seem to be supported. I can overcome this limitation by manually tuning the SQL, but it will help if the query planner can do this automatically. The major problem I have is with link table evaluation. Imagine a SQL like "select * from A join B on A.ID=B.ID join C on C.ID=B.LINKID". The current implementation evaluates cost of B only as B (ID, LINKID) causing the execution to perform a full scan on either A or C. This seems to be caused by the implementation of whereLoopAddVirtual() function. I think it should evaluate cost for terms separated by tables in the right term as well, e.g. for the mentioned SQL, table B, it should try B(), B(ID), B(LINKID), B(ID, LINKID) instead of only B() and B(ID, LINKID). What should I do? Best regards, Joe ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
