0

Please note this is slightly different than my last question. Previously I had await in the ExecuteNonQueryAsync() as a last minute change before I posted the question this is the original state of the program. I would still expect that there should be no waiting when the async() function is called to prevent any computer hangs. I am using c# sqlite version 3. My application requires saving up to 20 columns of data at 4000Hz. To accomplish this I am using threads that take commands from a queue and periodically write/read to the database. Currently, I am just working on the writing portion and have found that ExecuteNonQueryAsync() and ExecuteNonQuery() have relatively the same execution time which makes no sense to me. My function is as follows: 

        public void dumpStorage(string cmdStr)
        {
            using (SQLiteCommand cmd = new SQLiteCommand(sqliteConnection))
            {
                    string times = "";
                    Stopwatch tTime = new Stopwatch();
                    tTime.Start();
                cmd.CommandText = cmdStr;
                    times += "tTime1 " + tTime.Elapsed.Seconds + ":" + tTime.Elapsed.Milliseconds + "\n";
                    tTime.Restart();
                cmd.ExecuteNonQueryAsync();
                //cmd.ExecuteNonQuery();
                //cmd.ExecuteScalar();
                //cmd.ExecuteScalarAsync();
                    times += "tTime2 " + tTime.Elapsed.Seconds + ":" + tTime.Elapsed.Milliseconds + "\n";
                    tTime.Restart();
                transaction.Commit();
                    times += "tTime2 " + tTime.Elapsed.Seconds + ":" + tTime.Elapsed.Milliseconds + "\n";
                    tTime.Restart();
                transaction = sqliteConnection.BeginTransaction();
                    tTime.Stop();
                    times += "tTime3 " + tTime.Elapsed.Seconds + ":" + tTime.Elapsed.Milliseconds + "\n";
                    timeStr = times;
                storeToDBWatchDog.Restart();
            }
        }

at the moment I am saving 40,000 rows with 20 (plus a primary key) columns which represents 10 seconds of data. I am unable to get the async to have any time advantage over the non-async function. in the future I will need to introduce a scheme that will manage if there was any failures to insert.

I would expect that ExecuteNonQueryAsync() should make tTime2 essentially 0 and it would work in the background however that is not what I am seeing as it appear to wait for the ...Async() function to complete.

the execute non query async and non-async require roughly 2.1 seconds. The end goal is to save the data at least 20 times faster than it is coming in. The only time that this becomes very important is on initial load. By this I mean i am reading an existing file (not in a database) and then saving it into the database. so, at the moment I only have approximately 4 times the performance. by this i mean it would take 5 minutes to save a file that consists of data associated with 20 minutes. this seems unexceptionably slow.

typical time output regardless of which function call is used (both scalar functions take ~50% longer):

enter image description here

Edit: I recently came across this unfortunate post, which essentially states that async doesn't do anything so you should use the WAL setting: https://learn.microsoft.com/en-us/dotnet/standard/data/sqlite/async

i have since changed my code to include the WAL journal-mode:

            connectionString = "Data Source=" + filename + "; Version=3" + "; PRAGMA journal_mode=WAL;";
            sqliteConnection = new SQLiteConnection(connectionString);

I did not see any performance increases setting the journal_mode to WAL.

JCoder
  • 75
  • 5
  • Sorry but I do not see how this is different than your initial question. Please update this question and also point out in the comments how this question is different from the last. Then I, or someone else from the community, will reopen it. As it stands now I feel it is almost identical. – Igor Apr 14 '20 at 18:56
  • Also as pointed out in your last question you can call it this way (an async call that is not awaited from a method with the return type void) *but* you will never know the state of the call and if/when it completes. – Igor Apr 14 '20 at 18:59
  • If you want to handle multiple threads that push data to your data store you should look into using TPL or some type of concurrent scheduler that handles your inserts in batches. – Igor Apr 14 '20 at 19:00

0 Answers0