OpenQuery vs Four-Part Names

In this article, we’ll cover the uses of OpenQuery and Four-Part Names. These methods are created to temporarily connect and fetch data when data is needed from a remote server.

The OPENQUERY function is a temporary way to access a remote server’s data. If you are querying the remote server frequently, you should use the main server instead of using this function or transfer the data to DWH with ETL processes.

The OpenQuery syntax is as follows;

  SELECT * FROM OPENQUERY (linkedServer, ‘Query’)

The query you wrote with OpenQuery is completely run on the remote server, but when you write it with a four-part names query, a separate request is sent to the LinkedServer for each statement. Below you can see the Query Execution Plan of our OPENQUERY example;

The Four-Part Names Query syntax is as follows;

select col1, col2, col3
from serverName.databaseName.ownerName.tableName a
inner join serverName.databaseName.schemaName.tableName b ON a.col1 = b.col1
inner join serverName.databaseName.schemaName.tableName c ON a.col1 = c.col1 
where col4 = something

There are 3 different linked server usage in the query you have seen above. SQL Server divides this query into three parts and runs it as three separate queries on the relevant server. This causes it to be slower than OpenQuery in terms of performance. When we look at the Query Execution Plan, the result is as follows;

If the SQL server we connect to Linked Server and the server we run the query on have the same character set, you can gain a serious speedup by making the SP_SERVEROPTION “collation compatible” feature ‘true.’ However, if this feature is not ‘true,’ it tries to fetch the entire table from LinkedServer to apply the WHERE clause.

In the sample study we have done, the queries that took 8-10 seconds on the main servers did not return any results, although they worked for hours with the four-part names method.