Sqlite 中的慢速删除 [英] Slow DELETE in Sqlite

问题描述

我需要从另一个数据库更新一个表.我附加它并删除和插入.插入速度很快，但删除需要很长时间.对于 20 万条记录，大约需要 1 分钟.如何更快?

I need to update one table from another db. I attach it and do delete and insert. Insert goes fast, but delete took very large time. For 200k of records it took about 1 minute. How to do it faster?

db.execSQL("attach database '" + db_path + "' as db2");
db.beginTransaction();
try {
    db.execSQL("DELETE FROM CategoryProduct_MM WHERE PrdID IN (SELECT PrdID FROM db2.CategoryProduct_MM)");
    db.execSQL("INSERT INTO CategoryProduct_MM SELECT * FROM db2.CategoryProduct_MM");
    db.setTransactionSuccessful();
} finally {
    db.endTransaction();
}

sqlitebrowser 做同样的操作 4 秒

sqlitebrowser do the same operation for 4 secs

更新:

这是我的数据库结构

CREATE TABLE CategoryProduct_MM(CategoryID INTEGER, PrdID INTEGER)
CREATE UNIQUE INDEX CategoryProduct_MM_CatID_PrdID_idx ON CategoryProduct_MM(CategoryID,PrdID);
CREATE INDEX CategoryProduct_MM_CatID_idx ON CategoryProduct_MM(CategoryID);
CREATE INDEX CategoryProduct_MM_PrdID_idx ON CategoryProduct_MM(PrdID);

推荐答案

在删除前删除索引并在插入后重新创建索引解决了这个问题.

Solved it with dropping indexes before delete and recreating them after insert.

    db.execSQL("DROP INDEX IF EXISTS CategoryProduct_MM_CatID_PrdID_idx");
    db.execSQL("DROP INDEX IF EXISTS CategoryProduct_MM_CatID_idx");
    db.execSQL("DROP INDEX IF EXISTS CategoryProduct_MM_PrdID_idx");
    db.execSQL("DELETE FROM CategoryProduct_MM WHERE PrdID IN (SELECT PrdID FROM superup.CategoryProduct_MM)");
    db.execSQL("INSERT OR REPLACE INTO CategoryProduct_MM SELECT * FROM superup.CategoryProduct_MM");
    db.execSQL("CREATE UNIQUE INDEX CategoryProduct_MM_CatID_PrdID_idx ON CategoryProduct_MM(CategoryID,PrdID)");
    db.execSQL("CREATE INDEX CategoryProduct_MM_CatID_idx ON CategoryProduct_MM(CategoryID)");
    db.execSQL("CREATE INDEX CategoryProduct_MM_PrdID_idx ON CategoryProduct_MM(PrdID)");

对于 20 万条记录，需要 7 秒.

For 200K records it took 7 sec.

这篇关于Sqlite 中的慢速删除的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！