November 28, 2011 at 1:48 am
Hello Everybody,
i'm using a ssis package to retrieve a table from an oracle database. The oracle DBMS is located on another location and the ssis package is recieving dhe data through an IPSec VPN(2Mbits symmetric connection).
The problem is that it takes so much to download the whole table through the oledb component(more than 1hour for 100.000 rows).
Any suggestions on how to speed up the whole process?
Thank you.
November 28, 2011 at 12:12 pm
I would try to only pull the data you need in an incremental load rather than move the whole table.
November 28, 2011 at 12:59 pm
My requirements is to copy the whole data.
November 29, 2011 at 8:12 am
Then I suggest you get/create some kind of mirroring or replication process to keep a complete copy on a local server and then do your ETL from there.
November 29, 2011 at 8:50 am
The problems is making the copy on my local server which is too slow.
November 29, 2011 at 8:58 am
Bcp out, zip, copy, unzip, bcp in??
November 29, 2011 at 9:09 am
Let me explain better my requirements.
There are 2 servers. A remote one which has Oracle as dbms and the local server which has SQL Server 2005 as DBMS. These 2 servers are connected through a VPN(Symmetric 2Mbits connection) in order to exchange the data securely.
My requirements is to copy the whole table from oracle to sql server.
I have no control over the oracle server so i can't do extration of the data save somewhere zip it or other stuff.
I can only read this table using the oracle credentials that the DBA gave to me.
November 29, 2011 at 9:11 am
Then you can only wait.
Or increase the bandwith.
There's no magic button here.
You have a traffic bottle neck.
Increase the neck or reduce traffic.
November 29, 2011 at 9:19 am
Ninja's_RGR'us (11/29/2011)
Then you can only wait.Or increase the bandwith.
There's no magic button here.
You have a traffic bottle neck.
Increase the neck or reduce traffic.
No i have not a traffic bottleneck! I have done the neccessary verification and the connections is used less than 10% of the 2Mbits.
The problem, i think it could be on oledb drivers or something else...
November 29, 2011 at 9:22 am
How long does it take if you do select top 1 or top 100?
Network debuging is way outside my competence. What did the network admin say?
November 29, 2011 at 9:43 am
Ninja's_RGR'us (11/29/2011)
How long does it take if you do select top 1 or top 100?Network debuging is way outside my competence. What did the network admin say?
Doing a Top 1 in P/SQL which is something like the following
"SELECT *
FROM mytablename
WHERE rownum <= 1
ORDER BY rownum"
it takes some milliseconds, so very fast...
Doing a Top 100 it takes 9-10 sec...
During the execution of the query the newtork is used exactly 75-80kbits, less than 5% of the whole bandwith.
The net admin already told me that there are no problem on the connections and its stable and very good.
Reading on the net i have found that other people has the same issues with oledb and oracle databases...
November 29, 2011 at 9:45 am
We're completely outside my expertise here... going to have to bow out of this one!
November 29, 2011 at 9:53 am
anyway thanks
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply