System.Data.SQLite slow execution of large script

Question

I'm trying to run an SQL file from my .NET application but the run time is extremely slow. I'm not sure how to optimize it to run faster. I know that the script works because when I run it in the SQLite shell application it executes in less than 1 second. In my .NET application it just hangs on sqlite_cmd.ExecuteNonQuery().

Here's an excerpt from the script.sql file:

--
-- File generated with SQLiteStudio v3.1.1 on Thu Feb 15 11:33:12 2018
--
-- Text encoding used: System
--
PRAGMA foreign_keys = off;
BEGIN TRANSACTION;

-- Table: Document_Type
CREATE TABLE [Document_Type] (
    [Id]    integer PRIMARY KEY AUTOINCREMENT NOT NULL,
    [Type]  nvarchar(50) NOT NULL COLLATE NOCASE

);
INSERT INTO Document_Type (Id, Type) VALUES (1, '.docx');
INSERT INTO Document_Type (Id, Type) VALUES (2, '.xlsm');
INSERT INTO Document_Type (Id, Type) VALUES (3, '.jpeg');

-- Table: Learn_More
CREATE TABLE [Learn_More] (
    [item_ID]   integer PRIMARY KEY AUTOINCREMENT NOT NULL,
    [field_name]    nvarchar(100) COLLATE NOCASE,
    [description]   text(1073741823) COLLATE NOCASE,
    [location]  char(10) COLLATE NOCASE

);
INSERT INTO Learn_More (item_ID, field_name, description, location) VALUES (1, 'System Name', 'Full descriptive name of the system.*      Example: Agency Billing System ', 'SSP1      ');
.
.
.
.
.
COMMIT TRANSACTION;
PRAGMA foreign_keys = on;

The script.sql file is 35,831 lines. I plan on cleaning up the column types after I can get it to run in .NET.

My .NET code:

Dim baseDir As String = Application.UserAppDataPath()
Dim db3Path As String = baseDir + "\Schema Scripts\ProgrammaticallyGenerateDatabase\Test.db3"
Dim sqlPath As String = baseDir + "\Schema Scripts\script.sql"

SQLiteConnection.CreateFile(db3Path)

Dim sqlite_conn = New SQLiteConnection("Data Source= " & db3Path)
Using sqlite_conn
    sqlite_conn.Open()
    Dim sqlite_cmd = sqlite_conn.CreateCommand()
    'Dim sqlite_tran = sqlite_conn.BeginTransaction()

    'Using sqlite_tran

        Using sqlite_cmd
            Try
                sqlite_cmd.CommandText = File.ReadAllText(sqlPath)
                sqlite_cmd.ExecuteNonQuery()

                sqlite_cmd.CommandText = "SELECT Type FROM Document_Type"

                Dim sqlite_datareader = sqlite_cmd.ExecuteReader()
                While (sqlite_datareader.Read())
                    Dim textReader As String = sqlite_datareader.GetString(0)

                    Console.WriteLine(textReader)
                End While
            Catch ex As Exception
                'End Using
                'sqlite_tran.Commit()
            End Try
        End Using
    sqlite_conn.Close()
End Using

I've tried using File.OpenText(sqlPath).ReadToEnd() but had the same performance issue.

Any ideas on how to speed this up? Just to reiterate, the same file runs in less than 1 second in the SQLite shell program using the .read script.sql command.

EDIT:

This is a win app. The code is running on a background thread and takes 483801 milliseconds to complete.

I came up with a solution that works for my case. The query now runs in ~700-900ms. I'm just bundling the sqlite3.exe with my application and calling it with the command line.

Here's the code:

Private Sub SQLiteButtonPress()
        Dim baseDir As String = Application.UserAppDataPath() + "\Schema Scripts"
        Dim db3Path As String = baseDir + "\ProgrammaticallyGenerateDatabase\Test.db3"
        Dim sqlPath As String = baseDir + "\Baseline Schema.sql"

        Dim watch = System.Diagnostics.Stopwatch.StartNew()
        Console.WriteLine("Starting Stopwatch")

        Try
            Dim proc As New System.Diagnostics.ProcessStartInfo()
            proc.Arguments = "/c sqlite3.exe """ + db3Path + """ < """ + sqlPath + """"
            proc.FileName = "cmd.exe"
            proc.UseShellExecute = False
            proc.CreateNoWindow = True

            Dim p As New Process()
            p.StartInfo = proc
            p.Start()
            p.WaitForExit()

            Dim sqlite_conn = New SQLiteConnection("Data Source= " & db3Path)
            Using sqlite_conn
                sqlite_conn.Open()
                Dim sqlite_cmd = sqlite_conn.CreateCommand()

                Using sqlite_cmd
                        sqlite_cmd.CommandText = "SELECT Type FROM Document_Type"

                        Dim sqlite_datareader = sqlite_cmd.ExecuteReader()
                        While (sqlite_datareader.Read())
                            Dim textReader As String = sqlite_datareader.GetString(0)

                            Console.WriteLine(textReader)
                        End While
                End Using
                sqlite_conn.Close()
            End Using

        Catch ex As Exception
            Throw ex
        End Try

        watch.Stop()
        Dim elapsedMs = watch.ElapsedMilliseconds
        Console.WriteLine("Finished Stopwatch: " & elapsedMs & "ms")
    End Sub

Does it hang, or does it just take a long time (how much) to finish? — Mark Benningfield, Feb 15 '18 at 22:08
Are you sure that this is a problem of the File.ReadAllText ? I would try to turn this code async. Also in what enviroment do you run it ? (This is a mobile app, win app, server app ?) — Nick Polyderopoulos, Feb 15 '18 at 22:10
@NickPolideropoulos I took your advice and moved this code into a backgroundworker. It still takes an abnormally long time to execute. This is on a win app. I need to be able to generate the database via the code on user's machines so that later I can provide updates to it without erasing their user data. — Dillon Connolly, Feb 15 '18 at 22:33
@DillonConnolly After a bit of research i came up with those two links. a [Memory-Mapped File](https://learn.microsoft.com/en-us/dotnet/standard/io/memory-mapped-files) and a blog with different techniques for loading files [Fasted Way to Read Text Files](http://cc.davelozinski.com/c-sharp/fastest-way-to-read-text-files). Could you try to give it a spin ? Also could you measure the time it takes to load the file and the time it takes to execute the command ? It may be faster to run them in chunks. — Nick Polyderopoulos, Feb 15 '18 at 22:44
Try splitting the SQL string on the semi-colon, and providing each statement to the command text in a loop. Just a hunch. — Mark Benningfield, Feb 15 '18 at 22:47

score 0 · Answer 1 · answered Jan 18 '19 at 08:42

0

I had the same problem. Advice from @MarkBenningfield resolved the issue. As he mentioned, split your script on the semi-colon and run each statement separately in a loop. After that everything ran very fast for me.

answered Jan 18 '19 at 08:42

Darkhan Zholmukhanov

76
5

score 0 · Answer 2 · answered Mar 21 '19 at 12:24

For some reason, the .NET SQLite library doesn't run large queries in a single string very quickly, even if you wrap each bulk insert into a transaction; or wrap the entire "ExeecuteNonQuery" method call inside a transaction.

The above solution works, split your large SQL file into individual statements, execute all of them in a transaction and then commit. I was able to load 15,000 records into a memory based SQLite instance in 0.22 seconds that way.

Cheers.

System.Data.SQLite slow execution of large script

2 Answers2