August 26, 2002 at 11:17 am
I have a developer using DTS to pull data from Oracle to SQL 2000, and the throughput is absurdly slow. He is moving only 1000 records per minute, without doing any transformation. I have heard that the OLE DB method of connecting to Oracle is slow, but that there might be a faster way using the .Net connection method. Is this true? Can the .Net add-on using Oracle OCI be used with DTS. Please help!! Thanks.
August 26, 2002 at 3:34 pm
That is extremely slow. I usually get 2000+ records (text at that) in a few seconds. I am on a 10 MB network and this is running from a third machine so the data must cross that machine to reach the server. It however does depend on the width of the data you are moving, network speed, indexes can be an issue on both sides, various things. Can you give a bit more information and do you have a lot of indexes on the SQL table.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
September 25, 2002 at 3:52 pm
I need help with a similar problem on 7.0.
A SQL Server DTS package that copies data from a NT SQL Server 7.0 database at a remote location to a local VMS Oracle 8.1.6 database over a WAN takes exceedingly longer amount of time when executed from the EM console on the remote NT server than when it is executed from the EM console of the local NT workstation.
I would expect the exact opposite to be true since data must pass through the local NT workstation when the DTS package is executed from the workstation. I see very few resources being utilized on the remote NT server while the package is being executed.
I've tried both MS ODBC for Oracle and MS OLE provider for Oracle.
Information on the boxes:
Server (remote)
Windows NT 4.0
Build 1381 Service Pack 5
850 MHz 512 MB
MDAC version that is closest is 2.1.3711.11(GA)
Workstation (local)
Windows NT 4.0
Build 1381 Service Pack 6
550 MHz 256 MB
MDAC version that is closest is 2.5 SP2 (2.52.6019.2)
Is anyone familiar with this type of problem?
Regards,
Warren
September 26, 2002 at 1:38 am
I also have a DTS job pulling recs from Oracle to SQL 2000 using the standard OLE DB driver.
The job itself runs at a decent speed, similar to Antares, but viewing the properties of the "Transform Data Task" takes a long time (about a minute) to appear.
In the same package is a straight SQL to SQL transform data task, and this opens instantly.
Has anyone else come across this?
September 26, 2002 at 2:52 am
Hello All, we are not having this problem with the MS OLE DB method, it is faster than ODBC.
However there are servile ways to improve performance with Oracle. It is recommended to use the OLE DB provider for all database connections to external data sources. I am not sure why this is; I think it is do with the API using a direct connection rather than going thought the ODBC’s.
There are couple of things you can do to tune DTS. First Look at the type of tasks you are using.
Data pumps are great for transformations and loading data, but if you have not use DTS much you can end up with an inefficient set of transformations. When creating a custom Data pump DTS will create a transformation pre column. This causes a separate transformation for every operation for every column, a bit slow….
I have read you can get another performance increase for large data sets by limiting Initial insert batch size, this found on the options tab in the data pump task. This limits the number of row inserted into the table on load, by creating batches (note it has a limit of 999999)
Try using BCP or the Bulk insert task instead it is fast than data pump.
BCP T-SQL is faster again. You will have to place this in a store procedure or a SQL task in DTS.
Last thing Network, make sure you are on the same sub net this can improve network throughput.
Have a look at
http://www.sql-server-performance.com
Hope this helps
Myles Matheson
September 26, 2002 at 7:18 am
Honestly, I think the DTS method is more complex (and clearly slower) than it needs to be. I pull alot of data over from Oracle into SQL 2000, and this method works very quickly: Just set up linked servers and use an OPENQUERY statement:
Select * From OpenQuery ([OracleServerName], 'some valid SQL code here')
All this does is execute the SQL statement as a string against the Oracle target. Works like a charm.
ciao ciao
September 26, 2002 at 2:38 pm
I experienced the same issue on a couple of different servers I've worked with. The answer we came up with was setting up linked servers to attatch to a SqlNet connection for Oracle, and use stored procedures to insert from the Openquery command. It worked very well.
We did run into a couple of issues with Sql doing implicit conversions, but if you write your procs to explicitly convert anything needing it, rather than relying on sql to do it, it eliminates that concern. Just be sure to check your types.
Edited by - Scorpion_66 on 09/26/2002 2:41:02 PM
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply