Sqlite 删除速度非常慢 - 如何加快速度? [英] Sqlite delete incredibly slow - How to speed up?

问题描述

我读到删除操作很慢，我想知道如何改进此检查.

I read that delete operation is slow and I was wondering how can I improve this check.

我有一个每天有 10-15k 行的表，每次启动时我都需要清理所有超过 6 个月的记录，但是当数据库增长时，我开始遇到速度问题.当我运行此命令时接近 100 万条记录 - 即使我没有要删除的内容 - 软件会挂起几分钟......这是不可接受的:

I have a table which is filled by 10-15k rows every day and every startup I need to clean all records older than 6 months but when the database grows, I start to have speed problem. Close to 1 MILION of records when I run this command - EVEN if I have NOTHING to delete - the software hang for minutes....which is not acceptable:

Using cnn as New SqliteConnection(dbConnection)
    cnn.Open()
    dim cmd as New SQLiteCommand(cnn)
    cmd.CommandText = "DELETE FROM tablename WHERE timecolumn < datetime('now', '-6 months')"
    rowsUpdated = cmd.ExecuteNonQuery
End Using

即使没有删除任何记录，这也会导致几分钟的挂起.

This results in a few minutes of hang even if no records are deleted.

我怎样才能做得更快、更快?

How can I do it quicker, much quicker?

我正在为 WinCe 6 开发 .NET 紧凑型框架 3.5

I'm working on .NET compact framework 3.5 for WinCe 6

谢谢

推荐答案

这里有很多需要调整的地方 SQLite 优化.我会尝试通过更改PRAGMA cache_size"来将整个表名"放入内存中.当您删除它时，它必须大幅重新平衡 btree.如果它可以在执行时全部保存在内存中，速度会快很多.

There are lots of things to tweak here SQLite Optimization. I would try to get the whole of your "tablename" into memory by changing the "PRAGMA cache_size". When you delete it has to rebalance the btree substantially. If it can all be in memory while it does it it will be a lot faster.

我还会在timecolumn"字段上添加一个索引.没有它，删除将扫描表中的每条记录.当实际删除符合条件的记录时，它会增加完成的工作，但总体上仍然应该快很多.

I would also add an Index on the 'timecolumn' field. Without it the delete is scanning through every record in the table. It will add to the work done when actually deleting the records which match the condition but it should still be a lot faster overall.

如果可能，请移至支持分区(如 postgreSQL)的数据库，并使用时间列"将数据拆分为多个时间段.这使得数据库将您的表划分为许多子表，并真正加快了在时间列"之间搜索的内务处理和查询...

If possible move to a database that supports partions (like postgreSQL) and split your data into time periods using the 'timecolumn'. This makes the DB divide your table into lots of sub tables and really speeds up housekeeping and queries that search between "timecolumn"s...

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