Can anyone tell me, if, generally, the performance or the cost of executing a pass-through command on a linked server in SQL Server
2005 would be better using OPENQUERY or the new option with EXECUTE -- whether the two servers are on the same box or not? I haven't

been able to find a comparison between the two?

Have there been any tests of the difference?

What effect on performance is there with 'rpc out' set with sp_serveroption so EXECUTE can be used?

To be more specific I have a development box with SQL Server 2005 and Oracle 9.2.

The new option with EXECUTE would be something like the example in MSDN (Example J.) at:

http://msdn2.microsoft.com/en-us/library/ms188332.aspx


EXEC ( 'SELECT * FROM scott.emp') AT ORACLE;
GO

Not very sure though, just a thought.

You can make both the queries and check out in query analyser for cost of the query and find which is better than the other.

IMO, as long as the control is within the SQl server the performance matter, but processing query and fetching data from the remote server is all in the hands of remote server and one cannot have control on its performance.

Hope that helps.

_________________________
- Vijay G
Strive for Perfection