SQL sever--linked Servers

When using a linked server (Linked Servers), the most expensive cost is the transmission of large amounts of data between network bandwidth. It is important to write the correct code on the correct server, because each error can cause a very expensive cost to the network bandwidth. Here are a few common errors when using a linked server (Linked Servers):

1: Use push method instead of pull method to fetch number

Surprisingly, pushing data using a linked server is much slower than fetching data. Linchi Shea wrote a very good blog to discuss this.

Linchi Shea uses OpenQuery to illustrate the difference between the two, but this also happens in the SQL statement using the linked server (this is not a good translation, in fact, the use of linked server in the query needs to use LinkServer.DatabaseName.dbo.TableName)

2: Using Join

When you cross-server queries, SQL Server needs to transfer data from one server to another in order to perform a join operation between the data sets between the two servers. If the transmitted data is a very large table, this process can be very painful. Typically, data is transferred from the remote server to the local server. To prevent large amounts of data from being transferred between servers, you can use a remote stored procedure to retrieve only the relevant data by filtering the data in the query condition, in case you need to associate a data set between two different servers using the inner join, and the data volume on the local surface is much smaller than that of the remote server. You can use remote JOIN HINT to improve performance by transferring data from the local server to the remote server

3: Using union

As with join operations, uniion two datasets between different servers must cause data to be transferred from a remote server to the local server. Even if you perform a remote query merge (UNION) with two datasets from the same remote server, the two datasets will be delivered to the local server first, and then the Union two datasets can be stopped by a remote stored procedure, function or view, by first union the database.

4: Writing too complex query statements

The optimizer cannot always understand what you need to do, especially if you use a linked server (Linked server) in your SQL statement, for example, I have encountered a SQL statement similar to the following, executed for 10 minutes

   1:select *

   2:from localtable

   3:where Somecolumn <

   4: (SELECT COUNT (*)

   5: From  RemoteServer.SomeDB.dbo.SomeTable

   6:  WHERE somecolumn > 100)

我像这样修改了查询语句

   1:declare @Count INT

   2:select @Count = Count (*)

   3:from RemoteServer.SomeDB.dbo.SomeTable

   4:where somecolumn > 100

   5:  

   6:select *

   7:from localtable

   8:where Somecolumn < @Count

 

After the SQL is rewritten, the query statement runs for only one second to query the results, keeping the SQL script simple.

5: Use a linked server when the database is in the same instance (Linked server)

The performance loss of this scenario may not be as obvious as in other scenarios, but this is slower than using the database prefix (Database.dbo.TableName)

If you want to differentiate between these two scenarios, you can test the performance of both methods in the test database, and then decide whether the performance boost is worth modifying the code in the production environment. In some cases, it will improve performance.

Main issues: 1, Performance issues 2, security issues

SQL sever--linked Servers