SQLite terribly slow compared to MS Access

Question

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.

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.

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.

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.

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

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

Table 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

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

The first field "id" is unique.

You said you went to Sqlite, but your table definition looks like Oracle. I didn't think Sqlite had a "Varchar2" datatype. — Eric J. Price, Jan 29 '13 at 18:16
Please run "Select value from SYS.NLS_DATABASE_PARAMETERS where PARAMETER = 'NLS_CHARACTERSET'" and post the output. If your database is not set to use a unicode character set you may need to change your table definition and start over. — Eric J. Price, Jan 29 '13 at 18:20
@LoveLearn, the excerpt is from an intermediate .sql file from the conversion ( ms access -> .sql -> sqlite3 — MirrorMirror, Jan 29 '13 at 19:10
You should definitely do some research to ensure that your columns are storing your unicode values correctly. If they are then it's all good, but if they aren't you need to update your datatypes. It may be fine, but that could be somewhat disastrous if they weren't. — Eric J. Price, Jan 29 '13 at 19:14
If you were able to speed up the queries in Access, would you still be interested in switching to SQLite? This is confusing. — HansUp, Jan 29 '13 at 21:05
@HansUp No, i didn't speed up any access queries, they are as they are. — MirrorMirror, Jan 30 '13 at 07:53

Eric J. Price · Accepted Answer · 2013-01-29T20:28:23.853

6

You almost never want to use HAVING where you can use WHERE criteria. You're evaluating all possible results and then culling them down after aggregation. You mainly want to use HAVING criteria where you're trying to cull down based upon the aggregated results. You can achieve the same thing by moving the HAVING logic to a WHERE criteria before the aggregation in this case. This should greatly speed up your query.

There is also no need to use GROUP BY logic since you're not returning any aggregates, just use DISTINCT.

I would write it like this:

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 & "';"

For these two queries with your table schema these indexes should optimize the queries:

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)

Keep in mind there is a cost at the time of insert for each additional index you have. You have to weigh this cost against the benefit of the index. If your tables contain static data then there is no harm.

edited Jan 29 '13 at 20:28

answered Jan 29 '13 at 17:33

Eric J. Price

2,740
1
15
21

thanks for your reply. Maybe I'm also missing a table indexing? i just converted the database from access to sqlite, but I don't know if I should index it and HOW. – MirrorMirror Jan 29 '13 at 17:37
1

You definitely need to add indexes on your tables if you don't have any. I can't tell from your columns exactly what they're used for. Can you provide the full table definition and describe what each column is? Myself or someone should be able to provide you with a copy paste "CREATE INDEX" statement with this information. – Eric J. Price Jan 29 '13 at 17:40
i updated my initial post with the table definitions and explanations. thanks alot! – MirrorMirror Jan 29 '13 at 17:52
What is the significance of the "id" column on each table, do you ever query or filter by it? – Eric J. Price Jan 29 '13 at 19:16
it's just an autonumber, it gets queried sometimes ( not on these two particular queries ) – MirrorMirror Jan 29 '13 at 20:09
Updated my answer with the indexes to optimize these two queries. – Eric J. Price Jan 29 '13 at 20:28
thanks alot! will test them. these indexes are done once i suppose right? during the table creation – MirrorMirror Jan 29 '13 at 21:11
They don't have to be done at the time of table creation. They can be done at anytime after table creation, but yes, you only create the indexes once. Depending on your environment you might need to defrag the indexes from time to time, but that is a totally different topic with many posts written just about it. – Eric J. Price Jan 29 '13 at 21:16

SQLite terribly slow compared to MS Access

1 Answers1