Update on this: On Mon, May 5, 2014 at 4:53 PM, Patrick Donnelly <batrick at batbytes.com> wrote: > Hi, > > I have an INSERT that looks like > > INSERT INTO T > SELECT ... > > which I'm running numerous times a second that generally does nothing > because the SELECT returns no rows. Unfortunately, I've found that > SQLite still does numerous disk writes anyway in this situation. > > Is my only option to eliminate the INSERT by using a SELECT first to > check if there are no rows? Something like: > > CREATE TEMPORARY VIEW V AS > SELECT ... > SELECT COUNT(*) FROM V; > /* If > 0 */ > INSERT INTO T SELECT * FROM V; > > ?
I've been able to reproduce it with this minimal example: CREATE TABLE t1(a INTEGER PRIMARY KEY AUTOINCREMENT); .print --------------------------------- INSERT INTO t1 SELECT 0 WHERE 1 = 2; select changes(); $ ./sqlite3 -vfstrace foo.db < test.sql ... --------------------------------- trace.xLock(foo.db,SHARED) -> SQLITE_OK trace.xAccess("/home/batrick/scm/cctools/chirp/src/sqlite-amalgamation-3090200/foo.db-journal",0) -> SQLITE_OK, out=0 trace.xFileSize(foo.db) -> SQLITE_OK, size=3072 trace.xRead(foo.db,n=16,ofst=24) -> SQLITE_OK trace.xFileSize(foo.db) -> SQLITE_OK, size=3072 trace.xAccess("/home/batrick/scm/cctools/chirp/src/sqlite-amalgamation-3090200/foo.db-wal",0) -> SQLITE_OK, out=0 trace.xFileSize(foo.db) -> SQLITE_OK, size=3072 trace.xLock(foo.db,RESERVED) -> SQLITE_OK trace.xFileControl(foo.db,20) -> SQLITE_OK trace.xOpen(foo.db-journal,flags=0x806) -> SQLITE_OK trace.xDeviceCharacteristics(foo.db) -> 0x00001000 trace.xWrite(foo.db-journal,n=512,ofst=0) -> SQLITE_OK trace.xWrite(foo.db-journal,n=4,ofst=512) -> SQLITE_OK trace.xWrite(foo.db-journal,n=1024,ofst=516) -> SQLITE_OK trace.xWrite(foo.db-journal,n=4,ofst=1540) -> SQLITE_OK trace.xLock(foo.db,EXCLUSIVE) -> SQLITE_OK trace.xWrite(foo.db-journal,n=4,ofst=1544) -> SQLITE_OK trace.xWrite(foo.db-journal,n=1024,ofst=1548) -> SQLITE_OK trace.xWrite(foo.db-journal,n=4,ofst=2572) -> SQLITE_OK trace.xDeviceCharacteristics(foo.db) -> 0x00001000 trace.xRead(foo.db-journal,n=8,ofst=3072) -> SQLITE_IOERR_SHORT_READ trace.xSync(foo.db-journal,FULL) -> 0 trace.xWrite(foo.db-journal,n=12,ofst=0) -> SQLITE_OK trace.xSync(foo.db-journal,FULL) -> 0 trace.xWrite(foo.db,n=1024,ofst=0) -> SQLITE_OK trace.xWrite(foo.db,n=1024,ofst=2048) -> SQLITE_OK trace.xFileControl(foo.db,21) -> 12 trace.xSync(foo.db,FULL) -> 0 trace.xClose(foo.db-journal) -> SQLITE_OK trace.xDelete("/home/batrick/scm/cctools/chirp/src/sqlite-amalgamation-3090200/foo.db-journal",0) -> SQLITE_OK trace.xFileControl(foo.db,22) -> 12 trace.xUnlock(foo.db,SHARED) -> SQLITE_OK trace.xDeviceCharacteristics(foo.db) -> 0x00001000 trace.xUnlock(foo.db,NONE) -> SQLITE_OK 0 trace.xDeviceCharacteristics(foo.db) -> 0x00001000 trace.xUnlock(foo.db,NONE) -> SQLITE_OK trace.xClose(foo.db) -> SQLITE_OK No rows were inserted but there are several writes. This behavior seems to be caused by AUTOINCREMENT? -- Patrick Donnelly