Linked Server enables SQL Server Database Engine to execute commands against OLE DB data sources outside of the instance of SQL Server. In the following figure you can see that application can execute SQL statements against MS Access database through SQL Server.
Using Linked Server is something you might have to use in order to integrate with legacy systems such as MS Access database. Usually you can just connect to database by using address and instance name. This is not the case with MS Access. The connection is made to a physical file (e.g.
C:\Databases\db.accdb). This means creating connection through the network would require mapping of network drive and opening the file. There are quite many things that can go wrong and you end up with corrupted database.
Using Linked Server means that you need to specify the server name in the SQL
Note the usage of server name MSACCESS and three dots in the INSERT statement.
Linked Server usage can be extremely slow because SQL Server might try to perform part of the logic locally. E.g. if you have
WHERE statement in the query SQL Server might just send
SELECT * FROM to the remote server and filter the results locally. Also single query will cause at least two connections to remote server: one to get the statistics for query analysis and another one to get the actual data. Depending on the table some queries might take several seconds to complete.
If you are stuck with Linked Servers one way to improve things is to start using OPENQUERY. The difference is that when using OPENQUERY SQL Server doesn’t try to process the query locally. It will just send it to the remote server as is. You can think of OPENQUERY as a pass-through query.
The syntax of the query is a bit different.
- The execution times presented are averages
- Times are in milliseconds but the absolute values should not be looked at because there are many factors that affect those.
The performance has been measured with Stopwatch.
I wasn’t expecting DELETE statements to be much faster so I was surprised to see almost 25% improvement in the execution time.
The INSERT statements were executed almost 30% (28.5) faster with OPENQUERY.
The slowest statements were UPDATE statements so I had high hopes for the performance gains. The first UPDATE statement is 35% faster when OPENQUERY is used.
Another UPDATE statement with 81% decrease in execution time.
Finally, the slowest UPDATE statement. This statement took on average over 8 seconds to execute. With OPENQUERY the execution time was cut down to 100 milliseconds! That is 82 times faster and almost 99% reduction in execution time.
In my limited experience you can expect 25-35% decrease in execution times in normal scenarios. If you have statements that take several seconds to complete you might get really good results with OPENQUERY. Unfortunately, it is difficult to know how much of an improvement you get. Start by measuring the statements and convert those that are the slowest.
If you are planning to move to OPENQUERY I highly recommend having configuration option to switch back to normal SQL if needed. The OPENQUERY is pickier about the SQL it accepts. Being able to switch back to slower but working solution will save your ass.