与MSAccess相比，SQLite非常慢-SQLiteterriblyslowcomparedtoMSAccess

Following the advice of fellow SO-ers, I converted an MS Access database I had (a small one, for test reasons) to SQLite. It has two tables, one with 5k entries and another with 50k entries.

根据SO-ers的建议，我将一个MS Access数据库（由于测试原因，一个小数据库）转换为SQLite。它有两个表，一个有5k个条目，另一个有50k个条目。

Now, the queries I will present below QuLimma and QLexeis took about 60ms (total time of the function below) with Access, but a whopping 830ms with SQLite.

现在，我将在QuLimma和QLexeis下面的查询使用Access花费大约60ms（下面函数的总时间），但是使用SQLite需要大约830ms。

Dim i As Integer
Dim ms As Integer
ResultPin(0) = ""
ResultPin(1) = ""
ResultPin(2) = ""
ResultPin(3) = ""
ResultPin(4) = ""
i = 0
Multichoice = 0
ms = 0

Dim rsTblEntries As ADODB.Recordset
Set rsTblEntries = New ADODB.Recordset

Dim QuLimma As String, QLexeis As String
QuLimma = "SELECT Words.limma, Words.limmabody, Words.limmapro " & _
        "FROM Words " & _
        "GROUP BY Words.limma, Words.limmabody, Words.limmapro " & _
        "HAVING (((Words.limma)='" & StrLexeis & "'));"
QLexeis = "SELECT Limma.limmalexeis, Words.limma, Limma.limmabody, Words.limmapro, Limma.limmaexp " & _
        "FROM Limma INNER JOIN Words ON Limma.limmabody = Words.limmabody " & _
        "GROUP BY Limma.limmalexeis, Words.limma, Limma.limmabody, Words.limmapro, Limma.limmaexp " & _
        "HAVING (((Limma.limmalexeis)='" & StrLexeis & "'));"

rsTblEntries.Open QuLimma, CnDataParSQLite ', adOpenStatic, adLockOptimistic
If rsTblEntries.EOF = True Then
    rsTblEntries.Close
    rsTblEntries.Open QLexeis, CnDataParSQLite ', adOpenStatic, adLockOptimistic
    If rsTblEntries.EOF = True Then
        SearchQParagSQLite = False
    Else
        SearchQParagSQLite = True
        Do While rsTblEntries.EOF = False
            ms = ms + 1
            rsTblEntries.MoveNext
        Loop
        rsTblEntries.MoveFirst
        If ms > 1 Then
            Do While rsTblEntries.EOF = False
                ResultTemp(0, i) = rsTblEntries.Fields("limma").Value & "" 'rsWordPar!limma
                ResultTemp(1, i) = rsTblEntries.Fields("limmalexeis").Value & "" 'rsWordPar!limmalexeis
                ResultTemp(2, i) = rsTblEntries.Fields("limmabody").Value 'rsWordPar!limmabody
                If IsNull(rsTblEntries.Fields("limmapro").Value) = False Then ResultTemp(3, i) = rsTblEntries.Fields("limmapro").Value 'rsWordPar!limmapro
                rsTblEntries.MoveNext
                i = i + 1
                Multichoice = 1
            Loop
        Else
            Do While rsTblEntries.EOF = False
                ResultPin(0) = rsTblEntries.Fields("limma").Value & "" 'rsWordPar!limma
                ResultPin(1) = rsTblEntries.Fields("limmalexeis").Value & "" 'rsWordPar!limmalexeis
                ResultPin(2) = rsTblEntries.Fields("limmabody").Value 'rsWordPar!limmabody
                If IsNull(rsTblEntries.Fields("limmapro").Value) = False Then ResultPin(3) = rsTblEntries.Fields("limmapro").Value 'rsWordPar!limmapro
                rsTblEntries.MoveNext
                Multichoice = 0
            Loop
        End If
    End If
