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 Administration (2000)
 Performance diff between linked / adhoc queries

Author  Topic 

rcurrey
Starting Member

30 Posts

Posted - 2007-02-27 : 12:45:54
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


Thanks,
Rich
   

- Advertisement -