openquery, openrowset or neither?

  • Within my SQL2005 Instance (32BIT), I successfully created and queried to an Oracle database.

    From a performance perspective, should the queries be written with OPENQUERY, OPENROWSET or how?

    ...thanks

  • rew-370421 (9/23/2010)


    Within my SQL2005 Instance (32BIT), I successfully created and queried to an Oracle database.

    From a performance perspective, should the queries be written with OPENQUERY, OPENROWSET or how?

    Both OPENQUERY and OPENROWSET will force the execution of the query in the remote server. This is not what happens when you use Four-Part Name, in this case query is executed in the local server - which may be a pain in terms of performance if doing join operations in large tables.

    OPENROWSET syntax forces you to hardcode your credentials e.g. username/password as part of the query therefore I would be concerned about both security and maintenance (passwords do change over time).

    If OPENQUERY does it for you, I'll stay with it.

    _____________________________________
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at Amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
  • Thanks, Paul! Your explanation helps.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply