Re: [sqlite] When to open/close connections
Simon Slavin wrote: > On 29 Apr 2014, at 2:24pm, Drago, William @ MWG - NARDAEAST > wrote: > >> Does closing the connection force, or at least encourage, the OS to write to >> disk whatever it might have been caching? > > Closing a connection calls fclose() on the database file (as long as fopen() > was called because at least one operation was done on the database). So the > operating system does everything it would normally do in response to fclose(). No, sqlite does NOT use fopen/fclose, neither on unix, nor on windows, it uses lower-level interface (open(2)/close(2) on unix). AFAIK, close(2) normally does not attempt to write/flush/sync anything (it could be more complex with unusual fs, e.g. NFS, but sqlite is incompatible with NFS anyway). (No idea about MS Windows specifics). > This means closing a connection can take a long time because it does lots of > things. For example under Windows/Mac/Unix if the file has been changed it > changes the 'last modified' date on the file. Consequently you would not > want to keep opening and closing a connection in a program that made a lot of > changes to a SQLite database. AFAIK, on unix, "last modified" (st_mtime) is updated when write(2) is called, and not delayed till close(2). (Again, no idea about MS Windows specifics). I have not instrumented sqlite (and thus could be wrong), but I'd guess only "expensive" thing in database "connection" close is memory deallocation. On other hand, database "connection" open is much more expensive, as it requires re-reading and re-parsing database schema and various other things. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] When to open/close connections
On 29 Apr 2014, at 2:24pm, Drago, William @ MWG - NARDAEAST wrote: > Does closing the connection force, or at least encourage, the OS to write to > disk whatever it might have been caching? Closing a connection calls fclose() on the database file (as long as fopen() was called because at least one operation was done on the database). So the operating system does everything it would normally do in response to fclose(). This means closing a connection can take a long time because it does lots of things. For example under Windows/Mac/Unix if the file has been changed it changes the 'last modified' date on the file. Consequently you would not want to keep opening and closing a connection in a program that made a lot of changes to a SQLite database. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] When to open/close connections
> -Original Message- > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- > boun...@sqlite.org] On Behalf Of Yuriy Kaminskiy > Sent: Monday, April 28, 2014 8:36 PM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] When to open/close connections > > Simon Slavin wrote: > > On 28 Apr 2014, at 11:11pm, RSmith wrote: > > > >> Second approach is better when you rarely access the database, also > it will make sure releases happen (or at least provide immediate errors > if not), but keeping a connection open is much better when hundreds of > accesses happen in terms of speed - especially loads of small queries, > large queries won't matter either way. > > > > I add two other reasons to Ryan's: if you are extremely short of > memory (for example in a tiny low-power device) or if your file system > lacks proper flushing because although flush() is documented in reality > it does nothing. > > s/flush/sync/. > However, I don't see just how closing/opening db will make it any > better. > Database/journal is written and synced at transaction end, not at > "connection" > close. Does closing the connection force, or at least encourage, the OS to write to disk whatever it might have been caching? CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and any attachments are solely for the use of the addressee and may contain information that is privileged or confidential. Any disclosure, use or distribution of the information contained herein is prohibited. In the event this e-mail contains technical data within the definition of the International Traffic in Arms Regulations or Export Administration Regulations, it is subject to the export control laws of the U.S.Government. The recipient should check this e-mail and any attachments for the presence of viruses as L-3 does not accept any liability associated with the transmission of this e-mail. If you have received this communication in error, please notify the sender by reply e-mail and immediately delete this message and any attachments. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] When to open/close connections
Simon Slavin wrote: > On 28 Apr 2014, at 11:11pm, RSmith wrote: > >> Second approach is better when you rarely access the database, also it will >> make sure releases happen (or at least provide immediate errors if not), but >> keeping a connection open is much better when hundreds of accesses happen in >> terms of speed - especially loads of small queries, large queries won't >> matter either way. > > I add two other reasons to Ryan's: if you are extremely short of memory (for > example in a tiny low-power device) or if your file system lacks proper > flushing because although flush() is documented in reality it does nothing. s/flush/sync/. However, I don't see just how closing/opening db will make it any better. Database/journal is written and synced at transaction end, not at "connection" close. > But as others are saying, on a desktop/laptop computer your only result will > be to slow things down. Yep. Also, if you keep connection open, you can save some time on keeping/caching prepared statements (you must finalize all statements before you close "connection") ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] When to open/close connections
On 28 Apr 2014, at 11:11pm, RSmith wrote: > Second approach is better when you rarely access the database, also it will > make sure releases happen (or at least provide immediate errors if not), but > keeping a connection open is much better when hundreds of accesses happen in > terms of speed - especially loads of small queries, large queries won't > matter either way. I add two other reasons to Ryan's: if you are extremely short of memory (for example in a tiny low-power device) or if your file system lacks proper flushing because although flush() is documented in reality it does nothing. But as others are saying, on a desktop/laptop computer your only result will be to slow things down. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] When to open/close connections
Second approach is better when you rarely access the database, also it will make sure releases happen (or at least provide immediate errors if not), but keeping a connection open is much better when hundreds of accesses happen in terms of speed - especially loads of small queries, large queries won't matter either way. Note: i say "keeping the connection open" but this may well mean as long as the queries are needed and not necessarily for the entire duration of the Program's life. A last consideration, when it is definitely good to keep open from the start, is when you intend the DB to be locked by your app - granted this is not the normal case, but it might be a requirement from time to time. So keeping the connection open in locked mode for the duration of the program will maintain the lock as well. "PRAGMA locking_mode = 'Exclusive'; -- Locked" As far as I am aware, there are no other "timely background maintenance" that sqlite does which would make it somehow more advantageous to resort to a "keep-alive" connection paradigm. Six of one, half-a-dozen of the other. Salut! Ryan On 2014/04/29 00:00, Peter Haworth wrote: There's a discussion on another forum I'm on about whether it's good practice to open an SQLite database as part of program initialization and close it when the program terminates, or whether the connection should be opened and closed around each transaction. I've always used the first approach and can't think of any benefits to the second unless you're simply reading data into memory and never accessing the database directly again. Are there any other circumstances where the second approach is better? Pete ___ 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
[sqlite] When to open/close connections
There's a discussion on another forum I'm on about whether it's good practice to open an SQLite database as part of program initialization and close it when the program terminates, or whether the connection should be opened and closed around each transaction. I've always used the first approach and can't think of any benefits to the second unless you're simply reading data into memory and never accessing the database directly again. Are there any other circumstances where the second approach is better? Pete ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
