Extremely slow SQLite query speed using Dapper in .NET CORE

Question

I have a bit of code which pulls in a HTML page from a local SQLite database file in .NET Core 2.0.

This code works fine when exicuted in debug mode, but runs extremely slow in production after I have published the app.

I used a Stopwatch to diagnose which piece of code is causing the problem and found that connection.QueryFirstOrDefault takes 2ms to find a single row in debug mode, however the same task takes 1.4 seconds after I have published the app. That's about 700 times slower.

//initialize connection
var connection = new SqliteConnection("Data Source=" + dbName);

// Build SQL String
string query = @"SELECT *
               FROM HtmlItems
               WHERE PostID = 1;

// Start Timer
var watch = System.Diagnostics.Stopwatch.StartNew();

Submit query
HtmlItem = connection.QueryFirstOrDefault<HtmlItem>(query);

// End Timer
watch.Stop();
var result = watch.ElapsedMilliseconds();

The query maps to an object looking like this

public class HtmlItem
{
    public int PostID { get; set; }
    public string PostTitle { get; set; }
    public string PostDescription { get; set; }
    public int PostDate { get; set; } // Unix Timestamp
    public int Hidden { get; set; }
    public string Url { get; set; }
    public string PostHTML { get; set; }
}

The same database file is used in debug and production, this database file only has three rows. My app should be the only thing trying to access the file.

Indexing the SQLite database file dosen't seem to make any speed improvement.

I'm wondering how I can identify what's causing my connection to the database to be 700x slower in production.

The network transmission could be taking a while. Try ruling that out — Joe Phillips
– Joe Phillips, Commented Apr 9, 2018 at 22:54
The small SQLIte database file sits in the same directory as the application. These are both on a local SSD which rules out the possibility of network latency.. — TidyDev
– TidyDev, Commented Apr 9, 2018 at 23:15

womoxap · Accepted Answer · 2018-04-25 22:39:40Z

1

SQLite dosen't seem to be very quick with dapper on .net core when reading the file synchronously.

If you read the file using asynchronous then it seems to be much faster.

HtmlItem = await connection.QueryFirstOrDefault<HtmlItem>(query);

answered Apr 25, 2018 at 22:39

womoxap

612 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Extremely slow SQLite query speed using Dapper in .NET CORE

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related