On Mon, Dec 7, 2015 at 11:51 PM, Simon Slavin <slavins at bigfraud.org> wrote:
>
> On 8 Dec 2015, at 12:19am, Patrick Donnelly <batrick at batbytes.com> wrote:
>
>> There are still writes:
>
> Because you have not defined any transactions, each of your INSERT commands
> it getting wrapped in its own transaction. A transaction has to involve
> writes to disk.
>
> Try this ...
>
> BEGIN
> INSERT INTO t1 ...
> END
>
> See whether the INSERT command still involves as much writing.
This problem is not related to transactions. Richard Hipp posted an
example [1] where there are no writes for an "INSERT INTO T SELECT..."
on a table (without AUTOINCREMENT), without any explicit
transactions.
My own test example without AUTOINCREMENT has no writes:
$ cat test2.sql
CREATE TABLE t1(a INTEGER PRIMARY KEY, b integer);
INSERT INTO t1(b) VALUES (1);
.print ---------------------------------
begin transaction;
INSERT INTO t1 (b)
SELECT 0
WHERE 1 = 0;
end transaction;
select changes();
$ diff test.sql test2.sql
1c1
< CREATE TABLE t1(a INTEGER PRIMARY KEY AUTOINCREMENT, b integer);
---
> CREATE TABLE t1(a INTEGER PRIMARY KEY, b integer);
3d2
< select * from sqlite_sequence;
$ rm foo.db*; ./sqlite3 -vfstrace foo.db < test2.sql
...
---------------------------------
trace.xLock(foo.db,SHARED) -> SQLITE_OK
trace.xAccess("/home/batrick/sqlite-amalgamation-3090200/foo.db-journal",0)
-> SQLITE_OK, out=0
trace.xFileSize(foo.db) -> SQLITE_OK, size=2048
trace.xRead(foo.db,n=16,ofst=24) -> SQLITE_OK
trace.xFileSize(foo.db) -> SQLITE_OK, size=2048
trace.xAccess("/home/batrick/sqlite-amalgamation-3090200/foo.db-wal",0)
-> SQLITE_OK, out=0
trace.xFileSize(foo.db) -> SQLITE_OK, size=2048
trace.xLock(foo.db,RESERVED) -> SQLITE_OK
trace.xLock(foo.db,EXCLUSIVE) -> 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
[1]
http://mailinglists.sqlite.org/cgi-bin/mailman/private/sqlite-users/2014-May/052855.html
--
Patrick Donnelly
