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:

3 Comments
Level I

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 !

Level V

  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.

Super User

I would love a performance increase for JSL Open Database() or Execute SQL().  

 

From Oracle tips by Burleson, http://www.dba-oracle.com/phys_odbc_slow.htm :

Databases that are subject to high traffic may have performance problems when ODBC is used. Many Oracle applications experience increased overhead because connecting via ODBC is less efficient than using a native API call to the database. It is therefore recommended that ODBC be replaced with a native communications tool such as the Oracle Call Interface (OCI). ODBC generally works fine for occasional database queries but is too slow to be a permanent fixture in most production applications.