'How to' : Improve multiple INSERT query

[Olivier-JOURDAN]

INSERT execution speed can be decisive in your application. Let me show you the different solutions to do multiple INSERT with SQLiteVIEW.

For the demonstration we'll use a simple database containing 1 table. The table contains 3 columns: [Id] --> autoincremented integer primary key, [Temperature] --> float and [Sensor] --> string.

We are going to benchmark the following INSERT query repeated 50,000 times :

INSERT INTO MyTable (Sensor, Temperature) VALUES ("Thermocouple", "42");

3 different methods

Simple Execute

As you can see below, this method is really easy to implement.

But it's also incredibly slow: more than 5min to execute 50,000 INSERT !

Simple Execute With Transaction

In this case we surround Execute with Begin transaction and Commit transaction.

INSERT performance are dramatically improved: ~ 1.6s to execute 50,000 INSERT !

Note that if an error occurred during the Execute, do a Rollback transaction allow you to retrieve the database as it was before Begin transaction.

Transaction And Statement

Here we have the fastest method.

We create a statement with the following query:

INSERT INTO MyTable (Sensor, Temperature) VALUES (?, ?);

• "?" replaces value in query
• "?" are replaced by actual values during the Execute statement

INSERT performance are improved again: ~ 1s to execute 50,000 INSERT !

Important notes

• Benchmark absolute time results are given for indication.
• You can do your own test using VI in attachment (SQLiteVIEW evaluation toolkit and LabVIEW 2010 or later required).