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
''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)+'''''
--SELECT * FROM datABC