7
$\begingroup$

I need to insert values to a remote MySQL server. However it is extremely slow compared to a local MySQL server.

This is my timing for the local MySQL server

Needs["DatabaseLink`"]

conn = OpenSQLConnection[
   JDBC["MySQL(Connector/J)", 
    "localhost/schema"], "Username" -> "root", 
   "Password" -> "pass"];

list = Table[{SQLDateTime[DateList@{2015, 1, 1, 1, 1, i}], i}, {i, 1, 
    100}];

SQLInsert[conn, "albedometer", {"dateTime", "volts"},
   list]; // AbsoluteTiming

{0.045335, Null}

This is my timing for the remote MySQL server

conn1 = OpenSQLConnection[
   JDBC["MySQL(Connector/J)", 
    "remoteServer/schema"], 
   "Username" -> "server", "Password" -> "server"];


list = Table[{SQLDateTime[DateList@{2015, 1, 1, 1, 1, i}], i}, {i, 1, 
    100}];

SQLInsert[conn1, "albedometer", {"dateTime", "volts"},
   list]; // AbsoluteTiming

{117.437, Null}

As you can see the difference is huge 0.04 vs 117 seconds. It is just plain impossible to work with such an small insert speed.

How could I speed this up?

$\endgroup$
4
  • $\begingroup$ What is the ping between your computer and the remote server? $\endgroup$
    – Edmund
    Nov 9, 2015 at 2:40
  • 2
    $\begingroup$ @Edmund, it is about 300 ms. But I dont think that's the issue since the same procedure with python + sqlalchemy does the insert in under a second $\endgroup$
    – elbOlita
    Nov 9, 2015 at 3:28
  • $\begingroup$ Try using a stored procedure for the update. I only upsert through sprocs to control access (SQL Server). I don't have any issues with repeated calls wrapped in a transaction on the connection. Perhaps the direct table bit has some issues. Or try wrapping the updates in a transaction on the connection. That might help. reference.wolfram.com/language/DatabaseLink/tutorial/… $\endgroup$
    – Edmund
    Nov 9, 2015 at 11:30
  • $\begingroup$ I am pretty sure that by playing with SQLInsert's "BatchSize" option, you can get better performance. reference.wolfram.com/language/DatabaseLink/ref/SQLInsert.html $\endgroup$ Nov 10, 2015 at 3:48

1 Answer 1

5
$\begingroup$

So finally I decided to create a BatchInsert[] functions that is 100 times faster than SQLInsert[]. This is the code:

BatchInsert[con_, tableName_, columns_, data_, max_:100]:=
    Module[{
      dataSplit, str, res
    },


      dataSplit = Partition[data, UpTo[max]];
      SQLBeginTransaction[con];
      res = InsertSingle[#, con, columns, tableName] & /@ dataSplit;
      res = Total@res;
      If[res === Length@data,
        SQLCommitTransaction[con];
        True
        ,
        SQLRollbackTransaction[con];
        $Failed
      ]

    ];

InsertSingle[data_, con_, cols_, tableName_] :=
    Module[{ll, str},
      ll = StringJoin@
          Riffle[Table[{"(",
            Sequence @@ Riffle[Table["?", Length@cols], ","],
            ")\n"},
            Length@data], ","];

      str =
          StringTemplate["INSERT INTO `tab` (`cols`) VALUES `ll`"]@
              Association@{
                "cols" -> StringJoin[Riffle[cols, ","]],
                "ll" -> ll,
                "tab" -> tableName

              };

      SQLExecute[con,
    str,
    Flatten@data,
    "JavaBatching" -> False
  ]
];

You can compare both functions with:

Map[AbsoluteTiming[#[con,tb,col,data];]&,{BatchInsert,SQLInsert}]
$\endgroup$
2

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.