dts package question

[aolb]

I have 2 dts packages that use a query to get just under 16,000 records from an ORACLE view that uses 5 tables and has just under 100,000 results


I have an ORACLE view that uses 5 tables and returns just under 100,000 results.
I have 2 dts packages that use the same query that returns just under 16,000 results.
These dts packages are located on different networks and have to go through different firewalls to get to the same source ORACLE data.
One package takes 25 minutes to executes (which I thought excessive), the other takes 3 hours 50 minutes!

I remember from my MS Access days that if you ran a query against linked data then MS Access brought all the data across from the data source to the machine the query was being executed on. I was wondering if the same thing was happening here?

Would it be better to get a View created on the ORACLE machine to select the data I wanted?

 

[Catadmin]

Actually, if your databases are on different networks and going through different firewalls, your problem is probably network related. Find out the IP address of both databases and run TraceRT from the command prompt. See how long the test packets take to get from there to you. See how many hops each trip takes.

I'm betting the longer one has more hops and takes longer for the test packets to get through.

BTW, TraceRT only allows for 30 hops, so if the route is too long and TraceRT quits giving you this message, grab a network admin, put a sniffer on the network and run a test on the DTS packages to see what the sniffer comes up with.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"