Effectively update a SQLite table with many records

Question

I am trying to use sqlite (sqlite3) for a project to store hundreds of thousands of records (I would like sqlite so that users of the program do not have to start [my] SQL server).

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

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

will be very slow. I tried to surround every thousand or so with

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

but again, very slowly. Odd, because when I fill it with several hundred thousand (with inserts), it ends in seconds.

I am currently trying to check the speed in python (slowness is on the command line and python) before moving it to a C ++ implementation, but now this is a way to slow down and I need to find a new solution if I do something wrong . Thoughts? (will use the open source alternative for SQLite, which is also portable)

Answer 1

Create an index on table.id

 create index table_id_index on table(id) 

Answer 2

In addition, to make sure you have an index in place, you can check the SQLite Optimization FAQ .

Using transactions can give you a very large increase in speed, as you mentioned, and you can also try disabling logging.

Example 1:

2.2 PRAGMA synchronous

Controls of Boolean synchronous values ​​whether the library will wait for a disc to be written is completely written to the disc before continuing. This parameter may differ from the default_synchronous value loaded from the database. In typical use, a library can spend a lot of time just waiting in the file system. setting "PRAGMA synchronous = OFF" can make a big difference in speed.

Example 2:

2.3 PRAGMA count_changes

When the count_changes parameter is enabled, the callback function is called once for each DELETE, INSERT, or UPDATE operation. The argument is the number of rows that have been changed. if you do not use this function, there is a slight increase in speed from turning off.