c# - 优化 sqlite 插入查询

Question

我正在尝试创建一个数据库(在 C# 中使用 SQLite)，其中包含一个用于存储句子的表、一个用于存储这些句子中使用的每个单词的表，以及一个将单词与句子相关联的联结表他们出现在。然后我试图用超过 1500 万个句子填充数据库。我估计我的代码中发生了大约 1.5 亿次插入。现在，我的代码每秒只能处理几百个句子，这将需要很长时间才能完成这个庞大的数据集。我怎样才能让它更快？

我尝试将整个事情放在一个事务中，但由于数据量巨大，我不确定这是否可行。所以我为每个句子使用一个事务。

表格:

CREATE TABLE sentences ( sid INTEGER NOT NULL PRIMARY KEY, sentence TEXT ); 
CREATE TABLE words ( wid INTEGER NOT NULL PRIMARY KEY, dictform TEXT UNIQUE); 
CREATE TABLE sentence_words( sid INTEGER NOT NULL, wid INTEGER NOT NULL, CONSTRAINT PK_sentence_words PRIMARY KEY ( sid, wid ), FOREIGN KEY(sid) REFERENCES Sentences(sid), FOREIGN KEY(wid) REFERENCES Words(wid));

代码:

while ((input = sr.ReadLine()) != null) //read in a new sentence
        {
            tr = m_dbConnection.BeginTransaction();
            sql = "INSERT INTO sentences (sentence) VALUES(@sentence)";
            cmd = new SQLiteCommand(sql, m_dbConnection);
            cmd.Parameters.AddWithValue("@sentence", input);
            cmd.ExecuteNonQuery();

            dict_words = jutils.onlyDict(input); //convert all words to their 'dictionary form'
            var words = dict_words.Split(' ');
            foreach (var wd in words) //for each word
            {
                sql = "INSERT or IGNORE INTO words (dictform) VALUES(@dictform)";
                cmd = new SQLiteCommand(sql, m_dbConnection);
                cmd.Parameters.AddWithValue("@dictform", wd);
                cmd.ExecuteNonQuery();

                sql = "INSERT or IGNORE INTO sentence_words (sid, wid) VALUES((SELECT sid FROM sentences WHERE sentence = @sentence), (SELECT wid FROM words WHERE dictform = @dictform))";
                cmd = new SQLiteCommand(sql, m_dbConnection);
                cmd.Parameters.AddWithValue("@sentence", input);
                cmd.Parameters.AddWithValue("@dictform", wd);
                cmd.ExecuteNonQuery();
            }
            tr.Commit();
        }

Answer 1

在处理如此大的数据时，我们必须始终避免“一个接一个”的 SQL 任务。

在我的例子中，(如果内存没有负担)，将数据加载到数据表中并根据需要进行操作(使用 LINQ)，最后使用 SqlBulkCopy。

还有 SqlBulkUpdate，但由支持 SQL 2008 的私有(private)作者创建。如果在 2008 下，我们仍然可以快速执行此操作，但必须创建临时 SQL 表并使用 UPDATE Join 命令。

SqlBulkCopy 真的快到几秒钟。