Set ConnectionTimeout in System.Data.SQLite

c# sqlite system.data.sqlite
Brian Sweeney picture Brian Sweeney · Jul 24, 2009 · Viewed 7.5k times · Source

I think that I'm experiencing a database connection timeout with my SQLite connection, but I'm not sure how to bump up the connection timeout. The command timeout can be set with ConnectionString.DefaultTimeout but Connection.ConnectionTimout is read only. Any suggestions?

Thanks, Brian

Answer

Liron Levi picture Liron Levi · Jul 27, 2009

Exceeding the default (30 seconds) timeout for SQLite queries is a strong indication that something is wrong in your approach.

Timeouts usually occur when there are too many concurrent connections. SQLite behaves poorly when you have interleaved write/read transactions.

Make sure that you chunk related SQL statements in a single transaction (the performance gains can be x1000!).

Another tip: by default - when you start a transaction (BeginTransaction) - the default behavior is to get a write lock immediatly. Try to use BeginTransaction(deffered) version instead and specify that you want to defer acquiring a write lock until it is actually needed. This will help you if have multiple readers because now they will be able to run concurrently.

Related questions

Create SQLite Database and table

Within C# application code, I would like to create and then interact with one or more SQLite databases. How do I initialize a new SQLite database file and open it for reading and writing? Following the database's creation, how do …

Read More
What 'additional configuration' is necessary to reference a .NET 2.0 mixed mode assembly in a .NET 4.0 project?

I have a project in which I'd like to use some of the .NET 4.0 features but a core requirement is that I can use the System.Data.SQLite framework which is compiled against 2.X. I see mention of this being …

Read More
Adding parameters in SQLite with C#

Im just learning SQLite and I can't get my parameters to compile into the command properly. When I execute the following code: this.command.CommandText = "INSERT INTO [StringData] VALUE (?,?)"; this.data = new SQLiteParameter(); this.byteIndex = new SQLiteParameter(); this.command.Parameters.…

Read More