mORMot Open Source

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