Performanceproblem with subquery to Oracle Linked Server

Hello

I hope anyone can help me here with a Problem between my Microsoft SQL
Server 2000 SP 3 and an Oracle 8 Server. I have to copy records from a Oracle
table (3.000.000 records by now) to a SQL Server table (8.000.000 records by
now) daily (somthing like an incremental backup). The Oracle Server is
configured as Linked Server and I use the following SQL statement to do this:

INSERT INTO CALLDETAIL
(CALL_TYPE,TRANS_TYPE,DISPOSITION,....46more fields...)

SELECT CALL_TYPE,TRANS_TYPE,DISPOSITION,....46more fields...
from ORACLE..ACC.CALLDETAIL
WHERE TERM_DATE > (SELECT MAX(TERM_DATE) FROM CALLDETAIL)

The query is placed in a DTS and runs once a day and normally copies between
4000 and 5000 rows. My problem is that the query takes between 90 and 130
minutes but when I use a hardcoded date instead of the subquery it only takes
2 minutes maximum. The subquery itself runs only 1-2 sec. As soon as I dont
use a hardcoded date it takes hours. I tried it by using the Query Analyzer
and placing the date in a int variable to used it instead of the subquery.

declare @datevalue int
set @datevalue = 1050208
SELECT CALL_TYPE,TRANS_TYPE,DISPOSITION,....46more fields...
from ORACLE..ACC.CALLDETAIL
WHERE TERM_DATE > @datevalue

I tried the Microsoft and the Oracle OLEDB Provider without success.
Even the OPENQUERY function hadn't helped because I can't use a dynamic
datevalue within the OPENQUERY function. Has anyone an Idea what I have to do
to improve the performance ?

I would use dynamic query + pass-through.

e.g.
declare @sql nvarchar(1000)
set @sql='select *
from openquery(oracle,''select a,b,c from tb where
term_date>'+cast(@datevalue as nvarchar)+''') as pt'

insert into localtb(a,b,c)
exec(@sql)


--
-oj


"Christian Mersch" wrote in
message
 > Hello
 >
 > I hope anyone can help me here with a Problem between my Microsoft SQL
 > Server 2000 SP 3 and an Oracle 8 Server. I have to copy records from a
 > Oracle
 > table (3.000.000 records by now) to a SQL Server table (8.000.000 records
 > by
 > now) daily (somthing like an incremental backup). The Oracle Server is
 > configured as Linked Server and I use the following SQL statement to do
 > this:
 >
 > INSERT INTO CALLDETAIL
 > (CALL_TYPE,TRANS_TYPE,DISPOSITION,....46more fields...)
 >
 > SELECT CALL_TYPE,TRANS_TYPE,DISPOSITION,....46more fields...
 > from ORACLE..ACC.CALLDETAIL
 > WHERE TERM_DATE > (SELECT MAX(TERM_DATE) FROM CALLDETAIL)
 >
 > The query is placed in a DTS and runs once a day and normally copies
 > between
 > 4000 and 5000 rows. My problem is that the query takes between 90 and 130
 > minutes but when I use a hardcoded date instead of the subquery it only
 > takes
 > 2 minutes maximum. The subquery itself runs only 1-2 sec. As soon as I
 > dont
 > use a hardcoded date it takes hours. I tried it by using the Query
 > Analyzer
 > and placing the date in a int variable to used it instead of the subquery.
 >


 > SELECT CALL_TYPE,TRANS_TYPE,DISPOSITION,....46more fields...
 > from ORACLE..ACC.CALLDETAIL

 >
 > I tried the Microsoft and the Oracle OLEDB Provider without success.
 > Even the OPENQUERY function hadn't helped because I can't use a dynamic
 > datevalue within the OPENQUERY function. Has anyone an Idea what I have to
 > do
 > to improve the performance ?
 >