Amazing SQLite Speed Up

dige · Post by **dige** » Fri Sep 26, 2003 8:35 am

Code updated For 5.20+

If someone works here with the brilliant database engine sqlite,
here is small hint to speed up querys like Insert & Replace...

Using transactions will accelerate these querys around factor 100!

For example 1.000 querys

Code: Select all

For a = 1 to 1000
  DatabaseQuery ( #DBIndex, "INSERT INTO Test ..." )
Next

... needs about 90 seconds!

Code: Select all

DatabaseQuery ( #DBIndex, "BEGIN TRANSACTION Test" )
For a = 1 to 1000
  DatabaseQuery ( #DBIndex, "INSERT INTO Test ..." )
Next
DatabaseQuery ( #DBIndex, "COMMIT" )

... needs less than 1 second!!! :D

cya dige

ebs · Post by **ebs** » Fri Sep 26, 2003 1:36 pm

dige,

A 100x speed-up is IMPRESSIVE!
It does makes sense, since I think using the transaction does all 1,000 INSERT statements in one disk write, instead of 1,000 disk writes.

I still have a hard time believing I can create a 300KB application, including the database engine. SQLite is pretty amazing, isn't it?

Eric