SQLite.NET性能：使用SQLiteParameter和LIKE操作符

Question

我遇到了一个使用SQLiteParameters和LIKE运算符的SQLite查询问题。这里是一段代码片段，如果我提供的代码不够，请见谅。如有需要，我可以提供更多的代码。

性能差：

using (OdysseyDataContext entities = new OdysseyDataContext())
{
    var results = entities.SearchResults.SqlQuery(
        "SELECT * FROM SearchResults WHERE ContactName LIKE @ContactName",
        new SQLiteParameter("@ContactName", "test")
    );
}

优秀的性能：

using (OdysseyDataContext entities = new OdysseyDataContext())
{
    var results = entities.SearchResults.SqlQuery(
        string.Format(
            "SELECT * FROM SearchResults WHERE ContactName LIKE '{0}'",
            "test"
        )
    );
}

其他重要的代码：

public class OdysseyDataContext : DbContext
{
    public DbSet<SearchResult> SearchResults { get; set; }
}

public class SearchResult
{
    [Key]
    public Guid Id { get; set; }
    public string ContactName { get; set; }
}

第一个示例需要执行700毫秒，我的主管认为这是不可接受的。第二个示例只需要执行7毫秒。为什么会有这样的差异？我做错了什么导致我成为新手吗？

提前感谢！

- Terry

---

你是否正在运行多个测试来消除缓存伪像？它们都返回相同的行吗？你是否在使用通配符？ - Tim

嗨，Tim。是的，我正在运行多个测试。第一次大约需要1秒钟，15次测试的平均时间约为700毫秒。是的，它们返回相同的行。不，我没有使用任何通配符。我知道这似乎很奇怪，但我正在使用like语句来删除区分大小写的搜索，以便使用我的索引设置在ContactName COLLATE NOCASE上。 - Terry

我们能看到SqlQuery方法的源代码吗？你确定你正在计时的是查询本身，还是包括创建参数并将其添加到集合的开销？（这几乎没有开销，但在查看方法的源代码之前我们无法确定） - vcsjones

@vcsjones SqlQuery方法实际上由Entity Framework 4.1在任何DbSet<>类型上提供。非常抱歉没有包括entities项的构建。我将修改帖子以包含所有必要的部分。 - Terry

你有性能监视器可以测量测试所需的时间吗？我认为代码本身应该有一些东西，因为生成的查询应该是相同的-但谁知道...如果性能监视器显示您在数据库操作期间确实失去了时间，那么您就知道这不是您的代码问题，而是当其在数据库上执行时需要关注查询本身。 - Gambrinus

Answer 1

因此，我认为问题可能出在System.Data.SQLite上。我在C++中尝试了以下代码：

#include "sqlite3.h"
#include <stdio.h>

void xProfile(void* pArg, const char* query, sqlite3_uint64 pTimeTaken)
{
    printf("%s\n", query);
    printf("%I64d ms\n", pTimeTaken / 1000000);
}

void PoorPerformance();
void GoodPerformance();

int main()
{
    printf("Poor Performance:\n");
    PoorPerformance();

    printf("Good Performance:\n");
    GoodPerformance();

    return 0;
}

void PoorPerformance()
{
    int rc;
    int rowCount = 0;

    sqlite3 *db;
    if (sqlite3_open("<<File Here>>", &db))
    {
        printf("Could not open the database.");
        return;
    }

    sqlite3_profile(db, &xProfile, NULL);

    sqlite3_stmt *statement;
    if (!sqlite3_prepare_v2(db, "SELECT * FROM SearchResults WHERE ContactName LIKE @ContactName;", -1, &statement, 0))
    {
        int result = 0;
        int parameterIndex = sqlite3_bind_parameter_index(statement, "@ContactName");
        sqlite3_bind_text(statement, 1, "test", -1, NULL);
        while (result != SQLITE_DONE)
        {
            result = sqlite3_step(statement);

            if (result == SQLITE_ROW)
            {
                rowCount++;
            }
        }

        sqlite3_finalize(statement);
    }

    printf("%d rows\n", rowCount);

    sqlite3_close(db);
}

void GoodPerformance()
{
    int rc;
    int rowCount = 0;

    sqlite3 *db;
    if (sqlite3_open("<<File Here>>", &db))
    {
        printf("Could not open the database.");
        return;
    }

    sqlite3_profile(db, &xProfile, NULL);

    sqlite3_stmt *statement;
    if (!sqlite3_prepare_v2(db, "SELECT * FROM SearchResults WHERE ContactName LIKE 'test';", -1, &statement, 0))
    {
        int result = 0;

        while (result != SQLITE_DONE)
        {
            result = sqlite3_step(statement);

            if (result == SQLITE_ROW)
            {
                rowCount++;
            }
        }

        sqlite3_finalize(statement);
    }

    printf("%d rows\n", rowCount);

    sqlite3_close(db);
}

无论是PoorPerformance函数还是GoodPerformance函数，都得出了11行1毫秒的结果。我所做的和System.Data.SQLite应该做的有什么不同吗？希望这只是需要报告给System.Data.SQLite的一个bug，并可能采用自己的修复。

- Terry

---

你对 System.Data.SQLite 的问题有任何更新吗？ - Shrike

@Shrike 请看下面我的回复，可能会有用。 - user1032657

Answer 2

由于我看不出这两个查询之间的区别，但事实上一个使用了SqliteParameter，而另一个则使用了完整的SQL语句字符串 - 我只是在Google搜索中发现了 这个链接。

在那里指出，在SQLiteCommand对象中有一个名为 ParameterCheck的属性，它可能会导致一些性能损失。

您可以尝试重新编写代码，传递一个SQLiteCommand对象并将ParameterCheck属性设置为false。我认为这样做可以提高一些速度。

至少值得一试 :)

- Gambrinus

---

我非常希望这能解决我的问题！不幸的是，它使用的是Devart.Data.SQLite而不是System.Data.SQLite。 - Terry

Answer 3

我也遇到了System.Data.SQLite的性能问题，其中一些我已经能够解决和改进，而另一些则没有。

然而，最近我发现了这个替代的C# SQLite库：http://code.google.com/p/csharp-sqlite/

它没有给我带来任何性能问题，实际上我在一个现有项目中用这个库替换了System.Data.SQLite（语法几乎没有变化 - 我只是简单地替换了DLL和using指令..只有几行需要强制类型转换），并且它使事情加速得惊人。以前使用System.Data.SQLite时，有时候我等待几秒钟，现在执行是瞬间完成的。

- user1032657