sqlite3_open speed opening database
(1) By Bill Aylward (backward) on 2021-05-15 06:14:34 [link] [source]
I have a database with 12 tables, each with a maximum of 10 columns, some with up to 3000 rows, no indices, and a total file size of 1.7MB. I am writing an application in Swift for iOS/macOS.
I am opening the database using the C interface as follows:
However, this function takes 13 seconds to return on my 2018 MacBook Air. I have tried sqlite3_open_v2 with various combinations of parameters, including SQLITE_OPEN_READONLY, but have not succeeded in reducing the opening time.
var handle: OpaquePointer? let result = sqlite3_open(path, &handle)
When I open it using the command line, it opens instantly. The same when using the macOS application SQLiteFlow.
My questions are:
- Is this the expected opening time for a database of this size?
- Are the command line and SQLiteFlow opening on a background thread (which I could do too), or is there a trick that I am missing?
Thank you in advance
(2) By Richard Hipp (drh) on 2021-05-15 11:46:17 in reply to 1 [link] [source]
The sqlite3_open() call should be nearly instantaneous. The size and schema of the database should not matter since sqlite3_open() does not actually read the database file. It just verifies that the file exists and opens a file descriptor. The first read does not occur until you actually try to access the database.
My guess is that something goofy is happening inside the Swift wrapper that you are using.
(3.1) By Simon Slavin (slavin) on 2021-05-15 13:16:06 edited from 3.0 in reply to 1 [link] [source]
As another response said, the
sqlite3_open() call of the SQLIte C API doesn't look at the contents of the file. It just sets up some things in memory. The same may or may not be true of the SQLite3 library you're using. Which SQLite3 library are you using ? I don't see an 'import' line.
Please duplicate your sqlite database file (make a copy with another name), then add another line to your code to open the copy too. Does the version of your code with two
sqlite3_open() calls take twice as long as the one with one
sqlite3_open() call ?
Where possible, use
sqlite3_open_v2() even if it doesn't make things any faster.
(4) By Bill Aylward (backward) on 2021-05-16 17:33:19 in reply to 3.1 [link] [source]
Thanks for your response. I am using direct C call to the SQLite3 library bundled with macOS, and not using a Swift framework. I copied the database file as you suggested and ran the following code:
import SQLite3 let path1 = "Database1.db" let path2 = "Database2.db" var handle1: OpaquePointer? var handle2: OpaquePointer? print(Date()) let result1 = sqlite3_open(path1, &handle1) print(Date()) let result2 = sqlite3_open(path2, &handle2) print(Date())
However, this time both opened instantly and I can't reproduce the problem! I have no idea what was happening yesterday, and the issue has now gone. Sorry to have wasted your time.
(5) By Simon Slavin (slavin) on 2021-05-17 15:01:16 in reply to 4 [source]
Thanks for doing the test. We get a lot of people who gripe about something but then refuse to do diagnostic testing. Glad the problem went away. Don't hesitate to post again if you get any more problems.