What is the difference between Open Database() and New SQL Query() when accessing databases?

[#1]

Hi,

I am trying to pull down a view from an Oracle database.  The Data Base Name is ACTPROD.WORLD, Schema is ABASE, and Table is RPT_H25_3MONTHS_VIEW

Using Database -> Open Table, JMP created the following script:

Open Database(
	"DSN=ACTPROD.WORLD;UID=;PWD=;DBQ=ACTPROD.WORLD;DBA=W;APA=T;EXC=F;FEN=T;QTO=T;FRC=10;FDL=10;LOB=T;RST=T;BTD=F;BNF=F;BAM=IfAllSuccessful;NUM=NLS;DPM=F;MTS=T;MDI=F;CSR=F;FWC=F;FBS=64000;TLO=O;MLD=0;ODA=F;STE=F;TSZ=8192;AST=FLOAT;",
	"SELECT * FROM \!"ABASE\!".\!"RPT_H25_3MONTHS_VIEW\!""
)

This script opens a data table in <5min, but we would like to be able to modify the query using the Query Builder.

Using Database -> Query Builder, JMP created the following script.  This script takes >3hrs just to load the Query Builder UI.

New SQL Query(
	Version( 130 ),
	Connection(
		"ODBC:DSN=ACTPROD.WORLD;UID=;PWD;DBQ=ACTPROD.WORLD;DBA=W;APA=T;EXC=F;FEN=T;QTO=T;FRC=10;FDL=10;LOB=T;RST=T;BTD=F;BNF=F;BAM=IfAllSuccessful;NUM=NLS;DPM=F;MTS=T;MDI=F;CSR=F;FWC=F;FBS=64000;TLO=O;MLD=0;ODA=F;STE=F;TSZ=8192;AST=FLOAT;"
	),
	QueryName( "RPT_H25_3MONTHS_VIEW" ),
	Select(
		Column( "TEST_ID", "t1" ),
		Column( "OBJECT", "t1" ),
		Column( "BATCH", "t1" ),
		Column( "WELL", "t1" ),
		Column( "PLATE", "t1" ),
		Column( "SEQUENCE", "t1" ),
		Column( "%C(%)", "t1" ),
		Column( "RATE", "t1" ),
		Column( "RATE_UNIT", "t1" ),
		Column( "REPLICATE", "t1" ),
		Column( "SOIL", "t1" ),
		Column( "SPECIES_ABBR", "t1" ),
		Column(
			"CREATED_DATE",
			"t1",
			Numeric Format( "m/d/y h:m:s", "0", "NO", "" )
		)
	),
	From( Table( "RPT_H25_3MONTHS_VIEW", Schema( "ABASE" ), Alias( "t1" ) ) ),
	Where(
		In List(
			Column( "TEST_ID", "t1" ),
			{"H2.5-20190618", "H2.5-20190723", "H2.5-20190806"},
			UI( SelectListFilter( ListBox, Base( "Categorical" ) ) )
		) & Is Not Null(
			Column( "%C(%)", "t1" ),
			UI( NullTest( Base( "Continuous" ) ) )
		)
	)
) << Modify

What are the Open Database and New SQL Query scripts doing differently that causes such a discrepancy in execution time?  Is there a way to speed up execution of Query Builder or incorporate the Query Builder UI into the Open Database script?

I am really new to working with databases, it would be very helpful if you could be detailed in your replies so I can learn.

Thanks!

Brian

[#2]

Hi Brian,

In your example, the Open Database is doing a SELECT * and just grabbing all the data.  This is going to be much faster then executing the WHERE clause that you have in the Query Builder script.  Also, you are doing a Modify on the Query Builder to bring up the UI.  You might want to try Run Background() there instead to run the query on a background thread.  Without seeing all of the data, I think removing the WHERE clause on the Query Builder script and grabbing all of the data would likely be faster.

Brian Corcoran

JMP Development

[#3]

You might need one or more indexes on your table for more efficient querying with a WHERE clause.  I would suggest tuning your query in an interactive Oracle tool like PL/SQL Developer, Toad, etc.  Use EXPLAIN PLAN to see what's costing you the most in your query, and to identify where an index would be helpful.  Once your query is tuned use either OPEN DATABASE or EXECUTE SQL to run it.

[#4]

Hi Brian,

I tried removing the WHERE clause and using Run Background, but the execution time is still 3+hrs

New SQL Query(
	Version( 130 ),
	Connection(
		"ODBC:DSN=ACTPROD.WORLD;UID=;PWD=;DBQ=ACTPROD.WORLD;DBA=W;APA=T;EXC=F;FEN=T;QTO=T;FRC=10;FDL=10;LOB=T;RST=T;BTD=F;BNF=F;BAM=IfAllSuccessful;NUM=NLS;DPM=F;MTS=T;MDI=F;CSR=F;FWC=F;FBS=64000;TLO=O;MLD=0;ODA=F;STE=F;TSZ=8192;AST=FLOAT;"
	),
	QueryName( "RPT_H25_3MONTHS_VIEW" ),
	Select(
		Column( "TEST_ID", "t1" ),
		Column( "OBJECT", "t1" ),
		Column( "BATCH", "t1" ),
		Column( "WELL", "t1" ),
		Column( "PLATE", "t1" ),
		Column( "SEQUENCE", "t1" ),
		Column( "%C(%)", "t1" ),
		Column( "RATE", "t1" ),
		Column( "RATE_UNIT", "t1" ),
		Column( "REPLICATE", "t1" ),
		Column( "SOIL", "t1" ),
		Column( "SPECIES_ABBR", "t1" ),
		Column( "CREATED_DATE", "t1", Numeric Format( "m/d/y h:m:s", "0", "NO", "" ) )
	),
	From( Table( "RPT_H25_3MONTHS_VIEW", Schema( "ABASE" ), Alias( "t1" ) ) )
		
) << Run Background()

Is there a way to use SELECT * with the New SQL Query() script I tried the following but am getting a syntax error.

New SQL Query(
	Version( 130 ),
	Connection(
		"ODBC:DSN=ACTPROD.WORLD;UID=;PWD=;DBQ=ACTPROD.WORLD;DBA=W;APA=T;EXC=F;FEN=T;QTO=T;FRC=10;FDL=10;LOB=T;RST=T;BTD=F;BNF=F;BAM=IfAllSuccessful;NUM=NLS;DPM=F;MTS=T;MDI=F;CSR=F;FWC=F;FBS=64000;TLO=O;MLD=0;ODA=F;STE=F;TSZ=8192;AST=FLOAT;"
	),
	QueryName( "RPT_H25_3MONTHS_VIEW" ),
	Select("*"),
	From( Table( "RPT_H25_3MONTHS_VIEW", Schema( "ABASE" ), Alias( "t1" ) ) )
		
) << Run Background()
		

Thanks!

Brian

[#5]

Hi Brian,

I'm sorry, but Query Builder does not accept *.  I think you probably need to follow Peter's tuning suggestion, or stick with Open Database or Execute SQL.

Brian