我有一个本地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
并且花了15分钟为上面的sql QUERY保存它！
请lmk如果可以优化SQL查询！
提前致谢
解决方法
除非将通配符附加到后缀,否则LIKE的查询通常会非常慢. LIKE’％foo’之类的谓词无法通过典型的字符串索引编制索引.
但是,您可以使用其full text search(FTS)功能替换sqlite中的重LIKE用法.
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 */
