Performance effect of Synonyms over a linked server in SQL Server
linked-server, performance, sql-server, sql-server-2008-r2, synonym
Solution
I would dump the data without the order by into a temp table on local server. Then I would select from the temp table with the order by. Order by is almost always the killer.
Problem
On localserver (a SQL Server 2008 R2), I have a synonym called `syn_view1` pointing to the linked server `remoteserver.remotedb.dbo.view1` This SLOW query takes 20 seconds to run. ``` select e.column1, e.column2 from syn_view1 e where e.column3 = 'xxx' and e.column4 = 'yyy' order by e.column1 ``` This FAST query takes 1 second to run. ``` select e.column1, e.column2 from remoteserver.remotedb.dbo.view1 e where e.column3 = 'xxx' and e.column4 = 'yyy' order by e.column1 ``` The only difference in the two queries is really the presence of the synonym. Obviously, the synonym has an impact on the performance of the query. The execution plan for the SLOW query is : ``` Plan Cost % Subtree cost 4 SELECT I/O cost: 0.000000 CPU cost: 0.000000 Executes: 0 Cost: 0.000000 0.00 3.3521 3 Filter I/O cost: 0.000000 CPU cost: 0.008800 Executes: 1 Cost: 0.008800 0.26 3.3521 2 Compute Scalar I/O cost: 0.000000 CPU cost: 3.343333 Executes: 1 Cost: 0.000000 0.00 3.3433 1 Remote Query I/O cost: 0.000000 CPU cost: 3.343333 Executes: 1 Cost: 3.343333 99.74 3.3433 ``` And for the FAST query: ``` Plan Cost % Subtree cost 3 SELECT I/O cost: 0.000000 CPU cost: 0.000000 Executes: 0 Cost: 0.000000 0.00 0.1974 2 Compute Scalar I/O cost: 0.000000 CPU cost: 0.197447 Executes: 1 Cost: 0.000000 0.00 0.1974 1 Remote Query I/O cost: 0.000000 CPU cost: 0.197447 Executes: 1 Cost: 0.197447 100.00 0.1974 ``` My understanding is that in the SLOW query, the server fetches all the data from the remote server, then applies the filter (though without index) whereas in the FAST query the server fetches the filtered data from the remote server, thus using the remote indexes. Is there any way to use the synonym while being fast? Maybe a setup of the linked server ? the local database server? Thanks for the help!