OPENQUERY Performance Inconsistencies

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

SQL Server Development (2000)

OPENQUERY Performance Inconsistencies

Author

Topic

dan.franks@gmail.com
Starting Member

2 Posts

Posted - 2005-03-25 : 16:13:29

Hello,

I am trying to do a cross server join. The remote table has too much data to bring across and hash between the local table. Because of this I decided to use openquery to execute the query from the local server but on the remote server.

The problem is that running the query through openquery, it performs just as though it had been executed locally. When I execute the query on the remote server it performs great. If I run that same query locally or using openquery on the remote server it performs horribly.

Below are the three queries:

Running Local Server-
Select
top 5 tbl_Account.*
from
phxmlistupd.accountdb.dbo.tbl_account tbl_Account left join Account Account
on tbl_Account.system = Account.system
and tbl_Account.vaxacct = Account.vaxacct
and tbl_Account.accountdate = Account.accountdate
where
Account.AcctPayment IS NULL

Running Remote Server-
Select
top 5 tbl_Account.*
from
accountdb.dbo.tbl_account tbl_Account left join phxaccount.chargeback.dbo.Account Account
on tbl_Account.system = Account.system
and tbl_Account.vaxacct = Account.vaxacct
and tbl_Account.accountdate = Account.accountdate
where
Account.AcctPayment IS NULL

Running Local Server Using Openquery-
SELECT *
FROM OPENQUERY(phxmlistupd, '
Select
top 5 AC1.*
from
accountdb.dbo.tbl_account AC1 left join phxaccount.chargeback.dbo.Account AC2
on Ac1.system = ac2.system
and ac1.vaxacct = ac2.vaxacct
and ac1.accountdate = ac2.accountdate
where
ac2.AcctPayment IS NULL
')

Any ideas?

Thanks in advance,
Dan

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2005-03-27 : 18:04:04

Indeed surprising!
What if to wrap it into a view (on the remote server) and then
open_query against this view? Just for to see any difference.

dan.franks@gmail.com
Starting Member

2 Posts

Posted - 2005-03-28 : 15:27:37

I am starting to come to the comclusion this is server specific behavior. The exact same scenario works fine from other servers. I have noticed a couple of active directory permissions issues on this box, so I suppose I will just reinstall rather than trying to spend any more time troubleshooting.

Thanks,
Dan

p.s. The view performed exactly the same as the others. Never returned anything.

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-03-28 : 17:04:26

Also check the "use remte collection" property of the linked server.

rockmoose

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources