在.NET CORE中使用Dapper极慢的SQLite查询速度(Extremely slow SQLite query speed using Dapper in .NET CORE)

我有一些代码从.NET Core 2.0中的本地SQLite数据库文件中提取HTML页面。

此代码在调试模式下工作良好，但在发布应用程序后运行速度非常慢。

我使用秒表来诊断哪一段代码导致了问题，并发现connection.QueryFirstOrDefault需要2ms才能在调试模式下查找单个行，但是同样的任务在发布应用程序后需要1.4秒。这大约要慢700倍。

//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();

查询映射到一个像这样的对象

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; } }

在调试和生产中使用相同的数据库文件，该数据库文件只有三行。我的应用程序应该是唯一试图访问该文件的东西。

索引SQLite数据库文件看起来似乎没有提高速度。

我想知道如何确定是什么原因导致我与数据库的连接速度降低了700倍。

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.