High cpu usage of System.Data.SQLite.SQLiteConnection..ctor
(1) By baislsl on 2021-06-28 04:11:33 [link] [source]
Hi, I am using System.Data.SQLite, version 188.8.131.52, dotnet framework project. My code creates the SQLiteConnection instance for each query and dispose it after the usage. But I found high cpu usage in the SQLiteConnection creation. As first I suspect it is my connection pool setting issues so I extend the connection pool size to 256 but found not help. I dump the CPU usage as below. The package name is mocked as 'MyPackage'.
|Function Name||Total CPU [unit, %]||Self CPU [unit, %]||Module||Category|
||||||||||||| + MyPackage.Local.SQLiteDataAccess.SQLiteAdapter.ExecuteScalarActionQuery(string, System.Action<System.Data.IDataReader>, System.Collections.Generic.Dictionary<string, string>)||617 (4.47 %)||1 (0.01 %)||MyPackageInternalProvider.dll||IO | File System | Kernel|
|||||||||||||| + System.Data.SQLite.SQLiteConnection..ctor(string, bool)||251 (1.82 %)||0 (0.00 %)||System.Data.SQLite.dll||File System | Kernel|
||||||||||||||| + System.Data.SQLite.UnsafeNativeMethods.Initialize()||231 (1.67 %)||0 (0.00 %)||System.Data.SQLite.dll||File System | Kernel|
|||||||||||||||| - System.Data.SQLite.UnsafeNativeMethods.GetSettingValue(string, string)||133 (0.96 %)||0 (0.00 %)||System.Data.SQLite.dll||Kernel|
|||||||||||||||| - System.Data.SQLite.UnsafeNativeMethods.SearchForDirectory(ref string, ref string, ref bool)||92 (0.67 %)||0 (0.00 %)||System.Data.SQLite.dll||File System | Kernel|
|||||||||||||||| - System.Data.SQLite.UnsafeNativeMethods.PreLoadSQLiteDll(string, string, bool, ref string, ref int)||6 (0.04 %)||0 (0.00 %)||System.Data.SQLite.dll||Kernel|
||||||||||||||| - System.Data.SQLite.SQLiteLog.Initialize(string)||20 (0.14 %)||0 (0.00 %)||System.Data.SQLite.dll||Kernel|
|||||||||||||| - MyPackage.Local.SqlDbLogger<T>.SelectRecord.AnonymousMethod__0(System.Data.IDataReader)||240 (1.74 %)||10 (0.07 %)||MyPackageInternalProvider.dll||IO | Kernel|
|||||||||||||| - System.Data.SQLite.SQLiteDataReader.Read()||45 (0.33 %)||2 (0.01 %)||System.Data.SQLite.dll||File System | Kernel|
|||||||||||||| - System.Data.SQLite.SQLiteCommand.ExecuteReader(System.Data.CommandBehavior)||42 (0.30 %)||0 (0.00 %)||System.Data.SQLite.dll||File System | Kernel|
|||||||||||||| - System.Data.SQLite.SQLiteConnection.Open()||28 (0.20 %)||0 (0.00 %)||System.Data.SQLite.dll||Kernel|
|||||||||||||| - MyPackage.Local.SQLiteDataAccess.SQLiteAdapter.ExecuteScalarActionQuery(string, System.Action<System.Data.IDataReader>, System.Collections.Generic.Dictionary<string, string>)||7 (0.05 %)||0 (0.00 %)||MyPackageInternalProvider.dll||Kernel|
The System.Data.SQLite.SQLiteConnection..ctor takes 1.82% cpu usage, compared with the 4.47% cpu usage of the total query execution. This one of the method cpu dump. In fact, the total cpu usage of the System.Data.SQLite.SQLiteConnection..ctor is over 20% in whole process. I don't think this is normal and I wonder why System.Data.SQLite.UnsafeNativeMethods.Initialize() take some many cpu? I also doubt that it is my error usage of connection. Can someone help me?
(2) By Larry Brasfield (larrybr) on 2021-06-28 11:14:55 in reply to 1 [source]
Here is an analogous question: When I commute to work, I do so by wallowing on the ground like an inch-worm from my house to the train stop, then taking the train. Why does the first leg take so much of the trip time?
My code creates the SQLiteConnection instance for each query and dispose it after the usage.
There is a lot going on during that call, taking time for I/O and CPU cycles. Most usages of SQLite involve a longer-lasting connection, where the cost of its creation can be amortized over many queries.
(3) By Bill Wade (billwade) on 2021-06-28 12:04:01 in reply to 2 [link] [source]
Larry's reply is likely applicable for many databases, but "even more so" for SQLite.
The on-disk format of the SQLite schema is mostly just text (a master table holds the text of each table's schema). At startup, SQLite parses that text to build its in-memory representation of the schema. Reading and parsing that text takes time.
Many other databases store a parsed representation of the schema. That may save some time at startup. If an instance of one of those database processes doesn't access some tables, that instance may never have to even get those particular tables' schemas into memory.
If you are using a server-based database, you may be connecting to an already-running instance of the server. If "connect to server" is substantially less expensive than "connect to database", that would provide additional savings.
The SQLite choice probably makes "Lite" easier to achieve. Clearly SQLite has to know how to read SQL. Reusing that knowledge at startup may mean writing less code specific to startup.
There is also a good chance that the text-based schema makes backwards compatibility easier. Any structure that never goes to disk can be changed, without having to worry about maintaining code specifically to read the old version.
I believe I've seen Dr. Hipp make similar statements, but I was unable to find those statements with a quick search.