Re: [sqlite] When to open/close connections

[Yuriy Kaminskiy]

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

[Simon Slavin]

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

[Drago, William @ MWG - NARDAEAST]

> -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 <rsm...@rsweb.co.za> 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

[Yuriy Kaminskiy]

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

[Simon Slavin]

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

[RSmith]

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

[Peter Haworth]

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