Hi,
I have an issue with an Oracle query that seems to run slowly. By slowly, I mean that if I take the exact same query that takes 30-50 seconds to run in EG and run it in Oracle SQL Developer, it consistently completes in less than one second. Notes and code are below. Also ran the query using SQL*PLUS on the same compute server that EG is running on and it also ran with sub-second response.
Question: Should I expect the SQL to show similar performance on EG vs SQL Developer/SQL*PLUS? Is it fair to compare using those two tools? If not, what is he right tool? All other suggestions are welcome and appreciated!
Thanks!
Ken
Notes: SAS server and the database sever reside in the same data center and connect via a 10GB fiber backbone.
The EG query is using SQL Pass-through
The final query result is one row
I have "options sastrace = ',,,d' sastraceloc = saslog" enabled
The query used in SQL Developer is taken directly from the log
SQL Developer is running locally on my desktop in a remote location connect via WAN
The outline of the query is below - I cannot post the full original query due to confidentially reasons
Other queries from EG to the same database perform well returning 1000's of rows in far less time
Code:
/* Pseudo query masked for confidential content */
proc sql threads noprint;
connect to oracle as alias1 (path="xxxxxxx" AuthDomain="xxxxxxxx" buffsize=10000);
create table final_result as
select strip(put( fld1,30.)) as uid,
strip(fld2) as node_type
from connection to alias1 ( select distinct fld1,
fld2
from table1
where to_node_uid in
( select fld3
from table1
where group=123456789 and fld3_type in
( select distinct fld_type
from table2
where class in ('A1','B2','C3','D4') ) ) );
quit;
run;
... View more
