SQL Server Performance

OPENQUERY vs EXECUTE on a linked server

Discussion in 'SQL Server 2005 General Developer Questions' started by aaqqqa, May 22, 2007.

  1. aaqqqa New Member

    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
  2. satya Moderator

    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.
  3. aaqqqa New Member

    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.
  4. satya Moderator

    [<img src='/community/emoticons/emotion-1.gif' alt=':)' />] In My Humble Opinion.<br /><br /><b>Satya SKJ</b><br />Microsoft SQL Server MVP<br />Writer, Contributing Editor & Moderator<br /<a target="_blank" href=http://www.SQL-Server-Performance.Com>http://www.SQL-Server-Performance.Com</a><br /><center><font color="teal"><font size="1">This posting is provided AS IS with no rights for the sake of <i>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.</i></font id="size1"></font id="teal"></center>
  5. aaqqqa New Member

    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?
  6. satya Moderator

    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.
  7. MichaelB Member

    Satya humble?? LOL<br /><br />Just kidding<img src='/community/emoticons/emotion-1.gif' alt=':)' /><br /><br />Michael <br />MCDBA<br /><br />"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!"

Share This Page