c# - SQLite - 从 SQLite 数据库读取数据的最快方法？

Question

我有一个本地 SQLite 数据库

表格详情

-- Describe PREFIX_LIST
CREATE TABLE PREFIX_LIST(ITEM VARCHAR(25) PRIMARY KEY)

-- Describe SUFFIX_LIST
CREATE TABLE SUFFIX_LIST(ITEM VARCHAR(25) PRIMARY KEY)

-- Describe VALID_LIST
CREATE TABLE VALID_LIST (
    "PART1" TEXT,
    "PART2" TEXT,
    PRIMARY KEY(PART1, PART2)
)

现在这个列表真的很大，我需要从中保存数据。

这是我的实现。

SQLiteConnection con = null;
SQLiteCommand cmd = null;
Connect(DbPath, ref con, ref cmd);

cmd.CommandText =
    "SELECT PART1 || '@' || PART2 FROM VALID_LIST 
 WHERE NOT EXISTS 
   (SELECT * FROM PREFIX_LIST WHERE VALID_LIST.PART1 LIKE '%' || ITEM || '%') 
   AND NOT EXISTS
   (SELECT * FROM SUFFIX_LIST WHERE VALID_LIST.PART2 LIKE '%' || ITEM || '%')";

var reader = cmd.ExecuteReader();

if (reader.HasRows)
{
    string savePath;

    if (SaveTextFile(out savePath) == DialogResult.OK)
    {
        TextWriter writer = new StreamWriter(savePath);
        while (reader.Read())
        {
            writer.WriteLine(reader.GetString(0));
        }
        writer.Close();
        writer.Dispose();
    }

}

reader.Close();
reader.Dispose();
cmd.Dispose();
con.Close();
con.Dispose();

MessageBox.Show("List Saved!.", Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Information);

我需要一些更好的方法来更快地保存列表。VALID_LIST 中的条目总数为 2639117

上面的 SQL QUERY 保存了 15 分钟!

如果可以优化sql查询，请lmk!

提前致谢

Answer 1

使用 LIKE 的查询通常会非常慢，除非通配符附加到后缀。无法通过典型的字符串索引对 LIKE '%foo' 等谓词进行索引。

然而，您可以用其 full text search 替换 sqlite 中大量使用 LIKE (FTS) 特征。

The FTS3 and FTS4 extension modules allows users to create special tables with a built-in full-text index (hereafter "FTS tables"). The full-text index allows the user to efficiently query the database for all rows that contain one or more words (hereafter "tokens"), even if the table contains many large documents.

他们有an example在您的用例的性能方面看起来很有前途。

CREATE VIRTUAL TABLE enrondata1 USING fts3(content TEXT);     /* FTS3 table */
CREATE TABLE enrondata2(content TEXT);                        /* Ordinary table *

SELECT count(*) FROM enrondata1 WHERE content MATCH 'linux';  /* 0.03 seconds */
SELECT count(*) FROM enrondata2 WHERE content LIKE '%linux%'; /* 22.5 seconds */