Else
     SearchQParagSQLite = True
     rsTblEntries.MoveFirst
     Do While rsTblEntries.EOF = False
        ResultPin(0) = rsTblEntries.Fields("limma").Value & "" 'rsWordPar!limma
        ResultPin(1) = "#"
        ResultPin(2) = rsTblEntries.Fields("limmabody").Value 'rsWordPar!limmabody
        If IsNull(rsTblEntries.Fields("limmapro").Value) = False Then ResultPin(3) = rsTblEntries.Fields("limmapro").Value 'rsWordPar!limmapro
        rsTblEntries.MoveNext
        i = i + 1
     Loop
End If
i = 0

rsTblEntries.Close
Set rsTblEntries = Nothing

With connection string:

使用连接字符串：

CnDataParSQLite.COnnectionString= "DRIVER=SQLite3 ODBC Driver;" & _
                          "Database=" & strDataPath & "u.sl3;LOngNames=0;Timeout=1000;NoTXN=0;SyncPragma=NORMAL;StepAPI=0;"
CnDataParSQLite.Open

Now, before someone asks "wasn't 60ms fast enough?", I'd like to say that I did this because I have other Access files and queries which take 3-4 seconds and would like to lower them down, so yes, I was hoping to go down from 60ms to 30 or less in this one.

现在，在有人问“不是60ms足够快？”之前，我想说我这样做是因为我有其他Access文件和查询需要3-4秒才想降低它们，所以是的，我希望在这一个中从60ms降到30或更低。

Do I have a misconfiguration or is it just that SQLite is not faster? I have checked, both return correct results, there is no weird looping issue.

我是否存在配置错误，或者仅仅是SQLite不是更快？我已经检查过，两者都返回正确的结果，没有奇怪的循环问题。

Edit: most of the time is consumed by the second query.

编辑：大部分时间由第二个查询消耗。

Edit 2: (copy/paste from the db.sql)

编辑2 :(从db.sql复制/粘贴）

Table Limma:

表Limma：

CREATE TABLE Limma ( id INTEGER PRIMARY KEY, limmabody INTEGER DEFAULT 0, limmalexeis VARCHAR2(100), limmastat VARCHAR2(50), limmaexp VARCHAR2(250));
INSERT INTO Limma VALUES (1, 1, 'υψικάμινος', 'ΣΠ', NULL);
INSERT INTO Limma VALUES (2, 1, 'υψίκορμος', 'ΣΠ', NULL);
INSERT INTO Limma VALUES (3, 1, 'υψίπεδο', 'ΑΠ', '<αρχ. υψίπεδον, ουδ. του επιθ. υψίπεδος<ύψι "ψηλά" + πέδον');

Total: 64k entries

总计：64k条目

Table Words:

表格词：

CREATE TABLE Words ( id INTEGER PRIMARY KEY, limma VARCHAR2(100), limmabody INTEGER DEFAULT 0, limmapro VARCHAR2(200));
INSERT INTO Words VALUES (1, 'υψι (αχώριστο μόριο)', 1, NULL);
INSERT INTO Words VALUES (2, 'ομο (αχώριστο μόριο)', 2, NULL);
INSERT INTO Words VALUES (3, 'διχο (αχώριστο μόριο)', 3, NULL);

Total: 6k entries

总计：6k条目

The first field "id" is unique.

第一个字段“id”是唯一的。

1 个解决方案

QuLimma = "SELECT DISTINCT Words.limma, Words.limmabody, Words.limmapro " & _
    "FROM Words " & _
    "WHERE Words.limma ='" & StrLexeis & "';"
QLexeis = "SELECT DISTINCT Limma.limmalexeis, Words.limma, Limma.limmabody, Words.limmapro, Limma.limmaexp " & _
    "FROM Limma INNER JOIN Words ON Limma.limmabody = Words.limmabody " & _
    "WHERE Limma.limmalexeis ='" & StrLexeis & "';"

CREATE NONCLUSTERED INDEX ix_words_1 ON Words (Limma) INCLUDE (Limmabody, Limmapro)
CREATE NONCLUSTERED INDEX ix_words_2 ON Words (Limmabody) INCLUDE (Limma, Limmapro)
CREATE NONCLUSTERED INDEX ix_limma_1 ON Limma (Limmabody, Limmalexeis) INCLUDE (Limmaexp)