Performance problem while using Linked Servers

[Raja]

I am transferring data from SQl Server 2000 to Oracle through Linked
Servers.It takes considerable amount of time while transferring data
from SQL Server to oracle.Both these databases are at remote
locations.I am executing a Stored procedure on SQL Server and what it
does is,It has an Insert statement which reads the data from a table in
SQL Server and inserts it into the Oracle table. It is a very time
consuming process.Vice-Versa it takes very less time for Oracle-SQL
Server transfer.If anybody has a clue towards reducing the time in SQL
Server-ORACLE transfer.

[Ryan]

How about extracting the data, sending the file and then inserting at
the other end ? i.e. dump to csv then import from csv. Not sure about
the Oracle side, but doing this from SQL is straight forward enough.

Ryan

Raja wrote:

I am transferring data from SQl Server 2000 to Oracle through Linked
Servers.It takes considerable amount of time while transferring data
from SQL Server to oracle.Both these databases are at remote
locations.I am executing a Stored procedure on SQL Server and what it
does is,It has an Insert statement which reads the data from a table in SQL Server and inserts it into the Oracle table. It is a very time
consuming process.Vice-Versa it takes very less time for Oracle-SQL
Server transfer.If anybody has a clue towards reducing the time in SQL Server-ORACLE transfer.

[Raja]

Thanks,Ryan
Yes,but the entire process has to be automated.At present I have an
Interface where in the records selected are transferred through the
execution of a Stored procedure.As suggested by you,we can also
transfer the data in the form of an XML through Webservice.For some
reasons WebService is not used,hence the only option is Linked Server.
Dumping the data into a File(CSV or XML) and inserting the same
into Oracle isnt a problem.Do you have any suggestions for sending the
file?.

Regards,
Raja

[Ryan]

How about using a DTS package to extract the data into a CSV and then
saving / moving this file onto the other server ? You can script moving
the file to wherever you need, or saving it there in the first place.
You'll need to understand how to script this, but it should be
reasonably straight forward. The bit I don't know is the Oracle end for
importing it once you have the file. As you are extracting the data and
then importing it anyway, this might be quicker than copying directly
as you are doing.

So, your DTS package would contain a connection to your database, a SQL
task for running your SP linked to a Text File (source) for your CSV
(selects everything from the SP into this file), and a VB ActiveX
script to move the file to the new server......Then do whatever you
need to do on the Oracle side. OK, so it's split the task into a
process for SQL and a process for Oracle and doesn't automate the
'entire' process, so hopefully someone can suggest a better way.

Ryan