Hi, Simon, Yes, I agree that a journal file in memory will lead some risk when I set synchronous as off. However the speed is a much critical requirement for me.
I have tried to close and reopen the file. However the time to open a file and prepare the first query need more time. Maybe I need spend more time to study the database design to enhance the performance. Thanks! BR Rick -----Original Message----- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Simon Slavin Sent: Monday, August 09, 2010 3:07 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] synchronous issue On 9 Aug 2010, at 7:13am, Lei, Rick (GE EntSol, SensInsp) wrote: > In order to reduce the operation time, I set synchronous as OFF and > journal_mode as MEMORY by PRAGMA command. However I found that the data > may be lost after inserting a BLOB data, even when I use a commit > action. Yes, transactions (BEGIN ... COMMIT) are a level above disk access level. Doing a COMMIT does not guarantee that your disk file is completely up-to-date and consistent. > I think the reason is synchronous is off. How can I know that > there is some data which are not written to the disk? The answer depends on whether you consider having data in the journal file rather than the database as being 'written to disk'. However, the lack of documentation to say otherwise suggests that with the 'PRAGMA synchronous' setting you've chosen you can never be sure of this. The fact that you are keeping your journal file in memory makes this even more serious. > And how can I > force sqlite to write these data on disk? Close the database and reopen it. There is an alternative which I would want to check the source code before committing myself about: switch 'PRAGMA synchronous' to FULL, then immediately back to OFF again. It may be that the tricks you're using for speed are not the best way to speed up your operations. You might want to take a look at how you're using transactions, and how many indexes you really need. Or even how your tables are organised. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
