angelnjj
asked on
OPENQUERY () slow from linked server, if run from PL/SQL Developer significantly faster...ideas?
Hello, I have a linked server to Oracle. If I run a SELECT statement using OPENQUERY, it takes 20 mintues to return 8000 records.
BUT If I run the same SELECT statement in PL/SQL Developer, it takes less than a minute.
I need to 'speed up' the query as currrently (the 8k records) is only 1 day's worth of data and I need to do a 3 year date range.
Code below...I've abbreviated it a bit, I have several fields coming from each table (rgv and nmv). They are each views in the oracle db - I've tried dissecting the views and pulling directly from tables to no avail, same speed issue.
SET @strsql = '
SELECT * INTO datABC
FROM OPENQUERY(ORACLE_SVR,
''SELECT rgv.field1, rgv.field2, rgv.field3 etc, nmv.field1, nmv.field2, nmv.field3 etc
FROM ORACLEVIEW1 rgv, ORACLEVIEW2 nmv
WHERE rgv.field1 = nmv.field1
AND rgv.date1 >= '''''+convert(char(11),@Be ginDate,13 )+'''''
AND rgv.date2 <= '''''+convert(char(11),@En dDate,13)+ '''''
'')'
EXEC (@strsql)
--SELECT * FROM datABC
BUT If I run the same SELECT statement in PL/SQL Developer, it takes less than a minute.
I need to 'speed up' the query as currrently (the 8k records) is only 1 day's worth of data and I need to do a 3 year date range.
Code below...I've abbreviated it a bit, I have several fields coming from each table (rgv and nmv). They are each views in the oracle db - I've tried dissecting the views and pulling directly from tables to no avail, same speed issue.
SET @strsql = '
SELECT * INTO datABC
FROM OPENQUERY(ORACLE_SVR,
''SELECT rgv.field1, rgv.field2, rgv.field3 etc, nmv.field1, nmv.field2, nmv.field3 etc
FROM ORACLEVIEW1 rgv, ORACLEVIEW2 nmv
WHERE rgv.field1 = nmv.field1
AND rgv.date1 >= '''''+convert(char(11),@Be
AND rgv.date2 <= '''''+convert(char(11),@En
'')'
EXEC (@strsql)
--SELECT * FROM datABC
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER