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$
  • $\begingroup$ What is the ping between your computer and the remote server? $\endgroup$ – Edmund Nov 9 '15 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 '15 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 '15 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$ – Gustavo Delfino Nov 10 '15 at 3:48
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$

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.