[sqlite] NOP INSERT still writes to the DB/journal

[Patrick Donnelly]

Hello,

On Mon, Dec 7, 2015 at 5:05 PM, Patrick Donnelly  
wrote:
> Update on this:
>
> On Mon, May 5, 2014 at 4:53 PM, Patrick Donnelly  
> 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) -> 0x1000
> 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) -> 0x1000
> 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) -> 0x1000
> trace.xUnlock(foo.db,NONE) -> SQLITE_OK
> 0
> trace.xDeviceCharacteristics(foo.db) -> 0x1000
> 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?

Now that there is a trivial test-case, I was hoping to find this on
the bugs page [1] but nothing yet. Is this not a bug?

[1] http://www.sqlite.org/src/rptview?rn=1

-- 
Patrick Donnelly

[sqlite] NOP INSERT still writes to the DB/journal

[R Smith]

On 2015/12/08 12:16 PM, Domingo Alvarez Duarte wrote:
> If I understood correctly when no transaction is specified an implicit
> transaction is created so there is no point to create a transaction for only
> one statement.

Yes, but what Simon is trying to achieve is to have the transaction 
explicitly record to the journal so as to avoid locking the tables until 
the transaction concludes, which we are hoping would allow the Query 
engine time to realize that there needn't be a table write at all (since 
zero records were selected), and so commit the journal without actually 
locking the table.

Makes sense?


> Try this ...
>
> BEGIN
> INSERT INTO t1 ...
> END
>
> See whether the INSERT command still involves as much writing.
>
> Simon.
>

[sqlite] NOP INSERT still writes to the DB/journal

[Simon Slavin]

On 8 Dec 2015, at 10:16am, Domingo Alvarez Duarte  wrote:

> If I understood correctly when no transaction is specified an implicit
> transaction is created so there is no point to create a transaction for only
> one statement. 

The OP here has lots of INSERT commands and is complaining that they do reading 
and writing even when they have no effect.

My point was that it was the implicit transaction commands (BEGIN and END) 
which were doing the writing.  And this means that every INSERT command does 
some writing even when no rows are actually inserted.

If the OP does a BEGIN first, he should be able do many of his INSERT commands 
and they will not do any writing.

However I have not tested this with the trace routines and it may not turn out 
to be true.

Simon.

[sqlite] NOP INSERT still writes to the DB/journal

[Domingo Alvarez Duarte]

If I understood correctly when no transaction is specified an implicit
transaction is created so there is no point to create a transaction for only
one statement.  

Cheers !  
>  Tue Dec 08 2015 5:51:35 am CET CET from "Simon Slavin"
>  Subject: Re: [sqlite] NOP INSERT still writes to the
>DB/journal
>
>  On 8 Dec 2015, at 12:19am, Patrick Donnelly  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.
> 
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> 
>
>  



?

[sqlite] NOP INSERT still writes to the DB/journal

[Patrick Donnelly]

On Mon, Dec 7, 2015 at 11:51 PM, Simon Slavin  wrote:
>
> On 8 Dec 2015, at 12:19am, Patrick Donnelly  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) -> 0x1000
trace.xUnlock(foo.db,NONE) -> SQLITE_OK
0
trace.xDeviceCharacteristics(foo.db) -> 0x1000
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

[sqlite] NOP INSERT still writes to the DB/journal

[Keith Medcalf]

If you execute an SQL statement in automagic mode, then BEGIN and COMMIT are 
magically wrapped around the statement -- you are absolutely correct and that 
is the purpose of the magic mode.  Therefore doing:

BEGIN;
INSERT ...
COMMIT;

is EXACTLY IDENTICAL to

INSERT 

with full automagic engaged.  The only difference ... tada ... is that you get 
to see the processing associated with performing the magic, which was 
previously obscured from your view (or rather, was plainly obvious but you 
could not distinguish the processing caused by the magic and the processing 
caused by your INSERT ... statement).

I believe Simon's point is that if you use "Manual Magic" then you can see the 
processing associated with that magic, compared to full on automagic, where the 
actions contain such an intermix of magically initiated operations and 
explicitly initiation operations that a primitive observer is unable to tell 
which part is caused by the magic and which part is not.

As Captain Pickard would point out, it explains the magic to a primitive 
culture that has never seen a door before.

> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-
> bounces at mailinglists.sqlite.org] On Behalf Of Domingo Alvarez Duarte
> Sent: Tuesday, 8 December, 2015 03:17
> To: SQLite mailing list
> Subject: Re: [sqlite] NOP INSERT still writes to the DB/journal
> 
> If I understood correctly when no transaction is specified an implicit
> transaction is created so there is no point to create a transaction for
> only
> one statement.
> 
> Cheers !
> >  Tue Dec 08 2015 5:51:35 am CET CET from "Simon Slavin"
> >  Subject: Re: [sqlite] NOP INSERT still writes to
> the
> >DB/journal
> >
> >  On 8 Dec 2015, at 12:19am, Patrick Donnelly 
> 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.
> >
> > Simon.
> > ___
> > sqlite-users mailing list
> > sqlite-users at mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> >
> >
> 
> 
> 
> 
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] NOP INSERT still writes to the DB/journal

