(SQL Server 2000, SP4)

Hello all!

I am experiencing some incredibly slow performance in migrating data between
SQL Server 2000 and Oracle 10g. The SQL Server database is the source and
the Oracle schema is the target.

I am sending a handful of integer and relatively short string columns (no
TEXT/CLOB). There's about 7000 rows. In my setup, it takes 30 minutes to
transfer what I consider a paltry amount of data!

I'm using UDLs for my source/target connections, and a standard Transform
Data Task between the two (the source is a query and the destination is a
table).

I have made sure I've got all the updated OLE DB drivers. I tried using the
MS-supplied OLE DB Oracle provider, as well as the Oracle-supplied one (the
MS one is a bit slower). I've tried to ensure that the network/my client
workstation isn't the issue by running the DTS packages on the Source
machine. I've toggled every property on the package that might have
anything to do with performance (the only one that seems to make a
demonstrable difference is to UNcheck the "Use OLE DB service components"
option.

My only inkling that this *should* be faster is that if I replicate the
target data model from Oracle to SQL Server, so that both my source and
target platforms are SQL Server, then those 7000 rows are processed *very*
quickly! That makes me think the issue is on the Oracle side or the
interaction between the SQL Server/Oracle OLE DB drivers. But I've tried
different Oracle servers (and versions, including 8.1.7), and they all seem
to behave about the same.

Where applicable, I've tried to adhere to the performance suggestions at
this link:

http://www.databasejournal.com/featu...le.php/1499481

I'm at my wits end. Any advice/suggestions would be *hugely* appreciated!

John Peterson

Hello John,

A lot of people see this slow down. I think it just has to do with the drivers
and how they are used. I note that in SSIS a 3rd party has specifically
written an Oracle speed loading component.

A lot of people revert to throwing the data out to flat file and then use
SQL*Loader to get the data in from there


Allan