SQL Server Performance

Difference between OPENQUERY and four part name queries on a linked server

Discussion in 'Performance Tuning for DBAs' started by chetanjain04, Aug 30, 2007.

  1. chetanjain04 Member

    Hi,
    I want to understand the difference from point of performance between a query executed through linked server using the OPENQUERY function and using four part name.
    My query is structure as follows:
    insert into tablename
    select * from openquery( linked_server, 'A very complex select statement with lot of outer join and subqueries.' )
    Basically, I want to understand how the query is executed and it will consume resources of which server.
    Regards,
    Chetan
  2. Adriaan New Member

    I think that with OPENQUERY you have a better chance of the other server doing all of the work on that part of the query. With a four-part name, your current instance may attempt to retrieve loads of data and do more of the processing locally, which may slow down the query.
  3. satya Moderator

    For large remote objects, the solution to performance problems is the OPENQUERY function.
  4. chetanjain04 Member

    Yes, openquery is much faster.....only one problem....is the query string is limited to 8k only. Hence, I had to use DTS.
    Regards,
    Chetan
  5. dineshasanka Moderator

    I had issue with sysbase when dealing with directly linked servers. Problem was solved with open query. Later found that issue was with the sybase OLEDB drive
  6. satya Moderator

    Thats a good point, whenever you are using heterogenous drivers to access data better to check for vendor's website for latest driver.
  7. Adriaan New Member

    [quote user="chetanjain04"]Yes, openquery is much faster.....only one problem....is the query string is limited to 8k only. Hence, I had to use DTS.[/quote]If you can create a stored procedure on the remote server that returns the data you need, then you are much less likely to ever hit the 8K limit.
    Of course for a one-time transfer of data, DTS will do fine.

Share This Page