SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
Register Now and get your question answered!
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 Performance diff between linked / adhoc queries
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Starting Member

30 Posts

Posted - 02/27/2007 :  12:45:54  Show Profile  Reply with Quote
I have an environment with several servers in a central location and approx 56 remote servers. We are currently using OPENROWSET for our queries, but I am curious if there are any performance benefits of one vs the other. When I use an OPENQUERY against a linked or an adhoc OPENROWSET, they produce identical execution plans. If I use a four-part name for the same query, it is approximately 10% of the estimated resources.

Can someone help me understand what's happening here?

Here are the 3 queries:

select * from openquery(myserver,'select * from TestDB.dbo.testtable')

select * from openrowset('SQLOLEDB','Server=myserver;uid=myid;pwd=mypass;',
'select * from TestDB.dbo.testtable')

select * from myserver.TestDB.dbo.testtable

  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000