Best Practice For Data Views with Linked Server in SQL Server

Question

In SQL Server, I have to design a View that I can map on a DBML file in order to dynamically retrieve information from the database with multiple conditions to filter.

The problem is that I have to combine the information from multiple tables, and some of them are on another server.

Currently, I have a view with most of the information, composed by multiple joins of several tables on a server (A), and another view in another server (B) that joins that view on server A with several other tables from the same server B.

So the flux of data as I understand it goes like this: Final View on Server B = Server A View JOIN Server B Tables

Here is the template for the Server A view:

ALTER VIEW [dbo].[DataView_ServerA]
AS
SELECT 
 ss.Field, 
 ss.Field, 
 ss.Field, 
 ss.Field, 
 (CASE WHEN ss.Field is null then 'Field not Assigned' else ss.Field end) as Field,
 mfl.Field as Field,
 St.Field,
 (CASE WHEN ss.Field=0 THEN CAST(0 AS BIT)
 WHEN ss.Field=1 THEN CAST(1 AS BIT)
 else ss.Field end) as Field,
 fh.Field AS Field, 
 ssABC.Field AS Field,
 (CASE WHEN ssABCAB.Field IS NULL OR ssABC.Field IS NOT NULL THEN CAST(0 AS BIT) ELSE CAST(1 AS BIT) END) AS Field,
 (select (CASE WHEN count(Field)>=1 THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT) END) as treatedByAB from table where Field='Example' and id in (select Field from [table] where Field= fh.Field)) as Field
FROM 
 dbo.table AS ss WITH (nolock) 
LEFT OUTER JOIN
 dbo.table as St WITH (nolock) on ss.Field!=St.Field
LEFT OUTER JOIN
 dbo.table AS ssABC WITH (nolock) ON ss.Field = ssABC.Field AND ssABC.Field = St.Field 
LEFT OUTER JOIN
 dbo.table AS fh WITH (nolock) ON fh.Field= ssABC.Field
LEFT OUTER JOIN
 dbo.table AS ssABCAB WITH (nolock) ON ss.Field= ssABCAB.FieldAND ssABCAB.Station = St.Station
LEFT OUTER JOIN
 dbo.table mfl ON ss.Field=mfl.Field and ss.Field=mfl.Fieldand ss.Field=mfl.Field and mfL.Field='Example'

And the template for the Final View on Server B is:

ALTER view [dbo].[FinalDataView_ServerB] as
select ABCraw.*, 
Tch.Field,
cat.Field,
f.Field
FROM [LinkedServer].[Database].[dbo].DataView_ServerA as raw
LEFT OUTER JOIN
 dbo.table AS f ON f.Field=raw.Field
LEFT OUTER JOIN
 dbo.table AS act ON f.Field = act.Field
LEFT OUTER JOIN
 dbo.table AS cat ON f.Field = cat.Field
LEFT OUTER JOIN
 dbo.table AS Tch ON f.Field= Tch.Field

But the time that it takes to retrieve registers is normally above 7 minutes, with no distinction to the quantity.

I you could provide me of some advice of how to rewrite the query in order to optimize it or configure the servers to load the data more quickly, I would appreciate it a lot.

Thanks in advance.

Answer 1

In my experience, the round-robin joins on both legs of the query certainly make for an expensive plan. It depends on a few issues, but consider using these options to help you:

1. Use a temporary or a work table on Server A named #DataTable_ServerB_local to store the results from Server B.
2. If possible create a stored procedure Prepare_Datatable_ServerB_Data on Server B that you can call remotely to get the Server B data. This may join back to Server A to get some data.
3. UseOPENQUERY to execute the Remote Stored Procedure Prepare_Datatable_ServerB_Data to insert its data into Server A's #DataTable_ServerB_local. The code internal to the stored procedure would join back to the local server to get what I suspect are the relatively few rows that it needs.
4. Join DataView_ServerA with #DataTable_ServerB_local which are now both on Server A to provide the final result.

This strategy assumes that Server B joining back to Server A is the smaller set of joins. (If this is not the case then you will need to switch directions with this strategy.)

The whole point of this approach is to:

1. Limit the number of rows passing between the servers.
2. Avoid as many joins across the linked servers as possible.
3. To the extent possible (e.g. the Prepare_Datatable_ServerB_Data procedure) process data on the server where the data is stored.

Big joins across a linked server can be very expensive in terms of time and the optimizer may not be able to make good decisions.

• BlueLightning: Thanks! It seems like a reasonable answer. I'll try to modify the flow of the final view according to your suggestions.