CODEWITHSUNDEEP
c#sqlitesystem.data.sqlite
MaYaN
MaYaN

Reputation: 6996

How can I improve the performance of SELECT query in SQLite?

I have a large SQLite DB file ~ 3 GB. In it there are 5 tables (Table1-5) with the following definition:

CREATE TABLE IF NOT EXISTS [TableOne] (
    [EntryDate_Epoch_Utc] INTEGER DEFAULT (strftime('%s','now')),
    [Key] TEXT PRIMARY KEY,
    [Count] INTEGER NOT NULL
)

I have the below SELECT query that I need to run every 1 min:

using (Db)
{
    using (Db.OpenTransaction())
    {
        foreach (var table in _tables)
        {
            var query = StringExtensions.FormatWith("SELECT Key, Count FROM {0} ORDER BY Count DESC LIMIT 100", table);

            var result = Db.Select<Result>(query);
            // do something with the result
        }
    }
}

At the start of my program when the size of the DB is small the query runs quite fast ~ 400 ms however as the DB file gets larger (towards the end of the day) it takes around 30 seconds to run the above query.

Is there any way to improve the query? below is the connection string I use when opening the SQLite DB file:

var conStr = new SQLiteConnectionStringBuilder
{
    DataSource = dbFilePath,
    FailIfMissing = false,
    Pooling = true,
    DateTimeKind = DateTimeKind.Utc,
    DateTimeFormat = SQLiteDateFormats.UnixEpoch,
    JournalMode = SQLiteJournalModeEnum.Memory,
    SyncMode = SynchronizationModes.Off,
    UseUTF16Encoding = true,
    PageSize = 4096,
    CacheSize = 5000,
    Version = 3
}.ToString();

Upvotes: 0

Views: 1220

Answers (1)

CL.
CL.

Reputation: 180070

To find the 100 rows with the largest Count values, the database has to go through all rows of the table.

When you create an index on this column:

CREATE INDEX whatever ON TableOne(Count);

the database can just take the last 100 entries from the index.

Upvotes: 3

Related Questions