Message Boards Message Boards

A way to speed up SQLExecute query execution time?

Posted 3 years ago

I have a bit of MySQL code that I am running within Mathematica (currently at version 12.0). The database I'm querying is remote, my copy of Mathematica and MySQL Workbench are on my personal machine. The query returns about 2.6 million rows, and to handle that I've adjusted the Java Heap size to be 4GB.

When I run the query in MySQL Workbench, it completes in about 75 seconds. When I run the same query in Mathematica using SQLExecute[], it runs around 150 seconds (sometimes a little more, sometime a little less.) It's not a huge deal, but I'm wondering if I can do anything to speed that up.

As a test, I tried running the query with increasing limits on the results returned. The results were (numbers are in seconds):

  • limit 1: 75.00 (as a baseline, MySQL Workbench did this in 72.30)
  • limit 100: 73.12
  • limit 10000: 73.47
  • limit 1000000: 97.91
  • limit 2000000: 132.08
  • limit 3000000 (beyond the number of rows returned): 151.64

SQLExecute[] has an option of "BatchSize", which defaults to 1000. I tried changing that to 10000 and running the full query and it completed in 154.81, so that didn't seem to solve anything.

For what it's worth, here are my connection details:

conn = OpenSQLConnection[JDBC[
    "MySQL(Connector/J)",
    "[DATABASE HOST]"],
   "Version" -> 3.1, 
    "Name" -> "[DATABASE]",
   "Catalog" -> Automatic,
   "Description" -> "[DESCRIPTION]",
   "Password" -> "[PASSWORD]",
   "Properties" -> {},
   "ReadOnly" -> Automatic,
   "RelativePath" -> False,
   "TransactionIsolationLevel" -> Automatic,
   "UseConnectionPool" -> Automatic,
   "Username" -> "[USERNAME]"];

I'm then running SQLExecute[] using that connection. I use these same credentials to connect via MySQL Workbench.

I'm okay if this is just the cost of doing business in WL, but if there's anything I haven't tried that might help, please let me know.

POSTED BY: Dave Lartigue

Not sure if this will help, but you mentioned "the database I'm querying is remote". In case the bandwidth of your connection is the bottleneck, it might be possible to activate compression so that the connection may work faster. When creating ssh tunnels this is possible by adding a -C flag and it is also possible by setting up a reverse proxy in the server.

POSTED BY: Gustavo Delfino
Reply to this discussion
Community posts can be styled and formatted using the Markdown syntax.
Reply Preview
Attachments
Remove
or Discard

Group Abstract Group Abstract