I'm attempting to use sqlite (sqlite3) for any project to keep 100s of 1000's of records (would really like sqlite so customers from the program do not have to operate a [my]sql server).

I must update 100s of 1000's of records sometimes to go in left right values (they're hierarchical), but have discovered the conventional

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

to be really slow. I've attempted surrounding every 1000 approximately with

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

however, very slow. Odd, because after i populate it having a couple of hundred 1000 (with card inserts), it finishes within minutes.

I'm presently attempting to test the rate in python (the slowness reaches the command line and python) before I move it towards the C++ implementation, but at this time this really is method to slow and I have to get a new solution unless of course I'm doing a problem. Ideas? (would take free option to SQLite that's portable too)

Create a catalog on table.id

create index table_id_index on table(id)

Apart from ensuring you possess an index in position, you are able to checkout the SQLite Optimization FAQ.

Using transactions can provide you with a really large speed increase while you pointed out and you will likewise try to show off journaling.

Example 1:

2.2 PRAGMA synchronous

The Boolean synchronous value controls set up library will wait for disk creates to become fully written to disk before ongoing. This setting could be not the same as the default_synchronous value loaded from the database. In typical make use of the library may spend considerable time just waiting around the file system. Setting "PRAGMA synchronous=OFF" can produce a major speed difference.

Example 2:

2.3 PRAGMA count_changes

Once the count_changes setting is ON, the callback function is invoked once for every Remove, Place, or UPDATE operation. The argument may be the number of rows which were transformed. Should you avoid using this feature, there's a small speed increase from turning this off.