//
you're reading...
T-SQL

Distributed Queries VS OPENQUERY

First some definitions:

Distributed Query : Linked server four part queries are also called distributed queries. Using distributed queries,  you can refer tables on different data sources/servers in a single query. Query optimizer creates an execution plan by looking at the query nomenclature and breaks it into remote and local queries. Local queries are executed locally and data for remote queries are collected from the remote servers, scrubbed locally, combined together and presented to end user as single record set.

OpenQuery\Exec(sql) at linkedserver : Executes the specified pass-through query on the specified linked server. SQL Server sends pass-through queries as un-interpreted query strings to an OLE DB data source . That is, SQL won’t apply any kind of logic on the query and won’t try to estimate what that query would do, it would simply pass the specified query as it is to the target linked server. Open queries are useful when you are not referencing multiple servers in one query. It’s generally fast as SQL does not  break it into multiple operations and does not perform any local action on the output received.

I recently was running some performance comparisons between a distributed query, OPENQUERY and Exec(SQL) at linkedserver. I found the OPENQUERY and the Exec(Sql) at methods to be comparable but the distributed query was many times slower.

So which is faster Distributed Query or OPENQUERY\Exec query and why?

The answer is, generally, OPENQUERY\Exec query would be faster. A lot depends on the OLEDB drivers used. I was connecting to an remote Sysbase Advantage database.

One of the main differences I found was that the distributed query collects all the data from the remote server and then applies filters to the data on the client. Therefore in the case of distributed query even though you have a WHERE clause in your query, you might notice that when the query is sent to retrieve the rows of a table in the remote server, SQL Server will just send a SELECT * FROM the remote table and then locally it filters out the necessary data after applying the predicates. Whereas the OPENQUERY, SQL Server sends the complete query to remote server and the resources of the remote server are spent in processing the query like parsing the SQL statements, generating a plan, filtering the rows as per predicates, etc. Then the final result set is sent to originating server which then just display what was received.

Advertisements

About ldgaller

Accomplished Data Warehouse Architect, DBA and Software Architect with over 15 years of professional experience and demonstrated success designing and implementing solutions that improve business functionality and productivity. Highly diverse technical background with proven ability to design, develop and implement technology on an enterprise level. I approach all projects with passion, diligence, integrity, and exceptional aptitude.

Discussion

No comments yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: