SQL Server Performance Forum – Threads Archive

OPENQUERY vs EXECUTE on a linked server

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

IMHO OPENQUERY is better in thsi case as it performs all the query processing on the relevant data source server before pulling it across the wire to your local SQLServer machine making the operation much faster. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

Thank you very much for your prompt answer. By the way, what does IMHO mean?

quote:Originally posted by satya IMHO OPENQUERY is better in thsi case as it performs all the query processing on the relevant data source server before pulling it across the wire to your local SQLServer machine making the operation much faster. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

[<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] In My Humble Opinion. Satya SKJ Microsoft SQL Server MVP Writer, Contributing Editor & Moderator http://www.SQL-Server-Performance.Com</a> <center>This posting is provided AS IS with no rights for the sake of knowledge sharing. <hr noshade size="1">Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.</center>

Just as I posted the thank you, I thought of something. The SELECT string I’m actually sending to Oracle uses ‘Minus’ which implies this is being done on the Oracle server and the resultset being sent back to SQL Server, also. So wouldn’t this mean that EXECUTE is as good, unless you mean that there is something additional being done on SQL Server 2005?

You can compare it yourselves by running the query and collecting the counters/stats in this case. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

Satya humble?? LOL Just kidding<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> Michael MCDBA "The statistics on sanity are that one out of every four persons is suffering from some sort of mental illness. Think of your three best friends — if they’re okay, then it’s you!"

]]>