为什么这个SQLite查询速度慢，速度变慢？

Question

我有一个长期的数据日志服务，它产生的文件包含一天的数据每个。我正在将文件加载到Windows应用程序中的SQLite DB中。将文件中的数据插入DB的过程包括两个查询，其结果将用于随后的插入。

Using SQLconnect As New SQLite.SQLiteConnection("Data Source=" & fn & ";")
SQLconnect.Open()
Using SQLcommand As SQLite.SQLiteCommand = SQLconnect.CreateCommand
    Dim SqlTrans As System.Data.SQLite.SQLiteTransaction = SQLconnect.BeginTransaction
    For Each Path As String In paths
        fs = System.IO.File.Open(Path, IO.FileMode.Open, IO.FileAccess.Read, IO.FileShare.Read) 'Open file
        Do While ReadFromStoreFile(fs, dt, Sent) = True 'Read a Timestamp/sentence pair
            'Create a positions table for this MMSI if one doesn't already exist
            SQLcommand.CommandText = "CREATE TABLE IF NOT EXISTS MMSI" & msg.MMSI & " (PosID INTEGER PRIMARY KEY AUTOINCREMENT, Date NUMERIC, Lat REAL, Lon REAL, Status INTEGER, SOG REAL, COG INTEGER, HDG INTEGER, VoyageID INTEGER);"
            SQLcommand.ExecuteNonQuery()

            Select Case msg.Type 'Dynamic position report

                Case AIS.MsgType.PosRptClsA

                    '###THIS QUERY TAKES 20 secs per file (day) and increases 3 seconds per day!
                    SQLcommand.CommandText = "SELECT * FROM Voyages WHERE MMSI = " & msg.MMSI & " ORDER BY VoyageID DESC LIMIT 1" 'still the same
                    SQLreader = SQLcommand.ExecuteReader()
                    SQLreader.Read()
                    VID = SQLreader.Item(0)
                    SQLreader.Close()

                    SQLcommand.CommandText = "INSERT INTO MMSI" & msg.MMSI & " (Date, Lat, Lon, Status, SOG, COG, HDG, VoyageID) VALUES (" & ts & ", " & msg.Latitude & ", " & msg.Longitude & ", " & msg.NavStatus & ", " & SOG & ", " & COG & ", " & HDG & ", " & VID & ")"
                    SQLcommand.ExecuteNonQuery()
                    SQLreader.Close()

                Case AIS.MsgType.StatAndVge

                    'Find the latest entry for this MMSI in the Voyages table
                    '###THIS QUERY takes 3 secs for same number of queries and does NOT increase
                    SQLcommand.CommandText = "SELECT * FROM Voyages WHERE MMSI = " & msg.MMSI & " ORDER BY VoyageID DESC LIMIT 1"
                    SQLreader = SQLcommand.ExecuteReader()
                    SQLreader.Read()

                    Dim NoVoyage As Boolean = Not (SQLreader.HasRows)
                    If Not NoVoyage Then
                        'If the data has changed, add a new entry
                        If Not (SQLreader.Item(2) = msg.Length) Then Changed = True
                        If Not (SQLreader.Item(3) = msg.Breadth) Then Changed = True
                        If Not (SQLreader.Item(4) = msg.Draught) Then Changed = True
                        If Not (SQLreader.Item(5) = msg.Destination) Then Changed = True
                        If Not (SQLreader.Item(6) = msg.ETA.Ticks) Then Changed = True
                        VoyageID = SQLreader.Item(0)
                    End If
                    SQLreader.Close()

                    If Changed Or NoVoyage Then
                        Changed = False 'reset flag
                        SQLcommand.CommandText = "INSERT INTO Voyages (Date, Length, Breadth, Draught, Destination, ETA, MMSI) VALUES (" & ts & ", " & msg.Length & ", " & msg.Breadth & ", " & msg.Draught & ", '" & msg.Destination.Replace("'", "''") & "', " & msg.ETA.Ticks & ", " & msg.MMSI_int & ")"
                        SQLcommand.ExecuteNonQuery()

                        SQLcommand.CommandText = "SELECT last_insert_rowid() FROM Voyages"
                        SQLreader = SQLcommand.ExecuteReader()
                        SQLreader.Read()
                        VoyageID = SQLreader.Item(0)
                        SQLreader.Close()

                    End If
            End Select 'message type
        Loop 'Read next entry from file
        fs.Close() 'Close the file

        'Write this file into the files table, so we know it has been written to the DB
        fileinf = New System.IO.FileInfo(Path)
        SQLcommand.CommandText = "INSERT OR REPLACE INTO  Files (Name, Size, Date) VALUES ('" & fileinf.Name & "', '" & fileinf.Length & "', '" & fileinf.LastWriteTimeUtc.Ticks & "')"
        SQLcommand.ExecuteNonQuery()

    Next 'The next path in the list of paths to decode

    SqlTrans.Commit() 'End of all files reached, commit all the changes to the DB

