Speed up SQL

[frankz]

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.

[cgross]

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 !

[hardner]

  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.

[pmroz]

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. 

[Martin]

@pmroz can this be implemented in JMP?  I too am finding it takes up to 20 minutes to run a query to an Oracle DB in JMP, while it only takes about 20 seconds to run an identical query using Excel.  In fact, any time I have compared performance between JMP and Excel (over the past 8 years), JMP is much slower than Excel.

Any help would be greatly appreciated!!!

[cgross]

As @pmroz hinted, using the Oracle Call Interface (OCI) instead of ODBC might be a better solution to this problem - if your database supports it (I can not comment on OCI as I have never used it myself) I would like to stress that there is software around (for a specific database, queried via SQL) which uses ODBC and works much faster than JMP under the same circumstances (see my post, 03-05-2018), so just reaching this level would be a real improvement for JMP. In view of the age of this call (without perceivable improvement in this respect from JMP13 to JMP15), I personally came to the conclusion that the combination JMP/SQL is probably not the way to go (if you can avoid it), if you need to fetch more than a moderate number of records from a database.

[Sarah-Sylvestre]

Hi @frankz, thank you for your suggestion! We have captured your request and will take it under consideration.

[SamGardner]

@frankz we looked at this and evaluated how well this performs in our latest version of JMP, version 17.  It appears this is no longer an issue.  I apologize for not responding to this sooner, but I hope you find this taken care of in more recent versions of JMP.  If not, please let us know.