[Simon Slavin]

On 8 Dec 2015, at 12:19am, Patrick Donnelly  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.

Simon.

[sqlite] NOP INSERT still writes to the DB/journal

[Patrick Donnelly]

On Mon, Dec 7, 2015 at 5:31 PM, Igor Tandetnik  wrote:
> On 12/7/2015 5:05 PM, Patrick Donnelly wrote:
>>
>> No rows were inserted but there are several writes. This behavior
>> seems to be caused by AUTOINCREMENT?
>
>
> Could be creating sqlite_sequence table where there wasn't one before. I
> wonder if there are still writes on the second and subsequent no-op inserts.

There are still writes:

CREATE TABLE t1(a INTEGER PRIMARY KEY AUTOINCREMENT, b integer);
INSERT INTO t1(b) VALUES (1);
select * from sqlite_sequence;
.print -
INSERT INTO t1 (b)
SELECT 0
WHERE 1 = 0;
select changes();


$ rm foo.db*; ./sqlite3 -vfstrace foo.db < test.sql
...
t1|1
-
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=3072
trace.xRead(foo.db,n=16,ofst=24) -> SQLITE_OK
trace.xFileSize(foo.db) -> SQLITE_OK, size=3072
trace.xAccess("/home/batrick/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) -> 0x1000
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) -> 0x1000
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/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) -> 0x1000
trace.xUnlock(foo.db,NONE) -> SQLITE_OK
0
trace.xDeviceCharacteristics(foo.db) -> 0x1000
trace.xUnlock(foo.db,NONE) -> SQLITE_OK
trace.xClose(foo.db) -> SQLITE_OK


-- 
Patrick Donnelly

[sqlite] NOP INSERT still writes to the DB/journal

[Igor Tandetnik]

On 12/7/2015 5:05 PM, Patrick Donnelly wrote:
> No rows were inserted but there are several writes. This behavior
> seems to be caused by AUTOINCREMENT?

Could be creating sqlite_sequence table where there wasn't one before. I 
wonder if there are still writes on the second and subsequent no-op inserts.
-- 
Igor Tandetnik

[sqlite] NOP INSERT still writes to the DB/journal

[Patrick Donnelly]

Update on this:

On Mon, May 5, 2014 at 4:53 PM, Patrick Donnelly  
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) -> 0x1000
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) -> 0x1000
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) -> 0x1000
trace.xUnlock(foo.db,NONE) -> SQLITE_OK
0
trace.xDeviceCharacteristics(foo.db) -> 0x1000
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

Re: [sqlite] NOP INSERT still writes to the DB/journal

[Christian Smith]

On Mon, May 05, 2014 at 05:00:08PM -0400, Richard Hipp wrote:
> On Mon, May 5, 2014 at 4:53 PM, Patrick Donnelly 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.
> >
> 
> I'm unable to reproduce this behavior.  Here is my test script:
> 
> 
> No writes.
> 

I suspect the culprit is file access time updates. Using something
like relatime mount option under Linux would eliminate atime updates
for files that have not been modified.

Christian
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] NOP INSERT still writes to the DB/journal

[Richard Hipp]

On Mon, May 5, 2014 at 4:53 PM, Patrick Donnelly 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.
>

I'm unable to reproduce this behavior.  Here is my test script:

CREATE TABLE t1(a,b,c);
INSERT INTO t1 VALUES(1,2,3),(7,8,9),(4,5,6);
CREATE TABLE t2(x,y,z);
.print -
INSERT INTO t2 SELECT * FROM t1 WHERE a>10;

Then running vfstrace after the --- I see:

trace.xLock(test.db,SHARED) -> SQLITE_OK
trace.xAccess("/home/drh/sqlite/bld/test.db-journal",0) -> SQLITE_OK, out=0
trace.xFileSize(test.db) -> SQLITE_OK, size=3072
trace.xRead(test.db,n=16,ofst=24) -> SQLITE_OK
trace.xFileSize(test.db) -> SQLITE_OK, size=3072
trace.xAccess("/home/drh/sqlite/bld/test.db-wal",0) -> SQLITE_OK, out=0
trace.xFileSize(test.db) -> SQLITE_OK, size=3072
trace.xLock(test.db,RESERVED) -> SQLITE_OK
trace.xLock(test.db,EXCLUSIVE) -> SQLITE_OK
trace.xFileControl(test.db,22) -> 12
trace.xUnlock(test.db,SHARED) -> SQLITE_OK
trace.xDeviceCharacteristics(test.db) -> 0x1000
trace.xUnlock(test.db,NONE) -> SQLITE_OK
trace.xDeviceCharacteristics(test.db) -> 0x1000
trace.xUnlock(test.db,NONE) -> SQLITE_OK
trace.xClose(test.db) -> SQLITE_OK

No writes.

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] NOP INSERT still writes to the DB/journal

[Patrick Donnelly]

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;

?

Thanks,

-- 
Patrick Donnelly
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users