Slow work of SQLInsert with MS SQL Server in V10

Question

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.

Murta · Accepted Answer · 2015-02-08 20:41:34Z

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.

Stack Exchange Network

Slow work of SQLInsert with MS SQL Server in V10

UPDATE 1

UPDATE 2

1 Answer 1

UPDATE

Your Answer

Linked

Hot Network Questions

Slow work of SQLInsert with MS SQL Server in V10

UPDATE 1

UPDATE 2

1 Answer 1

UPDATE

Your Answer

Sign up or log in

Post as a guest

Linked

Related

Hot Network Questions