On Thu, Feb 21, 2013 at 9:51 AM, Joost Voogt <[email protected]> wrote:
> While tracking all the I/O that SQLite does I noticed that the same 16
> bytes are read over and over again. After some investigation I found out
> that it is reading the File change counter to see if another process has
> changed the database.
>
> As in this program there is only one read-only process that has a lock on
> the database there is no way that the database could have been changed. So
> it would be nice, to minimize the amount of reads and gain performance, to
> not read this File change counter all the time.
>
> I tried to accomplish this by opening the database using an exclusive lock
> but that had no effect.
>
> Is there any way to accomplish this?
>
SQLite goes to a lot of trouble to avoid reading the change counter when it
does not have to. And my quick tests just now seem to show that these
efforts work, as the change counter is not read unnecessarily.
Can you provide a test case that demonstrates the change counter being
tested when it does not need to be?
My test case is this:
BEGIN;
CREATE TABLE t1(a);
INSERT INTO t1 VALUES(1),(null),(randomblob(2000)),(4.5);
INSERT INTO t1 SELECT a+1 FROM t1;
CREATE TABLE t2 AS SELECT * FROM t1;
COMMIT;
.print
-----------------------------------------------------------------------
PRAGMA locking_mode=EXCLUSIVE;
SELECT typeof(a) FROM t1;
.print
***********************************************************************
SELECT typeof(a) FROM t1;
Store the above in a file (say x1.txt) then:
strace sqlite3 test.db <x1.txt
No reads occurs for the second query. But if the PRAGMA is commented out,
then the change counter is read (to verify that it has not changed) for the
second query.
--
D. Richard Hipp
[email protected]
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
