Oracle to SQLServer 2008 R2 - Why so slow?
833146Jan 25 2011 — edited Mar 2 2011Setup
Windows 2008 R2 Standard server with MS SQL Server 2008 R2, both 64 bit, installed.
Oracle 11gR2 Clients installed, selected Administrator install for both the 32 and 64 bit versions.
32bit Version installed to
C:\Oracle\product\11.2.0\client_32 and copied the tnsnames.ora to the network/admin folder.
64bit Version installed to
C:\Oracle\product\11.2.0\client_64 and copied the tnsnames.ora to the network/admin folder.
Both servers are connected to the same network switch
Ping to Oracle data server < 1ms.
tnsping to Oracle data server for the 32 and 64 bit both show "OK" 10 or 20ms.
Problem
A simple select from an Oracle view which returns 32639 records.
Using BIDS and just changing the connection manager each time I get:
1) Using the "Microsoft OLE DB Provider for Oracle" it takes 22m26s - approx 24 records a second
2) Using the "Oracle Provider for OLE DB" it takes 28m36s - approx 19 records a second
I have tried uninstalling and reinstalling the Oracle 11gR2 client several times.
Rebooting the server at each step.
Running sqlplus and logging in, takes a few seconds to log in, then examining the log.xml in
C:\Users\<username>\Oracle\oradiag_<username>\diag\clients\user_<username>\host_3132834696_76\alert
I found that it was complaining about a missing folder structure from
C:\oracle\product\11.2.0\client_64\
So I created log\diag\clients folders in both the 64 and 32 bit client installs.
Did not make any difference in transfer speed.
I have run the same query in sqlplus and I get about about 5 lines of data, big pause, then another set, big pause etc
until end of the query.
The Oracle data server is reported as 10.2.0.4.0 by sqlplus.
I have tried during one of the installs to use the Oracle 10g client, made no difference.
Another set of data I need to transfer is 40x bigger - almost 20hours by the speeds above !!
Transfering data from another MSSQL Server 2008 to this one - 422711 rows, twice as many fields - 7 Seconds
How can I get the Oracle data transfer to run quicker?
Thanks in advance
Simon