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


But HSQL much more faster 3.5s for 80000 lines

  • $\begingroup$ 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. $\endgroup$
    – shrx
    Commented Aug 20, 2014 at 19:29
  • $\begingroup$ 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. $\endgroup$
    – Jonie
    Commented Aug 21, 2014 at 0:49
  • $\begingroup$ Try calling SQLBeginTransaction and SQLCommitTransaction. $\endgroup$
    – Jonie
    Commented Aug 21, 2014 at 3:50
  • $\begingroup$ @Jonie my approach use batch insert link. Funny but SQLBeginTransaction and SQLCommitTransaction add speed for this insert $\endgroup$ Commented Aug 21, 2014 at 6:37
  • $\begingroup$ @shrx SQLite driver version is 3.7.2. Looks like this is not old version $\endgroup$ Commented Aug 21, 2014 at 6:44

3 Answers 3


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 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 :

testdb = FileNameJoin[{$TemporaryDirectory, "test1.db"}];
If[ FileExistsQ[testdb], DeleteFile[testdb] ];
db = JDBC["SQLite", testdb];
conn = OpenSQLConnection[db];
    {SQLColumn["COL1", "DataTypeName" -> "INTEGER"], 
     SQLColumn["COL2", "DataTypeName" -> "DOUBLE"]
       SQLInsert[conn, "TEST", 
                 {"COL1", "COL2"}, 
                 RandomReal[{-10, 10}, {100, 2}]
  • $\begingroup$ 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! $\endgroup$ Commented Dec 12, 2018 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]} & /@ 

AbsoluteTiming[SQLExecute[conn, "INSERT INTO `1` (`2`) VALUES (`3`)", data1];]
  • $\begingroup$ 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 $\endgroup$ Commented Aug 21, 2014 at 14:10
  • $\begingroup$ 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. $\endgroup$ Commented Aug 22, 2014 at 12:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.