Speed up SQL

In JMP 13.2.1, JSL script "Open Database("DSN=mysql", sql)" is much slower than running the same "select" sql query on MySQL workbench or at mysql command line on same pc.  DSN points to MySQL ODBC driver installed locally in my win10 pc, (mysql-connector-odbc-5.3.7-winx64.msi).  The loading speed difference is very noticeable when "select" would return many lines such as 1 million line of data.  Wish JMP would improve this data connection and loading speed in next release.

Tracking Number:

Defect ID:

2 Comments
Established Member

The same is true if using JSL function "Open Database" for a sql query of a PI System database (OSIsoft, www.osisoft.com ). In this case, DSN points to a PI ODBC driver locally installed on my Win7Pro laptop. "Open Database" is much slower than running the same sql query through PI SQL Commander on the same machine. This is a real pain / bottleneck for anything above 100k lines of data  - strong support for this JMP wish !

Community Trekker

  Big thumbs up for this.... I found a huge difference with Oracle comparing queries with JSL Open Database() and Benthic Golden or SQL Developer using same SQL (JMP14).  I was able to erase that difference by fiddling with the settings of the datasource.  In my case boosting the "Fetch Buffer Size".  But completely agree that it seems JMP could/should be smarter about this since the other applications are.  I also have a more recent case where I may be seeing the offset again.  Maybe this different SQL wants a different setting but Golden seems to have no problem....ugh.