Slow work of SQLInsert with SQLite

Question

I want to store my data in SQLite database, but writing large numbers of data its too slow.

For example:

db = JDBC["SQLite", "f:\\test1.db"];
conn = OpenSQLConnection[db];
SQLCreateTable[conn, 
  SQLTable["TEST"], {SQLColumn["COL1", "DataTypeName" -> "INTEGER"], 
   SQLColumn["COL2", "DataTypeName" -> "DOUBLE"]}];
SQLInsert[conn, "TEST", {"COL1", "COL2"}, 
   RandomReal[{-10, 10}, {100, 2}]]; // AbsoluteTiming
(*{11.814676, Null}*)

Inserting 100 lines of data take more than 10s on my PC, but when I use SQLite Database Browser (external software) for creating table with more than 80000 lines its take less than 1 minute.

Why inserting from Mathematica so slow?

EDIT

But HSQL much more faster 3.5s for 80000 lines

Answer 1

Firstly, a bit about SQLite insert performances:

https://stackoverflow.com/questions/1711631/how-do-i-improve-insert-per-second-performance-of-sqlite https://stackoverflow.com/questions/3852068/sqlite-insert-very-slow

Both of these state the fact that SQLite wraps every insert statement with a transaction and that the run times can be up to 270x faster simply by wrapping the inserts in one transaction.

I installed a fresh copy of SQLite, version 3.7.16.1 and ran the code molekyla777 has provided.

In[9]:= SQLInsert[conn, "TEST", {"COL1", "COL2"}, RandomReal[{-10, 10}, {100, 2}]]; // AbsoluteTiming
Out[9]= {0.371021, Null}

The result showed a correctly optimised bulk insert which lead one to believe that something must be different between the versions. Searching around, one would find SQLite Release Notes for 3.7.11 for which bulk insert is supported for the INSERT syntax.

This meant that users on SQLite prior to 3.7.11 have to explicitly state SQLBeginTransaction and SQLCommitTransaction to perform an optimised bulk insert, as SQLite does not support a single batch insert SQL syntax. Whereas users on version 3.7.11 or later have the luxury of letting Mathematica map to the new syntax and thereby not having to explicitly start and end the transaction for the bulk insert.

I suspect that SQLite Database Browser has some optimisations inbuilt and hence its performance.

Answer 2

Using SQLBeginTransaction works fast with the SQLite version 3.7.2 distributed with Mathematica 10 :

Needs["DatabaseLink`"];
testdb = FileNameJoin[{$TemporaryDirectory, "test1.db"}];
If[ FileExistsQ[testdb], DeleteFile[testdb] ];
db = JDBC["SQLite", testdb];
conn = OpenSQLConnection[db];
SQLCreateTable[conn, 
  SQLTable["TEST"], 
    {SQLColumn["COL1", "DataTypeName" -> "INTEGER"], 
     SQLColumn["COL2", "DataTypeName" -> "DOUBLE"]
     }
  ];
 AbsoluteTiming[
    SQLBeginTransaction[conn];
       SQLInsert[conn, "TEST", 
                 {"COL1", "COL2"}, 
                 RandomReal[{-10, 10}, {100, 2}]
       ];
     SQLCommitTransaction[conn];
 ]

Answer 3

Why don't you use SQLExecute?

From the databaselink guide (just a synopsis below - please work through the guide example):

table = SQLTable["BATCH"];
cols = {SQLColumn["X", "DataTypeName" -> "Integer"],    SQLColumn["Y", "DataTypeName" -> "Integer"]};
SQLCreateTable[conn, table, cols];

data1 = {table, SQLArgument @@ cols, SQLArgument[#, #^2]} & /@ 
Range[10000];

AbsoluteTiming[SQLExecute[conn, "INSERT INTO `1` (`2`) VALUES (`3`)", data1];]