Query Builder for Oracle database slow unless use Custom SQL() argument in New SQL Query() function

[#1]

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.

[#2]

This is just a couple comments from experience:

• Oracle recalls a last query, so often the second call is faster than the first. To test this, close your connection and log off your system (so you have a new system userid), then run only your second query and get the performance time. Check if it is less nearer to 8 seconds or much larger or near 147 seconds.
• JMP experts will have to verify what is really going on, but it sounds like JMP might have built the table then ran the query statements in the first New SQL Query(). And possibly the second New SQL Query(), JMP just sends the sql statement to the database and returns the results.
• When I run the same or similar query over different days, I like use Query Builder to build and test the query.  Then I get the  SQL and write my script to use Open Database( "connection string", "querystring", "tablename");

Good Luck!

[#3]

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.

[#4]

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.