End Using 'SQLcommand
End Using 'SQLconnect

如代码所示，第一个查询需要很长的时间，而且(更重要的是)随着数据加载到DB中，持续时间越来越长。当添加到DB中的21天数据时，仅此查询就需要每天20秒左右的累积时间，并且每天增加大约3秒。真正奇怪的是，第二个查询(在我看来是相同的)是快速的(对于相同数量的查询，大约3秒左右)，并且随着更多数据的添加，没有增加。

下面是创建空数据库的函数：

Public Function CreateDB(fn As String, Force As Boolean) As Boolean

    If System.IO.File.Exists(fn) Then
        If Force Then
            System.IO.File.Delete(fn) 'Delete the old DB and create a new one
        Else
            Return True 'DB alrewady exists so just return true
        End If
    End If


    Using SQLconnect As New SQLite.SQLiteConnection
        SQLconnect.ConnectionString = "Data Source=" & fn & ";"
        SQLconnect.Open()

        'Create Tables
        Using SQLcommand As SQLite.SQLiteCommand = SQLconnect.CreateCommand

            'Set page size
            SQLcommand.CommandText = "PRAGMA Page_size = 4096;"
            SQLcommand.ExecuteNonQuery()

            'Set journalling mode to off
            SQLcommand.CommandText = "PRAGMA journal_mode = OFF;"
            SQLcommand.ExecuteNonQuery()

            'Set auto indexing off
            SQLcommand.CommandText = "PRAGMA automatic_index = false;"
            SQLcommand.ExecuteNonQuery()

            'Create Vessels Table
            SQLcommand.CommandText = "CREATE TABLE Vessels(MMSI TEXT PRIMARY KEY, Name TEXT, Type INTEGER, IMO TEXT, CallSign TEXT, MothershipMMSI INTEGER, LastVoyageID INTEGER);"
            SQLcommand.ExecuteNonQuery()

            'Create Voyages Table
            SQLcommand.CommandText = "CREATE TABLE Voyages(VoyageID INTEGER PRIMARY KEY AUTOINCREMENT, Date NUMERIC, Length INTEGER, Breadth INTEGER, Draught INTEGER, Destination TEXT, ETA NUMERIC, MMSI INTEGER);"
            SQLcommand.ExecuteNonQuery()

            'Create Meta Table
            SQLcommand.CommandText = "CREATE TABLE Files(Name TEXT PRIMARY KEY, Size NUMERIC, Date NUMERIC);"
            SQLcommand.ExecuteNonQuery()

        End Using 'SQLcommand

    End Using ' SQLconnect 

    Return True

End Function

与第二个查询相比，是什么原因导致第一个查询如此缓慢，并在向DB中添加更多数据时花费更长的时间？

SQlite和System.Data.Sqlite是最新版本。

Answer 1

假设msg被ReadFromStoreFile更改，则查询

"SELECT * FROM Voyages WHERE MMSI = " & msg.MMSI & " ORDER BY VoyageID DESC LIMIT 1"

如果对给定的MMSI有更多的航程，速度会变慢。因此，我假设具有AIS.MsgType.PosRptClsA的MMSI值比其他MMSI插入得更频繁。

该查询似乎正在获取MMSI的最大航次id。你可以用更直接的

"SELECT max(VoyageID) FROM Voyages WHERE MMSI = " & msg.MMSI

我不知道这会不会跑得更快。或者，您可以保留一个MMSI和max航海id的字典，并在执行insert insert时更新它以消除查询。