What inspired this wish list request?
Our JMP scripts that run in JMP 17 quickly are now much slower in JMP 18 because OpenDatabase() is performing additional and unnecessary data dictionary queries.
What is the improvement you would like to see?
Please allow users to disable the data-dictionary queries.
DETAILS -
In JMP 17, when calling OpenDatabase() with custom SQL against an ODBC connection (Oracle), JMP would run initially run two additional SQL queries prior to running the user’s custom SQL statement:
SELECT VALUE FROM SYS.NLS_DATABASE_PARAMETERS WHERE PARAMETER = 'NLS_CHARACTERSET'
SELECT VALUE FROM SYS.NLS_DATABASE_PARAMETERS WHERE PARAMETER = 'NLS_NCHAR_CHARACTERSET'
These two queries returned no data but executed very quickly on the database server, and thus caused a negligible delay (0.5~1.0sec) to the JMP user.
In JMP 18, when performing the exact same OpenDatabase() call with custom SQL against the same ODBC connection (Oracle, same server), JMP runs the same 2 pre-customSQL statements, as well as 2 additional pre-customSQL statements (statements JMP18-SQL1_DD and JMP18-SQL2_DD listed below).
Unfortunately, on our enterprise databases (which have a rather large data dictionary), these 2 additional SQL (DD=Data Dictionary) statements can take between 30 and 45 seconds to execute/return, causing a significant delay in the running of the JMP user’s custom SQL. And, given that the JMP user merely wants the results of their custom SQL and has no need for the DD sql results (can’t even access them or use them in any way to affect JMP which closes the database connection immediately), this additional delay serves no purpose.
Therefore, I am requesting that a new JMP 18 release be created with a boolean preference (Preferences->Tables->ODBC Inspect Data Dictionary) defaulting to ‘on’, which can be set to ‘off’, thereby disabling these two additional SQL statements from running.
For more technical details, please see the attached timings of JMP 17 and 18 and the pre-customSQL statements that are run when using OpenDatabase().
Fred Zellinger.
JMP 17 OpenDatabase() Transactions with database:
(0.00~0.41sec) - exchange connection string, auth, client/database details
( @0.41sec) - SELECT VALUE FROM SYS.NLS_DATABASE_PARAMETERS WHERE PARAMETER = 'NLS_CHARACTERSET' = ORA-01403: no data found
( @0.51sec) - SELECT VALUE FROM SYS.NLS_DATABASE_PARAMETERS WHERE PARAMETER = 'NLS_NCHAR_CHARACTERSET' = ORA-01403: no data found
( @0.68sec) - ...run user custom SQL
( @0.65sec) - ...begin data return
JMP 18 OpenDatabase() Transactions with database:
( 0.00~ 0.74sec) - begin connection, exchange connection string, auth, client/database details
( @ 0.74sec) - SELECT VALUE FROM SYS.NLS_DATABASE_PARAMETERS WHERE PARAMETER = 'NLS_CHARACTERSET' = ORA-01403: no data found
( @ 0.84sec) - SELECT VALUE FROM SYS.NLS_DATABASE_PARAMETERS WHERE PARAMETER = 'NLS_CHARACTERSET' = ORA-01403: no data found
( 0.92~26.90sec) - Run JMP18-SQL1_DD and return data
(26.90~43.32sec) - Run JMP18-SQL2_DD and return data
( @43.33sec) - ...run user custom SQL
( @43.34sec) - ...begin data return
JMP18-SQL1_DD:
select * from (
select
null table_qualifier, o1.owner table_owner, o1.object_name table_name
, decode(o1.owner,'SYS', decode(o1.object_type,'TABLE','SYSTEM TABLE','VIEW', 'SYSTEM VIEW', o1.object_type), 'SYSTEM'
, decode(o1.object_type,'TABLE','SYSTEM TABLE','VIEW', 'SYSTEM VIEW', o1.object_type), o1.object_type
) table_type
, null remarks
from all_objects o1
where o1.object_type in ('TABLE', 'VIEW')
union
select
null table_qualifier, s.owner table_owner, s.synonym_name table_name
, 'SYNONYM' table_type
, null remarks
from all_objects o3, all_synonyms s
where o3.object_type in ('TABLE','VIEW')
and s.table_owner= o3.owner and s.table_name = o3.object_name
union
select
null table_qualifier, s1.owner table_owner, s1.synonym_name table_name
, 'SYNONYM' table_type
, null remarks
from all_synonyms s1
where s1.db_link is not null
) tables
WHERE 1=1 AND ( table_type = 'TABLE' )
ORDER BY 4,2,3
JMP18-SQL2_DD:
select * from (
select
null table_qualifier, o1.owner table_owner, o1.object_name table_name
, decode(o1.owner,'SYS'
, decode(o1.object_type,'TABLE','SYSTEM TABLE','VIEW', 'SYSTEM VIEW', o1.object_type), 'SYSTEM'
, decode(o1.object_type,'TABLE','SYSTEM TABLE','VIEW', 'SYSTEM VIEW', o1.object_type)
, o1.object_type) table_type
, null remarks
from all_objects o1
where o1.object_type in ('TABLE', 'VIEW')
union
select
null table_qualifier, s.owner table_owner, s.synonym_name table_name, 'SYNONYM' table_type, null remarks
from all_objects o3, all_synonyms s
where o3.object_type in ('TABLE','VIEW')
and s.table_owner= o3.owner and s.table_name = o3.object_name
union
select
null table_qualifier, s1.owner table_owner, s1.synonym_name table_name, 'SYNONYM' table_type, null remarks
from all_synonyms s1
where s1.db_link is not null ) tables
WHERE 1=1 AND ( table_type = 'TABLE' )
ORDER BY 4,2,3
Why is this idea important?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.