adding a linked server - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

adding a linked server

Author

Topic

mdhingra01
Posting Yak Master

179 Posts

Posted - 2004-11-03 : 10:27:42

Trying to add a DB2 Linked server. Currently using "IBM OLE DB Provider for DB2 Servers". I am able to connect sucessfully, but when I try running a simple select statement, it runs very very slow and the results take a lot of time to receive.

Thanks

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-11-03 : 15:52:33

how are they connected and how far away are they? do you use vpn or simple lan?

Go with the flow & have fun! Else fight the flow

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-11-03 : 16:40:48

show us your select statement, also. remember that joins between two datasources can be extremely inefficient. Pass as much criteria into a subquery for just the DB2 database that can be sent to the DB2 server. And return as few rows as possible in your DB2-only query -- GROUP as much as possible.

otherwise, SQL Server must retreive EVERY SINGLE ROW in the table(s) in DB2 you are joining to, and do all the work on it's own.

This is very similiar to using Linked tables in MS Access.

for example, always try to rewrite stuff like this:


select 
  cust.ID, cust.name, sum(sales)
from 
  cust   <-- local SQL table
inner join 
  DB2...Sales  <-- linked table (non-SQL Server)
on
 cust.ID = sales.CustID 
group by 
  cust.ID, cust.name

as:


select 
  cust.ID, cust.name, DB2.TotalSales
from 
  cust 
inner join 
  [b](select CustID, sum(sales) as TotalSales from DB2...Sales group by CustID) DB2
on
 cust.ID = DB2.CustID

it can make a huge difference.

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources