11
$\begingroup$

This question is very similar to: Slow work of SQLInsert with SQLite, but for MS SQL Server. Unfortunately the use of SQLBeginTransaction and SQLCommitTransaction do not improved the performance as in SQLite.

First let's create a connection function and some test data:

  myConn[] := 
  OpenSQLConnection[
     JDBC["Microsoft SQL Server(jTDS)", "myIP"]
    ,"Username" -> "User"
    ,"Password" -> "pass"
  ];

data=RandomInteger[1000,{10000,5}];
heads={"col1","col2","col3","col4","col5"}

Now, here is the slow version, using SQLInsert function.

insertSlow[tableName_String,heads_List,data_List]:=Module[{conn=myConn[],colunas,r=data},

    SQLExecute[conn,"TRUNCATE TABLE "<>tableName];
    SQLBeginTransaction[conn];
    SQLInsert[conn,tableName,heads,data];
    SQLCommitTransaction[conn];
    CloseSQLConnection[conn];
]

insertSlow[tableName,heads,data]//AbsoluteTiming

180.7140(seconds) Which is unbelievable slow.

Now, I create this function to accelerate the insert statement:

insertString[tableName_String,heads_List,data_List]:=Module[{insertSql,col,values},

    col=StringReplace[ToString[heads,InputForm],{"{"-> "(","}"-> ")","\""-> ""}];
    values=StringReplace[ToString[data,InputForm],{"{"-> "(","}"-> ")","\""-> "'"}];
    values=StringTake[values,{2,-2}];
    insertSql="Insert into "<>tableName<>" "<>col<>" values "<>values;
    insertSql
];  
(*insertString["MY_TABLE",heads,data]*)

sqlBatchInsert[conn_,tableName_String,heads_,data_List]:=Module[{sql,len,i=0},
    sql=insertString[tableName,heads,#]&/@Partition[data,1000,1000,1,{}];
    len=Length@sql;
    (i++;Print["Block:",i,"/",len];SQLExecute[conn,#])&/@sql;
]

insertFast[tableName_String,heads_List,data_List]:=Module[{conn=myConn[]},

    SQLExecute[conn,"TRUNCATE TABLE "<>tableName];
    SQLBeginTransaction[conn];
    sqlBatchInsert[conn,tableName,heads,data]
    SQLCommitTransaction[conn];
    CloseSQLConnection[conn];
]

insertFast["MY_TABLE",heads,data]//AbsoluteTiming
Block:1/10
Block:2/10
Block:3/10
Block:4/10
Block:5/10
Block:6/10
Block:7/10
Block:8/10
Block:9/10
Block:10/10
`5.2312`(*seconds*)

Much faster, but still slow when compared to SQLite where I got 10.000 in 1.56 second in my tests. I don't like the way my batch insert function works either, to clumsy, and limited.

Question: How batch insert can be done in a more clean/direct/fast way, in MS SQL Server?

UPDATE 1

Mathematica uses jTDS driver to handler SQL Server. Here is the official jTDS page. It's not clear to me how batch works in it. In FAQ area, there is a list of parameters, I played with batchSize, but without success.

I tried to change driver from jTDS to Microsoft JDBC SQLServerDriver. The Driver worked nice, but the performance is the same.

UPDATE 2

This problem is new in V10, with the new SQL.m file. In V9 the insert statement is automatic in batch mode.

$\endgroup$
11
$\begingroup$

After some useless hours in Google trying to understand jTDS, I discovered that DatabaseLink implementation is a open Package, and that SQLInsert implementation is in the file DatabaseLink/Kernel/SQL.m.

Inside It you can check that SQLInsert implementation is just a special application of SQLExecute. And that SQLExecute has two new parameters in V10 called "JavaBatching" and "BatchSize". When "JavaBatching" is set to False (the default is True, and isn't working), the batch operation is implemented inside Mathematica, using "BatchSize" as partition (that is 1000 by default). So, you can rewrite the a new insertSuperFast as:

insertSuperFast[tableName_String,heads_List,data_List]:=Module[{conn=myConn[],colunas,r=data},

    SQLExecute[conn,"TRUNCATE TABLE "<>tableName];
    SQLBeginTransaction[conn];
    SQLInsert[conn,tableName,heads,data,"JavaBatching"->False,"BatchSize"-> 10000];
    SQLCommitTransaction[conn];
    CloseSQLConnection[conn];

]

insertSuperFast["MY_TABLE",heads,data]//AbsoluteTiming

1.420 (Seconds!)

with no clumsy string manipulation, and same performance as SQLite.

It's also interesting to note that in this case, the use of SQLBeginTransaction and SQLCommitTransaction make an improvement in performance.

UPDATE

This slowness didn't happens in V9. The SQL.m and SQLStatementProcessor.java file has been changed in V10. Now we have better batch size control inside MMA, but the native Java batch is broke in some way.

$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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