SQLite, keep connection open or close every time?

Peter Haworth pete at lcsql.com
Tue Nov 24 12:54:43 EST 2015


Hi Klaus,
You seem to have received several responses that don't address your
parameters of a local SQLite database on desktop or mobile so I'll limit my
reply to a desktop application using SQLite on a local disk with a single
user, no network connections, no multi user situations.

With that in mind, the answer is "it depends on the application".  An
application that simply reads data in at startup, accesses it in LC
variables and hardly ever needs to re-read it or update it is a lot
different than a data entry application where the database is constantly
being read/updated.  In the former case, open and closing around every
access is probably the best way to go.  In the second case, there's no
reason to incur the extra overhead of opening and closing around every
access.

As far as tidying up in the event of disk failure and other catastrophes,
SQLIte is more than capable of dealing with that.  Preserving data
integrity is one of the points of using an ACID compliant SQL database
rather than a flat file of some sort.

However, to get that benefit, you should make sure you wrap your logical
groups of SQL statements in BEGIN and END statements.  You should do that
anyway because it can make a huge difference in performance.  By doing
that, you ensure your database is not physically updated until the
transaction has logically completed so if a hardware failure occurs, your
database will still be in the state it was before the BEGIN statement.
Even if a power failure or some similar catastrophe occurs while the
database is being physically updated, SQLIte will rollback the database to
the state it was in before the power failure occurred next time you open
the database.  There's more information about this at
https://www.sqlite.org/atomiccommit.html.

Hope that helps.

Pete



Pete
lcSQL Software <http://www.lcsql.com>
Home of lcStackBrowser <http://www.lcsql.com/lcstackbrowser.html> and
SQLiteAdmin <http://www.lcsql.com/sqliteadmin.html>

On Tue, Nov 24, 2015 at 7:25 AM, Klaus major-k <klaus at major-k.de> wrote:

> Hi friends,
>
> see subject, any PROs or CONs to keep a db connection to a local SQLite
> database
> (deskton and mobile) open during a session or better close and reopen
> every time
> you access the db?
>
> Any opinions and hints appreciated!
> Thanks in advance!
>
>
> Best
>
> Klaus
>
> --
> Klaus Major
> http://www.major-k.de
> klaus at major-k.de
>
>
> _______________________________________________
> use-livecode mailing list
> use-livecode at lists.runrev.com
> Please visit this url to subscribe, unsubscribe and manage your
> subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-livecode
>



More information about the use-livecode mailing list