Slow work of SQLInsert with SQLite

10

5

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

molekyla777

Posted 2014-08-18T13:17:24.157

Reputation: 2 728

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 – 2014-08-20T19:29:37.670

This sounds like a batching and transaction issue. Have a quick go through http://stackoverflow.com/questions/3852068/sqlite-insert-very-slow , and see if you can force a single transaction.

– Jonie – 2014-08-21T00:49:23.083

Try calling SQLBeginTransaction and SQLCommitTransaction. – Jonie – 2014-08-21T03:50:00.570

@Jonie my approach use batch insert link. Funny but SQLBeginTransaction and SQLCommitTransaction add speed for this insert

– molekyla777 – 2014-08-21T06:37:53.687

@shrx SQLite driver version is 3.7.2. Looks like this is not old version – molekyla777 – 2014-08-21T06:44:16.127

@molekyla777 I'm curious as to how much faster it was? Mathematica may say batch insert, but we do not know whether it maps to individual Insert statements or a bulk insert statement in SQLite. The link you provide says it skips making individual SQLTable/SQLArgument but that doesn't imply a bulk insert statement (which may not exist for some DBs). Are you able to tap in SQLite to see what is being executed? – Jonie – 2014-08-21T10:53:02.467

@Jonie 11s without transaction and 0.6s with (for 100 lines). Tap SQL query directly (for example in SQLite browser)? – molekyla777 – 2014-08-21T11:00:23.240

I meant some kind of management tool so you can see the history of every SQL statement executed. – Jonie – 2014-08-21T11:12:01.863

Let us continue this discussion in chat.

– molekyla777 – 2014-08-21T11:38:37.170

Answers

7

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.

Jonie

Posted 2014-08-18T13:17:24.157

Reputation: 1 169

4

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

Rolf Mertig

Posted 2014-08-18T13:17:24.157

Reputation: 16 237

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 – 2018-12-12T08:16:01.373

0

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

Gordon Coale

Posted 2014-08-18T13:17:24.157

Reputation: 2 241

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 – 2014-08-21T14:10:53.993

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 – 2014-08-22T12:11:14.587