November 12, 2010 at 11:03 am
Hey guys,
I will often use openqueries and SSIS to pull data from one of our Oracle servers in my SS2005 database. However, the WAN is a limiting factor. For example, it takes 2 minutes to run a query and another 5 minutes to transfer it over the WAN to SQL Server. Is there anyway I could compress the results of the query to make it transfer faster over the WAN?
Also, this doesn't just have to be with openquery. I am open to any SSIS solution that could speed the transfer of data from Oracle to Sql Server.
Thanks.
November 13, 2010 at 6:23 am
As far as I remember OPENQUERY relies on OLEDB provider.
OLEDB provider can be setup to transfer data in two formats which are: compressed and uncompressed.
Since compressed is the default value most probably OLEDB is doing all it can but I will double check, may be somebody setup OLEDB to uncompressed in this particular case.
What's the latency in between your Oracle and SQL Server hosts?
Is this WAN public-internet based?
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.November 13, 2010 at 8:45 am
Latency is 166ms. The WAN may be public internet, I am not really sure how to confirm this.
How do I check if OLE DB Provider is set to compressed or not?
November 17, 2010 at 11:43 am
Has anyone found out how to see of openquery is already compressing data? I looked around for a while using Google, and couldn't really find anything that helped me out.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply