Hi JMP users. When using the JMP Query Builder, we have fast response for SQL servers using "ODBC Driver 11 for SQL Server", but slow response on some Oracle servers using "Oracle in OraClient11g", on the order of 2 to 3 minutes. We discovered by accident that it works just fine using the Custom SQL() argument in the New SQL Query() function in the script, instead of using the generated script from JMP Query Builder.
Has anyone else seen slow response from Oracle ODBC?
Part of the problem as I understand it is that our log in account for this Oracle database does not allow viewing some tables in some schemas on the server, so the query builder takes time when first loading up to by-pass them. You may not be able to replicate if your database access includes all schemas and tables. Submitting direct Custom SQL bypasses this scanning of the schemas and tables?
In the example below, using Query Builder, selected columns and a filter. Took 147 seconds to run for a million rows. Now from Query Builder, select the SQL from the SQL tab, or use the "Convert to custom SQL" menu item and copy the Custom SQL (or run the query from the Custom SQL window), and paste it in as the argument for Custom SQL(), and it took only 7 to 8 seconds for a million rows.
// this is the JSL script generated by JMP Query Builder and saved to the datatable // takes ~ 147 seconds to run t1 = Tick Seconds(); New SQL Query( Version( 130 ), Connection(mycon), QueryName( "Test1_147s" ), Select( Column( "X1", "t1" ), Column( "X2", "t1" ), Column( "X3", "t1" ), Column( "X4", "t1" ), Column( "LAST_UPDATE_DATE", "t1", Numeric Format( "m/d/y h:m:s", "0", "NO", "" ) ) ), From( Table( "MY_DATABASE_TABLE", Schema( "SCHEMA" ), Alias( "t1" ) ) ), Where(EQ(Column( "X2", "t1" ),"AAAA",UI( Comparison( Base( "Categorical" ) ) ))) ) << Run foreground; t2 = Tick Seconds(); t2-t1; // 147 seconds // Now just copy the SQL code and put into the CustomSQL argument // takes ~ 8 seconds to run t1 = Tick Seconds(); New SQL Query( Connection(mycon), QueryName( "Test2_8s" ), CustomSQL( "SELECT t1.X1, t1.X2, t1.X3, t1.X4, t1.LAST_UPDATE_DATE FROM SCHEMA.MY_DATABASE_TABLE t1 WHERE ( ( ( t1.X2 = 'AAAA' ) ) )" ) ) << Run foreground; t2 = Tick Seconds(); t2-t1; // 8 seconds
For our JMP Query Builder best practices, will now have to show how to run and save the query and datatable from the Custom SQL window for Oracle databases.
This is just a couple comments from experience:
Thanks for those tips! Did some more testing.
I do see the effect of a second call to Oracle database, but still see the order of magnitude difference between the two JSL techniques. I only have to log out of JMP14 to test this. If I run the test on the slow schema, first time Custom SQL() takes 0.4 seconds. If I do repeat runs of the Custom SQL() script within the current session of JMP, it is reduced to 0.06 seconds. But this does not help for standard Query Builder code, takes 120 seconds no matter what. I believe the change of 0.4 to 0.06 between 1st and 2nd call to Oracle database, if happening for the slow schema, is in the noise of the repeat tests always around ~120 seconds.
I also tested a "fast" schema. Custom SQL first run is also ~0.4s, then reduces to 0.06 seconds for subsequent calls in active session. For standard Query Builder code the first call is 1.2s, subsequent calls in active JMP session reduces to 0.8s.
From these tests, seeing a reduction of ~0.3s for a repeat call to the Oracle database because it recalls a last query no matter the schema for Custom SQL(), but not seeing it in the "slow" schema using the Query Builder script style.
Here we don't like the Open Database/Close Database method (worried about hanging a database open), we prefer using the Query Builder, but we will have to use the Custom SQL() method and capture the SQL code from the Query Builder user interface.
Thank you for the performance data. It appears you have a solution.
It will be interesting to see, if JMP has a few insights or suggestions to share.