我有一个本地sqlite数据库
表详细信息
@H_301_4@-- 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) )现在这个列表非常庞大,我需要从中保存数据.
这是我的实施.
@H_301_4@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在您的用例性能方面看起来很有前途.
@H_301_4@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 */