MS SQL Server - SQL Server linked server performance under high-volume scenarios

Author

SQL Server linked server performance under high-volume scenarios

Joe K.

2006-10-24, 6:30 pm

How well does SQL Server 2000/2005 linked server to an Oracle database work
with queries that extract high-volumes of data?

Is there other alternative ways to extract the data?

What are some of the pit falls and problems.

Thank so much.

alingsjtu@gmail.com

2006-10-24, 6:30 pm

I have the similar question with you, but my link target server is DB2.
The target DB2 sever has about several million records that need to
extract.

At first I just directly used the Linked DB2 Server table with 4 part
naming method in local T-SQL statements, but after some practice, I
found the perormance very low and even got file limit reached error
message (I dont' t know it wether it came from DB2 or SQLServer, but I
think it should be SQLServer, cause SQL Server fetched all Linked
Server data to local to exectue the distributed query).

So I changed code to use OPENQUERY which by pass the local SQLServer
and pass the query statement to linked server, and the query executed
on linked server, only needed data returned to local SQLServer.

This method should be fine. But I dynamically call OPERNQUERY in
dynamic string in a cursor loop, which lead to problem of DB2 SQL1040N
Error which said The maximum number of applications is already
connected to the database. Maybe every dynamic OPENQUERY statement not
release their connecion to DB2. No idea about it yet now.

See my post about this problem here:
http://groups.google.com/group/comp...> 60047cb23d535

Good Luck!

Ling, XIao-li

Joe K. wrote:
> How well does SQL Server 2000/2005 linked server to an Oracle database work
> with queries that extract high-volumes of data?
>
> Is there other alternative ways to extract the data?
>
> What are some of the pit falls and problems.
>
> Thank so much.

Sue Hoegemeier

2006-10-24, 6:31 pm

Usually works okay - you would want to look at using
openquery and passing the SQL Statement to Oracle to reduce
the load, data required on the SQL Server end. Drivers can
sometimes be problematic - it depends on what Oracle client
you have. Sometimes you have to play around with using the
MS provider vs the Oracle provider.
Alternatives would depend on what you are doing. One
possibility would be dumping out to a text file and
importing from there - what tools depends on which are
systems are sources and which are destinations and if these
are queries or just dumping out tables, whatever.

-Sue

On Thu, 21 Sep 2006 20:34:01 -0700, Joe K. <Joe
K.@discussions.microsoft.com> wrote:

>
>How well does SQL Server 2000/2005 linked server to an Oracle database work
>with queries that extract high-volumes of data?
>
>Is there other alternative ways to extract the data?
>
>What are some of the pit falls and problems.
>
>Thank so much.

Sponsored Links