Slow work of SQLInsert with SQLite

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

• What version is the bundled SQLite library on windows? On OS X I found out it is an old version that does not work well for what I need it, so I manually replaced it with the latest that I got from Homebrew. – shrx Aug 20 '14 at 19:29
• This sounds like a batching and transaction issue. Have a quick go through stackoverflow.com/questions/3852068/sqlite-insert-very-slow , and see if you can force a single transaction. – Jonie Aug 21 '14 at 0:49
• Try calling SQLBeginTransaction and SQLCommitTransaction. – Jonie Aug 21 '14 at 3:50
• @Jonie my approach use batch insert link. Funny but SQLBeginTransaction and SQLCommitTransaction add speed for this insert – molekyla777 Aug 21 '14 at 6:37
• @shrx SQLite driver version is 3.7.2. Looks like this is not old version – molekyla777 Aug 21 '14 at 6:44

Firstly, a bit about SQLite insert performances:

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.

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];
]

• Simple hack - improved the speed a lot - inserted 1.5 million entries in about 20 seconds (whereas the attempt without commit was inserting approx 1,000 entries per second) - this is a MySQL Db though on LAMP! – my account_ram Dec 12 '18 at 8:16

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];]

• you test that? On my pc that insert for 100 lines take 3s. It's very slow too. And I use MMA syntax without SQLExecute` becouse I dont know SQL syntax well – molekyla777 Aug 21 '14 at 14:10
• No I haven't tested it as a its a direct quote from the Database Link user guide. The hyperlink dropped off my original post - so I have added it back in. Its worth noting that if you are into the territory of BEGIN/END transaction as mentioned above - you are probably at the stage where you need to start picking up SQL syntax. Although it seems in this case you were hit by a quirk of software versions. – Gordon Coale Aug 22 '14 at 12:11