Re: [sqlite] Behavior of SELECT queries during transaction

[drh]

Thomas DILIGENT <[EMAIL PROTECTED]> wrote:
> 
> That's it. This is what I want to do.
>  From this point, I have the following questions :
> 1) Will this increase speed compared to a basic solution where I would 
> use autocommit mode ? (in other words, is it worthy implementing such a 
> solution ?)
> 2) If yes, how much and how to chose the number of rows between begin 
> and commit ?
> 

The speed of COMMIT is limited by the speed of your disk drive.
Generally speaking, the disk platter has to do at least two complete
revolutions in order complete a COMMIT.  This limits you to about
60 commits per second on a 7200 RPM disk drive.

On the other hand, SQLite can process in excess of 6 INSERT
statements per second on a modern workstation.  So roughly speaking,
INSERT is about 1000 times faster than COMMIT.

So if you do BEGIN, 1000 INSERTs, and then COMMIT, that takes
roughly the same amount of time as doing two INSERT statements
in autocommit mode.

--
D. Richard Hipp <[EMAIL PROTECTED]>


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Behavior of SELECT queries during transaction

[Thomas DILIGENT]

Hi all !

I would like to know what is the behavior of sqlite when performing a 
SELECT query during a transaction ?


For example :

CREATE TABLE thebeatles (_ID INTEGER PRIMARY KEY, name TEXT);

BEGIN TRANSACTION;
INSERT  INTO thebeatles (name) VALUES ('john');
INSERT  INTO thebeatles (name) VALUES ('paul');

SELECT * FROM thebeatles;

INSERT  INTO thebeatles (name) VALUES ('george');
INSERT  INTO thebeatles (name) VALUES ('ringo');
...

COMMIT TRANSATION;

So,
Does sqlite support this kind of sequence (and how ?) or is it wrong ?
What about the performance issue ?

Thanks in advance,
Thomas

-
To unsubscribe, send email to [EMAIL PROTECTED]
-