Performanceproblem with subquery to Oracle Linked Server
From: Christian Mersch (Mersch_at_discussions.microsoft.com)
Date: 02/11/05
- Next message: oj: "Re: Performanceproblem with subquery to Oracle Linked Server"
- Previous message: Maer: "Re: Login failed for Server\Guest"
- Next in thread: oj: "Re: Performanceproblem with subquery to Oracle Linked Server"
- Reply: oj: "Re: Performanceproblem with subquery to Oracle Linked Server"
- Messages sorted by: [ date ] [ thread ]
Date: Thu, 10 Feb 2005 23:23:01 -0800
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 ?
- Next message: oj: "Re: Performanceproblem with subquery to Oracle Linked Server"
- Previous message: Maer: "Re: Login failed for Server\Guest"
- Next in thread: oj: "Re: Performanceproblem with subquery to Oracle Linked Server"
- Reply: oj: "Re: Performanceproblem with subquery to Oracle Linked Server"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
