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
Well, openquery/rowset/datasource only takes literal string (i.e. you cannot user string variable). The new Execute syntax allows you do the same pass-through as with openquery but also allows the use of variable. If you're doing lots of cross server invocation, this is certainly a major benefit.
As for perf implication, there wouldn't be much of a difference between both methods. I would bet the Exec would actually be better.
sql