Improving Linked Server Performance With OPENQUERY

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.

Linked Server & 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

-- Normal INSERT statement
INSERT INTO Table1(Column1, Column2) VALUES('Value1', 'Value2')
-- Statement against linked server
INSERT INTO MSACCESS...Table1(Column1, Column2) VALUES('Value1', 'Value2')

Note the usage of server name MSACCESS and three dots in the INSERT statement.

Performance Issues

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.

OPENQUERY to The Rescue

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.

-- Normal INSERT against Linked Server
INSERT INTO MSACCESS...Table1(Column1, Column2) VALUES('Value1', 'Value2')
-- OPENQUERY INSERT against Linked Server
INSERT OPENQUERY (MSACCESS, 'SELECT Value1, Value2 FROM Table1') VALUES ('Value1', 'Value2')

Performance Measurement

  • 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.

var watch = Stopwatch.StartNew();
using(var cmd = new SqlCommand(sql, connection))
{
cmd.CommandTimeout = commandTimeout;
cmd.ExecuteNonQuery();
}
watch.Stop();
Log.Write("Executing statement took {0} ms", watch.ElapsedMilliseconds)

DELETE Statement

I wasn’t expecting DELETE statements to be much faster so I was surprised to see almost 25% improvement in the execution time.

DELETE statement execution time. Lower is better.

INSERT Statement

The INSERT statements were executed almost 30% (28.5) faster with OPENQUERY.

INSERT statement execution time. Lower is better.

UPDATE Statements

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.

UPDATE statement execution time. Lower is better.

Another UPDATE statement with 81% decrease in execution time.

UPDATE statement execution time. Lower is better.

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.

UPDATE statement execution time. Lower is better.

Notes and Conclusion

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.