mORMot Open Source

richardmaley

Member

Registered: 2012-11-05

Posts: 4

2001 times faster inserts

I wish to report a dramatic increase in performance inserting records into Sqlite3 tables.

I have 81 tables with more than 200 million records that I was attempting to insert.

I attempted to insert 263000 records last night for 9 hours and the process was less than half done.

I was using Conn.Synchronous:=smFull and I was not using transactions.

Additionally, I was suffering a significant memory leak from Query: TSQLDBStatement.

Changing Conn.Synchronous:=smFull to Conn.Synchronous:=smOff and using conn.StartTransaction; and Conn.Commit; every 10000 records dramatically improved performance.

However, the memory leak continued until the application crashed.

The source of the memory leak was the Query: TSQLDBStatement.

I modified my code so that every use of Query looked like this:
        Query := Conn.NewStatement;
        Try
          Query.Execute(sgSQL, False);
        Finally
          FreeAndNil(Query);
        End;
The memory leaks completely disappeared.

The net result was the new code was 2001 times faster and there were no memory leaks.

Dick Maley

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,659

Website

Re: 2001 times faster inserts

This is as expected.

See http://blog.synopse.info/post/2012/07/26/ACID-and-speed

Thanks for the feedback.