Re: [sqlite] Flush to disc
Clay Dowling wrote: Edovia Technologies said: It works, although it takes quite a while to transfer data. Like about a minute for 500 records! Wrap each table into a transaction. You'll be shocked at how much faster it is. I have a similar application to transfer a 2.5 MB paradox database. It took hours before I added the transaction, and minutes after. Steven Van Ingelgem wrote: How can I make sure after an insert that everything has been flushed to disc? SQLite flushes everything to the disk whenever it COMMITs and a COMMIT is implied after each INSERT unless you explicitly start a multi-statement transaction using BEGIN. Flushing to the disk is slow. It normally takes about 2 complete revolutions of the disk platter to complete the flush. On a 7200RPM disk drive, that comes to 1/60th of a second. Consequently, you are hardward limited to about 60 COMMITs per second. That's why it take so long to do a bunch of INSERTs that are not inside a transaction. After each INSERT, SQLite has to wait for the disk flush to complete which takes about 1/60th of a second. On the other hand, if you put all the INSERTs inside BEGIN...COMMIT, no waiting has to occur until the final COMMIT. This can speed up your inserts by 2 or 3 orders of magnitude - literally. -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
Re: [sqlite] Flush to disc
No, I have none of the below... It was my own stupid mistake... I was like "SELECT MAX(ID) FROM table" and then I was like ... Mmmm, start again from that max-id... Of course if it is already in the database it has already been reworked... So I had to do +1 :p Thx & sorry for the timewaste At 22:50 3/11/2004, you wrote: On 11/03/2004 08:48 PM, Reid Thompson wrote: > [EMAIL PROTECTED] wrote: > >>In a message dated 11/3/2004 12:20:36 PM Eastern Standard Time, >>[EMAIL PROTECTED] writes: >> >>How can I make sure after an insert that everything has been flushed >>to disc? >> >>I mean, I want even my program to become slower as long as I know >> everything have been written to disc. >> >>At the moment I am using: >>PRAGMA default_synchronous = FULL; >>PRAGMA synchronous = FULL; >> >> >>And the queries are all INSERT's without a transaction. >> >> >>Thx, >> >>KaReL (aka Steven) >> >>Main Webpage : _http://www.karels0ft.be/ >>_ (http://www.karels0ft.be/) ICQ # : 35217584 >> >> >> >>If synchronus=FULL is set. every insert without a transaction is >>flushed to disk before sqlite3_finalized is returned. You are safe. >> >>Wei > > > Unless you are using IDE disks that respond that they've written to the > disk when in fact they have not. ..or you have some transaction-based/journaling filesystem which delays data writing to disk ;-) > reid > > -- Konstantin Malakhanov <[EMAIL PROTECTED]> KaReL (aka Steven) Main Webpage : http://www.karels0ft.be/ ICQ # : 35217584 pgpBcc6OJc9WT.pgp Description: PGP signature
Re: [sqlite] Flush to disc
On 11/03/2004 08:48 PM, Reid Thompson wrote: > [EMAIL PROTECTED] wrote: > >>In a message dated 11/3/2004 12:20:36 PM Eastern Standard Time, >>[EMAIL PROTECTED] writes: >> >>How can I make sure after an insert that everything has been flushed >>to disc? >> >>I mean, I want even my program to become slower as long as I know >> everything have been written to disc. >> >>At the moment I am using: >>PRAGMA default_synchronous = FULL; >>PRAGMA synchronous = FULL; >> >> >>And the queries are all INSERT's without a transaction. >> >> >>Thx, >> >>KaReL (aka Steven) >> >>Main Webpage : _http://www.karels0ft.be/ >>_ (http://www.karels0ft.be/) ICQ #: 35217584 >> >> >> >>If synchronus=FULL is set. every insert without a transaction is >>flushed to disk before sqlite3_finalized is returned. You are safe. >> >>Wei > > > Unless you are using IDE disks that respond that they've written to the > disk when in fact they have not. ..or you have some transaction-based/journaling filesystem which delays data writing to disk ;-) > reid > > -- Konstantin Malakhanov <[EMAIL PROTECTED]>
RE: [sqlite] Flush to disc
[EMAIL PROTECTED] wrote: > In a message dated 11/3/2004 12:20:36 PM Eastern Standard Time, > [EMAIL PROTECTED] writes: > > How can I make sure after an insert that everything has been flushed > to disc? > > I mean, I want even my program to become slower as long as I know > everything have been written to disc. > > At the moment I am using: > PRAGMA default_synchronous = FULL; > PRAGMA synchronous = FULL; > > > And the queries are all INSERT's without a transaction. > > > Thx, > > KaReL (aka Steven) > > Main Webpage : _http://www.karels0ft.be/ > _ (http://www.karels0ft.be/) ICQ #: 35217584 > > > > If synchronus=FULL is set. every insert without a transaction is > flushed to disk before sqlite3_finalized is returned. You are safe. > > Wei Unless you are using IDE disks that respond that they've written to the disk when in fact they have not. reid
Re: [sqlite] Flush to disc
In a message dated 11/3/2004 12:20:36 PM Eastern Standard Time, [EMAIL PROTECTED] writes: How can I make sure after an insert that everything has been flushed to disc? I mean, I want even my program to become slower as long as I know everything have been written to disc. At the moment I am using: PRAGMA default_synchronous = FULL; PRAGMA synchronous = FULL; And the queries are all INSERT's without a transaction. Thx, KaReL (aka Steven) Main Webpage : _http://www.karels0ft.be/ _ (http://www.karels0ft.be/) ICQ #: 35217584 If synchronus=FULL is set. every insert without a transaction is flushed to disk before sqlite3_finalized is returned. You are safe. Wei
