在Windows Phone 8非常糟糕表现的SQLite [英] Very bad SQLite performance on Windows Phone 8
问题描述
我有一个使用SQLite数据库一款Windows Phone 8应用。要访问数据库，我安迪威格利（的来源）
I have a Windows Phone 8 Application that uses a SQLite database. To access the database, I use the WinRT Wrapper by Andy Wigley (source)
我的数据库非常简单，只有一个表：
位置：整数主键字段索引，VARCHAR字段1，VARCHAR字段2，VARCHAR字段3，VARCHAR字段4，VARCHAR字段5，VARCHAR字段6，诠释类别ID
My database is very simple, only one table: Locations: integer primary key "FieldIndex", varchar "Field1", varchar "Field2", varchar "Field3", varchar "Field4", varchar "Field5", varchar "Field6", int "CategoryID"
我也有两个字段索引和类别ID一个指数。
I also have a index on both "FieldIndex" and "CategoryID".
有在总4000项表与数据库在大小900 KB。我也压缩数据库（真空）。该数据库将部署到手机应用程序内容（=在安装文件夹=只读）。我只用于查询的数据库。我的数据访问代码看起来是这样的：使用（VAR DB =新SQLiteWinRTPhone.Database（Package.Current.InstalledLocation
There are in total 4000 entries in the table with the database being 900 kB in size. I have also compacted the database (vacuum). The database is deployed to the phone as application content (= in the installation folder = read-only). I only use the database for Queries. My data access code looks like this:
using (var db = new SQLiteWinRTPhone.Database(Package.Current.InstalledLocation, @"Model\db.sqlite"))
{
await db.OpenAsync(SQLiteWinRTPhone.SqliteOpenMode.OpenRead);
await db.ExecuteStatementAsync("PRAGMA journal_mode = MEMORY");
await db.ExecuteStatementAsync("PRAGMA temp_store = 2;");
using (var stmt = await db.PrepareStatementAsync("SELECT * FROM Locations;"))
{
int i = 0;
while (await stmt.StepAsync())
{
// There is nothing happening here
// Just for testing. In my real code, I iterate on all rows and store them in a object. I wanted isolate the issue here.
i++;
}
}
MessageBox.Show("We read " + i.toString() + " records.");
}
目前，上面的语句需要20秒即可完成。从我的观点来看，这是不可接受的。我试图分析我的应用程序和hotpath是本机代码libaries（主要是ConcRT有关）。该WinRT的包装被编译为释放，我不明白，为什么表现如此糟糕。
At the moment, the above statement takes 20 seconds to complete. From my standpoint, this is unacceptable. I tried to profile my application and the hotpath is in the native code libaries (mostly ConcRT related). The WinRT wrapper is compiled as "release" and I don't understand why the performance is so bad.
目标：我想读的所有行和他们在一个对象存储结合我的看法，搜索，等等等等。
GOAL: I want to read all rows and store them in a object for binding to my view, searching, etc. and so on.
任何想法我可以做些什么来让我的数据库查询某种程度上可以接受的（小于5秒）？
Any ideas what I could do to make my database queries somewhat acceptable (< 5 seconds)?
推荐答案
有关可怕的性能主要故障是WinRT的包装由微软，它在默认情况下表现不佳。始终使用以下方式来选择和在循环中插入：
The main fault for the horrible performance is the WinRT wrapper by Microsoft, which has bad performance by default. Always use the following way to select and insert in loops:
await stmt.StepAsync().AsTask().ConfigureAwait(false))
这篇关于在Windows Phone 8非常糟糕表现的SQLite的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
