高效更新SQLite表与许多记录 [英] Efficient update of SQLite table with many records

问题描述

我试图使用sqlite（sqlite3）一个项目存储成千上万的记录（希望sqlite，所以程序的用户不必运行一个[我的] sql服务器）。

I am trying to use sqlite (sqlite3) for a project to store hundreds of thousands of records (would like sqlite so users of the program don't have to run a [my]sql server).

我必须更新成千上万条记录，有时会输入左侧的值（它们是层次结构），但是发现了标准

I have to update hundreds of thousands of records sometimes to enter left right values (they are hierarchical), but have found the standard

update table set left_value = 4, right_value = 5 where id = 12340;

很慢。我已经尝试用

begin;
....
update...
update table set left_value = 4, right_value = 5 where id = 12340;
update...
....
commit;

但又一次，很慢。奇怪，因为当我填充几十万（插入），它完成在几秒钟。

but again, very slow. Odd, because when I populate it with a few hundred thousand (with inserts), it finishes in seconds.

我目前正试图测试速度在python在命令行和python），我把它移动到C ++实现，但现在这是缓慢，我需要找到一个新的解决方案，除非我做错了。想法？ （将采用可移植的开源替代SQLite）

I am currently trying to test the speed in python (the slowness is at the command line and python) before I move it to the C++ implementation, but right now this is way to slow and I need to find a new solution unless I am doing something wrong. Thoughts? (would take open source alternative to SQLite that is portable as well)

推荐答案

在表上创建索引.id

create index table_id_index on table(id)

这篇关于高效更新SQLite表与许多记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！