Edit 3:
I assume that my problem is resolved at the moment ... I changed both the service and testing applications to work as a SYSTEM account instead of a NetworkService account. It remains unclear whether the benefits of changing the user account will continue or be temporary.
The original question:
I noticed that my small 224kB SQLite DB opens very slowly in my C # application, taking from a small amount of ms to 1.5 seconds or more. Below is my code, with all the additional debuggers that I added this afternoon. I narrowed it down to a call to cnn.Open(); as shown in the logs:
2014-03-27 15:05:39,864 DEBUG - Creating SQLiteConnection... 2014-03-27 15:05:39,927 DEBUG - SQLiteConnection Created! 2014-03-27 15:05:39,927 DEBUG - SQLiteConnection Opening... 2014-03-27 15:05:41,627 DEBUG - SQLiteConnection Opened! 2014-03-27 15:05:41,627 DEBUG - SQLiteCommand Creating... 2014-03-27 15:05:41,627 DEBUG - SQLiteCommand Created! 2014-03-27 15:05:41,627 DEBUG - SQLiteCommand executing reader... 2014-03-27 15:05:41,658 DEBUG - SQLiteCommand executed reader! 2014-03-27 15:05:41,658 DEBUG - DataTable Loading... 2014-03-27 15:05:41,767 DEBUG - DataTable Loaded!
As you can see, in this case, 1.7 SECONDS was required to open the connection. I tried to repeat this and cannot predict whether subsequent connections will open almost immediately or delay as follows.
I have considered using some form of connection pool, but is it worth using for a single-threaded, single-threaded application? Right now I'm creating an instance of the SQLiteDatabase class and calling the function below for each of my queries.
public DataTable GetDataTable(string sql) { DataTable dt = new DataTable(); try { Logging.LogDebug("Creating SQLiteConnection..."); using (SQLiteConnection cnn = new SQLiteConnection(dbConnection)) { Logging.LogDebug("SQLiteConnection Created!"); Logging.LogDebug("SQLiteConnection Opening..."); cnn.Open(); Logging.LogDebug("SQLiteConnection Opened!"); Logging.LogDebug("SQLiteCommand Creating..."); using (SQLiteCommand mycommand = new SQLiteCommand(cnn)) { Logging.LogDebug("SQLiteCommand Created!"); mycommand.CommandText = sql; Logging.LogDebug("SQLiteCommand executing reader..."); using (SQLiteDataReader reader = mycommand.ExecuteReader()) { Logging.LogDebug("SQLiteCommand executed reader!"); Logging.LogDebug("DataTable Loading..."); dt.Load(reader); Logging.LogDebug("DataTable Loaded!"); reader.Close(); } } cnn.Close(); } } catch (Exception e) { throw new Exception(e.Message); } return dt; }
Edit:
Of course, dbConnection is the connection string specified by the following function. inputFile is just the file path to open the file.
public SqLiteDatabase(String inputFile) { dbConnection = String.Format("Data Source={0}", inputFile); }
And at the moment, I think sql does not matter, since it does not do this to this point when cnn.Open () stops.
Edit 2:
Ok, I did some more tests. Performing tests locally, it completes a cycle of 1000 iterations in ~ 5 seconds, about 5 ms per call to cnn.Open() . Running the test from the same Windows installer that I did on my local PC completes in ~ 25 minutes, an average of 1468 ms per call to cnn.Open() .
I made a small test program that only calls the TestOpenConn() function from a service program (the same exact code that runs in the Windows service) and works with a copy of the file located in the test directory. Doing this on the server or on my local PC results in acceptable performance (1.95 ms per call on the server, 4 ms per call on my local PC):
namespace EGC_Timing_Test { class Program { static void Main(string[] args) { Logging.Init("log4net.xml", "test.log"); var db = new SqLiteDatabase("config.sqlite"); db.TestOpenConn(); } } }
Here's the test function:
public void TestOpenConn() { // TODO: Remove this after testing loop of opening / closing SQLite DB repeatedly: const int iterations = 1000; Logging.LogDebug(String.Format("Running TestOpenConn for {0} opens...", iterations)); var startTime = DateTime.Now; for (var i = 0; i < iterations; i++) { using (SQLiteConnection cnn = new SQLiteConnection(dbConnection)) { Logging.LogDebug(String.Format("SQLiteConnection Opening, iteration {0} of {1}...", i, iterations)); var startTimeInner = DateTime.Now; cnn.Open(); var endTimeInner = DateTime.Now; var diffTimeInner = endTimeInner - startTimeInner; Logging.LogDebug(String.Format("SQLiteConnection Opened in {0}ms!", diffTimeInner.TotalMilliseconds)); cnn.Close(); } } var endTime = DateTime.Now; var diffTime = endTime - startTime; Logging.LogDebug(String.Format("Done running TestOpenConn for {0} opens!", iterations)); Logging.LogInfo(String.Format("{0} iterations total:\t{1}", iterations, diffTime)); Logging.LogInfo(String.Format("{0} iterations average:\t{1}ms", iterations, diffTime.TotalMilliseconds/iterations)); }