Hi,

I currently have a DTS job that movers about 40k to 50k rows from SQL 2K to
Oracle 9i DB. I am using Oracles own OLE DB to connect to the Oracle DB as
its quicker than ODBC.

Its taking 7 minutes to move 2000 rows using a transform data task. So I
guess its going to take over 2 hrs to move 40000 rows. Which is ridiculously
slow. When I use Microsofts OLE DB for Oracle it takes even longer that I
kill the process long before if finishes out of sheer bloody frustration.

I have tired using Link Servers and run a query to insert into the Oracle
DB. This doesnt seem to perform any better than the dts data pump task. So
is this just a fact of life or is there a quicker way of doing it. I am a
bit stumpt as going in reverse Oracle to SQL the performance is acceptable.


So has anyone any ways of improving performance

Cheers
Adrian

Ado wrote:
I assume it's a regular job you're going to be running?

If so, it may be quicker to pump the data out to text file and re-import
into Oracle. Not elegant but it works and takes the load off of the
servers using the current method.

It also depends on how much formatting you're applying to the data on
its way over to Oracle.

Other solutions are high-end message queuing type scenarios, but you
don't wanna go there.

I would agree with the export to text file then use SQL Loader to get it in
from the file. The added advantage is you have a backup copy of the data
this way.

--



Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - You thought DTS was good. here we show you the new stuff.
www.konesans.com - Consultancy from the people who know


"Cagey" <k@c.com> wrote

Hi Lads,

Thanks for the reply. It is a nightly Job and on day 1 it will be 400k
records after that 40k. There is no transformation on the data going from
SQL2k to Oracle. I considered Exporting the data to a file and FTPing it
accross. However the Oracle DB is not under my control and due to polictics
etc etc it would take weeks before I could get someone on the Oracle side to
write the necessary package.

Both systems are high spec and its not a network problem. In this day and
age of high speed networks and cheap memory/processors I have to resort to
exporting to a text file and FTPing to a server to be reloaded. Can any one
explain to me why its quicker to transfer Data from Oracle to SQL2k compared
to SQL2k to Oracle. Is is an Oracle/SQL2K problem ?

Cheers
Adrian


"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

The drivers IMHO are not that fast. If you Google for others who have asked
to do the same thing as you they have had the speed issue and resorted to
File -FTP

--



Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - You thought DTS was good. here we show you the new stuff.
www.konesans.com - Consultancy from the people who know


"Ado" <Adrian_donnelly (AT) hotmail (DOT) com> wrote