SQLite - 从SQLite数据库读取数据的最快方法是什么？

Question

i have a local SQLite database

TABLE DETAILS

-- 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 查询花费了 15 分钟来保存它！

如果 SQL 查询可以进行优化，请告知我！

提前致谢。

- Parimal Raj

---

相关子查询 + LIKE = 性能大幅下降 - cdhowie

你能提供一些示例数据以及你想从查询中获得的结果吗？ - cdhowie

PREFIX和SUFFIX列表中的数据都是简单的单词。 - Parimal Raj

基本上我需要从 valid_list 中获取所有的项，其中 prefix_list.item 不是 valid_list.part1 的子串，而 suffix_list.item 不是 valid_list.part2 的子串。 - Parimal Raj

这个旧技巧使用外连接和NULL值怎么样？http://databases.about.com/library/weekly/aa051203a.htm - Larry

Answer 1

通常情况下，带有LIKE的查询速度都很慢，除非通配符附加到后缀上。例如，LIKE '%foo'这样的谓词不能通过典型的字符串索引进行索引。

但是，在sqlite中，您可以使用其全文搜索（FTS）功能来替换繁重的LIKE用法。

FTS3和FTS4扩展模块允许用户创建具有内置全文索引（以下简称“FTS表”）的特殊表。全文索引允许用户高效地查询数据库中包含一个或多个单词（以下简称“标记”）的所有行，即使表中包含许多大型文档。

在示例中，他们有一个看起来在您的用例中性能良好的例子。

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 */

- Tom Kerr

Answer 2

考虑使用全文检索。

为了使其起作用，PREFIX和SUFFIX中的值必须被标记化（它们必须是单独的单词），并且您要匹配的ITEM必须是这些值中的一个单独的标记（不是单词的一部分或两个单词在一起）。例如，PREFIX和SUFFIX中的字符串必须类似于“RED BLUE GREEN”或“DOG，CAT，CAPYBARA”，而ITEM的值必须是RED，BLUE，GREEN，DOG，CAT或CAPYBARA。

如果满足这些条件，您可以启用全文检索，将这些表重新创建为全文表，并将LIKE（以及通配符）替换为MATCH。在这种情况下，SQLite将在PREFIX或SUFFIX中找到的每个标记上维护索引，并且搜索的这部分将快得多。

不幸的是，在SQlite中启用FTS涉及从源代码编译产品，并设置一个或多个编译时标志。我没有此方面的经验。

- Larry Lustig

---

你是否将ITEM与单个单词进行匹配？如果是这样，请逐个将单词放入它们自己的行中。这样可以快速完成工作。 - Larry Lustig

抱歉我的英文不好，但我想通过SQL实现的是，确保valid_list.part1中的所有项目都不与prefix_list.item中的所有项目部分匹配。 - Parimal Raj

没问题。我建议，如果部分值都是单独的单词，您应该将它们在 “PREFIX” 和 “SUFFIX” 表中分成单独的行，然后使用 “=” 而不是 “LIKE” 进行搜索。这仅适用于值是不同单词并且您不需要在单词内查找匹配项的情况。请在所有表格中发布样本值。 - Larry Lustig

抱歉，这些词语都不是很准确。实际上，PART1 是电子邮件用户名，而 PART2 是电子邮件域名。 - Parimal Raj

所以，它们一起组成了电子邮件！如果[e-mail]是电子邮件，那么abc = valid_list.part1并且test.com = valid_list.part2。 - Parimal Raj

Answer 3

我不确定这是否是您想要的，但它将有助于加快编写过程。尝试在字符串构建器中累积从数据库读取的字符串，然后写入文件。例如，您可以一次性读取100k个字符串，然后将这些100k个字符串一次性写入文件。

    StringBuilder builder = new StringBuilder();
    int count = 0; //to limit the number of rows stored in string builder.
    while (reader.Read())
    {

        builder.AppendLine(reader.GetString(0));
        count++;

        //store every 100k or so rows at once. 
        //This number depends on how much RAM 
        //you can allocate towards storing the string of rows.
        //If you have 2GB of free RAM
        //this number can easily be 1 million but it always depends on the
        //size of each string stored in database.
        if(count == 100000) 
        {
           File.AppendAllText(path, builder.ToString()); //Append all rows to the file
           builder.Clear(); //clear the string for next 100k rows of data
           count = 0; //Clear the counter value
        }
        count++
    }

如果有帮助，请告诉我。

- Jawad Khan