Specify SELECT timeout for SQLITE

Question

Is it possible to specify the maximum amount of time a SELECT query may take with SQLITE?

The situation would be useful where you have big tables and the users have to possibility to enter free search terms. If the searched for terms is not quickly found then the entire table is scanned which can take a very long time as indices cannot generally be used.

So having SQLITE give up after a few seconds would be useful.

I am using SQLITE via the System.Data.Sqlite and it seemed that SqliteCommand.CommandTimeout would be what I want, but setting this seem to have no effect for some reason. Perhaps I'm missing something.

Answer 1

For a simple select query, no, there doesn't appear to be a way to set a timeout, or maximum time to execute, on SQLite itself. The only mention of timeout in the documentation is the busy timeout. So, if you need to limit the maximum amount of time a select query can take, you'll need to wrap your connection with a timeout in the application level, and cancel/close your connection if that timeout is exceeded. How to do that would obviously be application/language specific.

For a busy timeout (the timeout before the connection stops waiting for locks to clear) you can do it through the provided C interface, or through the SQLite driver provided to your application.