Link to home
Start Free TrialLog in
Avatar of angelnjj
angelnjjFlag for United States of America

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),@BeginDate,13)+'''''
AND rgv.date2 <= '''''+convert(char(11),@EndDate,13)+'''''
'')'
EXEC (@strsql)

--SELECT * FROM datABC
ASKER CERTIFIED SOLUTION
Avatar of Som Tripathi
Som Tripathi
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of angelnjj

ASKER

Thanks...I've got experience doing that, and will try in this scenario - I just didn't want to recreate the wheel...thanks!