Hello Nono, Microsoft's SQL Server uses FILE_FLAG_WRITE_THROUGH in it's "CreateFiles". Read a neat article about it while looking up what exactly this flag does.
"All versions of SQL Server open the log and data files using the Win32 CreateFile function. The dwFlagsAndAttributes member includes the FILE_FLAG_WRITE_THROUGH option when opened by SQL Server. FILE_FLAG_WRITE_THROUGH Instructs the system to write through any intermediate cache and go directly to disk. The system can still cache write operations, but cannot lazily flush them. The FILE_FLAG_WRITE_THROUGH option ensures that when a write operation returns successful completion the data is correctly stored in stable storage. This aligns with the WAL protocol ensuring the data." C NB> --- Robert Simpson <[EMAIL PROTECTED]> wrote: >> > -----Original Message----- >> > From: Nono BEZMan [mailto:[EMAIL PROTECTED] >> > >> > Why not integrate a patch like this one >> > (http://www.sqlite.org/cvstrac/tktview?tn=1250) in >> the >> > next cvs. It would awesome for us windows users to >> be >> > able to chose between FILE_FLAG_WRITE_THROUGH and >> > FlushFileBuffers, with a PRAGMA for instance! >> > >> > I did make the changes in the pacth using 3.3.2 as >> > base (I had to change a few function names + >> change >> > the 'id->' in to 'f.' + did not like '@@ -169,6 >> +192,7 >> > @@ (..) + id->writeThru = 0;' so I did apply that >> > change). >> > >> > Anyways, I went with this solution after >> experiencing >> > extremely slow performance doing multiple simple >> > insterts. I know about the Synchronous=OFF (which >> > works wonders as far as performance), as well as >> using >> > transactions (which also works great, except that >> I >> > have multiple processes using the same database >> and >> > inserting/updating stuff atomically). >> > >> > Here's the pseudo-code that I used: >> > >> > - 'CREATE TABLE Test (TestID INTEGER PRIMARY KEY, >> > TestValue INTEGER);' >> > - Do N x 'INSERT INTO Test (TestValue) VALUES >> (1)'; >> >> The critical thing missing from the above pseudo >> code is the transaction >> being started before the loop and committed >> afterwards. Inside a >> transaction, you can easily get 100,000 inserts/sec >> in SQLite with >> Synchronous=Normal >> >> Robert >> >> >> NB> Hi Robert, NB> Thanx for your answer, but I know about transactions NB> and I cannot use them in my case... Quote from my NB> original post: NB> "I know about the Synchronous=OFF (which NB> works wonders as far as performance), as well as using NB> transactions (which also works great, except that I NB> have multiple processes using the same database and NB> inserting/updating stuff atomically)." NB> I do not intend to "bulk" insert rows, since I will NB> have one process inserting NB> rows, and another updating/deleting the same rows. NB> They will work concurently, NB> so I do not have the luxury of being able to 'begin a NB> transaction/queue inserts NB> for x secs/end transaction' here. NB> My point was that there is light for windows users, if NB> this NB> FILE_FLAG_WRITE_THROUGH flag is added to the code. NB> Otherwise, it seems that NB> FlushFileBuffers really hinders SLQite to a point it NB> becomes unusable. '7 NB> Inserts per seconds' on a brand new AMD64 3500+ with NB> SATA drives is extremely NB> low, and this could be fixed by using this option, NB> IMO. NB> Thanx. NB> __________________________________________________ NB> Do You Yahoo!? NB> Tired of spam? Yahoo! Mail has the best spam protection around NB> http://mail.yahoo.com -- Best regards, Teg mailto:[EMAIL PROTECTED]
