Performance issue querying Linked Servers in SQL Server

When querying a linked server in SQL server there are basically two different ways to do it.

Four Part Naming

There is the four part naming method which calls the object directly while tacking on the linked server name to the table you are querying.

And this method is nice and simple, but it works by passing the full table over the network to the SQL server that initiated the query and then performing operations on the data at that point. So if there is a where clause or join with another table in the linked server then the server copies both tables and performs the where or join at the initiating SQL Server. This does not take advantage of any indexes or foreign keys on the linked server and can lead to transferring a lot more data over the network than what is necessary.

OpenQuery

And the other method is to use openquery:

The openquery method isn’t as elegant as the four part naming method, but it does have the advantage of doing any where’s or joins on the linked server and not on the SQL Server initiating the call. Since the query is run on the linked server it can take advantage of any indexes or foreign keys on the linked server and only sends back the information from the completed query instead of sending all of the tables.

So if your doing queries across linked servers in SQL server make sure that you understand the difference and use the correct method.

About the Author

Mike Griffith

Twitter Google+

A veteran programmer with over 20 years of experience in web development. If you have any questions please feel free to make a comment or send an email to mwgriffith@gmail.com.

Mike GriffithPerformance issue querying Linked Servers in SQL Server