CODEWITHSUNDEEP
vb.netsqlite
user1500403
user1500403

Reputation: 569

SQLite, open one permanent connection or not?

I have been under the understanding that database connections are best used and closed. However with SQLite Im not sure that this applies. I do all the queries with a Using Connection statment. So it is my understanding that I open a connection and then close it doing this. When it comes to SQLite and optimal usage, is it better to open one permament connection for the duration of the program being in use or do I continue to use the method that I currently use.

I am using the database for a VB.net windows program with a fairly large DB of about 2gig.

My current method of connection example

  Using oMainQueryR As New SQLite.SQLiteCommand
            oMainQueryR.CommandText = ("SELECT * FROM CRD")
            Using connection As New SQLite.SQLiteConnection(conectionString)
                Using oDataSQL As New SQLite.SQLiteDataAdapter
                    oMainQueryR.Connection = connection
                    oDataSQL.SelectCommand = oMainQueryR
                    connection.Open()
                    oDataSQL.FillSchema(crd, SchemaType.Source)
                    oDataSQL.Fill(crd)
                    connection.Close()
                End Using
            End Using
    End Using

Upvotes: 5

Views: 2125

Answers (3)

Joel Coehoorn
Joel Coehoorn

Reputation: 415735

As with all things database, it depends. In this specific case of sqlite, there are two "depends" you need to look at:

  1. Are you the only user of the database?
  2. When are implicit transactions committed?

For the first item, you probably want to open/close different connections frequently if there are other users of the database or if it's all possible that more than process will be hitting your sqlite database file at the same time.

For the second item, I'm not sure how sqlite specifically behaves. Some database engines don't commit implicit transactions until the connection is closed. If this is the case for sqlite, you probably want to be closing your connection a little more often.

The idea that connections should be short-lived in .Net applies mainly to Microsoft Sql Server, because the .Net provider for Sql Server is also able to take advantage of a feature known as connection pooling. Outside of Sql Server this advice is not entirely without merit, but it's not as much of a given.

Upvotes: 1

Colton
Colton

Reputation: 645

I think with most databases the "Best used and closed" idea comes from the perspective of saving memory by ensuring you only have the minimum number of connections need open.

In reality opening the connection can be a large amount of of overhead and should be done when needed. This is why managed server infrastructure (weblogic etc.) promotes the use of connection pooling. In this way you have N connections that are utilizable at any given time. You never "waste" resources but you also aren't left with the responsibility of managing them at a global level.

Upvotes: 1

mac10688
mac10688

Reputation: 2205

If it is a local application being used by only one user I think it is fine to keep one connection opened for the life of the application.

Upvotes: 1

Related Questions