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.