SQL Server Performance

OpenQuery Vs four-part names(linked server)

Discussion in 'General Developer Questions' started by Balasundaram, Apr 19, 2005.

  1. Balasundaram New Member

    Hi,

    I just want to know, is there any difference,advantages or disadvantages in using openquery & four-part names(linked server)?

    I generally use four-part names to combine multiple tables. so just want to know is there any perf gain if i use openquery.

    Sample Queries:
    select * FROM OPENQUERY(ServerName, ''SELECT a.code ....

    select b.code from ServerName.Database.dbo.tableName a



    Thanks,
    Balasundaram
  2. Argyle New Member

    There can be a major difference.

    A standard four-part linked server query will often return the data row by row with a slow cursor. OPENQUERY will generate a pass-through query and return the data in a single batch but will generally use more memory. For simple selects I would use OPENQUERY. For complex joins the four-part syntax with correct indexes is probably the way to go.

    http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=1944
    http://www.sql-server-performance.com/forum/topic.asp?ARCHIVE=true&TOPIC_ID=118
    http://www.sql-server-performance.com/forum/topic.asp?ARCHIVE=true&TOPIC_ID=449
  3. Balasundaram New Member

    Thanks Argyle,

    This is really useful information.

  4. regu_r New Member

    Hi Argyle,

    I am looking out for difference by running below query using Openquery.

    select a.* from openquery(rcip_shadow,'select count(bseg_id) from CI_BSEG_SQ ') a
    where a.bseg_id = '598889689650'

    select a.* from openquery(rcip_shadow,'select count(bseg_id) from CI_BSEG_SQ where a.bseg_id = ''598889689650''') a

    ** The table contains 75000000 records (DW database in oracle)

    First one is very slow. and second one runs faster and gives me within a second. I have also

    My problem is, i need to pass nearly about 30000 values to the openquey, so i am using while loop now. I am trying to avoid the while loop as it runs for # of times.

    What is way to improve the query performance using four part linked query. Please note there are couple of other tables being involved in this query.

    I am looking forward to your reply. Thanks

Share This Page