Oracle to SQL Server DTS Slow

[dbomrrsm]

On previous projects I have done the above without the same heartache I have on this project.

The data transform task is really straight forward moving a select from an Oracle table into a table in SQL Server. There are no joins in the query just a straight select from one oracle table.

In past projects I have seen these types of transforms move millions of rows in an hour but this one struggles to do 100,000 rows in an hour.

What things can affect the performance of transfers from Oracle to SQL Server.

Any help or advice much appreciated.

TIA

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[mrdenny]

Disk issues, Network performace, WAN vs. LAN (etc).

Indexes on the inserting table.

CPU load on either machine.

Low available RAM on either machine.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[dbomrrsm]

Mr Denny

Thanks for the advice - I will look into this further.

This is really confusing me though - I can use Toad for Oracle on my PC and run the select in Oracle that the DTS runs - I can then save the results (returned in about 20 seconds 500k rows) using the export function in Toad, to a text file on the server which exports all 500k rows in about 2 minutes.

Doing exactly the same thing in DTS (i.e. Oracle to text file with the text file being in the same server location) it takes 5 hours.

I can only think it is down to the connection "client" used by DTS against the client used by Toad for Oracle.

I cant see that it is anything else - same data being transferred from the same location to the same destination.

In the DTS I have tried OLE DB connection and ODBC connection and they are both about the same - extremely poor.

Can anyone throw any light on this as this DTS performance is totally unacceptable.

TIA

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[mrdenny]

DTS is very slow when it comes to dealing with Oacle.

Since you've probably already installed the Oracle client tools on the SQL Server why not use the Oracle version of bcp called datapump to export to the text file. Then use DTS to load the file.

I've found that it's usually faster to use a softwares native tool to deal with that system.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[dbomrrsm]

Mr D

Thanks again - I will look into datadump.

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[dbomrrsm]

Looks like datapump is a feature of 10g and we are using 9i !

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[mrdenny]

I know that there is a 9i equilivent.

http://orafaq.com/faqiexp.htm

Check your ORAHOME\bin folder for exp. That appears to allow you to export. Check with the Oracle forums. They may know of a better tool for exporting data.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[GeorgeMichaelDBA]

DataPump will not work even if you have a 10g
DataPump produces binary format that could be imported into Oracle only
There is an option to use wisdomforce.com fastreader to move large volumes of data from Oracle to flat text files. fastreader will create loader into sqlserver too

 

[mrdenny]

Sounds like that will probably be the best bet for the OP then.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com