SQL Server – Best Practices for Data Views with Linked Server

join;linked-serversql serverview

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.

Best Answer

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:

Use a temporary or a work table on Server A named #DataTable_ServerB_local to store the results from Server B.
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.
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.
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:

Limit the number of rows passing between the servers.
Avoid as many joins across the linked servers as possible.
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.

Related Solutions

SQL Server Linked Server – Query Performance Tips

Your problem begins and ends with statistics and estimates. I have reproduced your situation on my servers and found some interesting hints, and a workaround solution.

First things first, let's take a look at your execution plan:
When a view is used we can see that a filter is applied after the Remote Query is executed, while without the view there was no filter applied at all. The truth is that the filter was applied inside the Remote Query, at the remote server, before retrieving the data over the network.
Well, obviously applying the filter at the remote server and thus retrieving less data is a better option, and obviously that only happens when not using a view.

So... what is so intersting...?

Surprisingly, when I changed the filter from cognome = 'test' to cognome = N'test' (unicode representation of the string) the view used the same execution plan as the first query did.
I guess the reason is that somehow when using the view SQL Server estimated that there will be a small number of rows returning from the (remote) query, and that a local filtering will be cheaper, but when SQL Server had to implicit convert NVARCHAR to VARCHAR, statistics could no longer be used and the decision to filter locally was not taken.
I have looked for the statistics locally, but the view had no statistics, so my guess is that the view uses the remote statistics in a way that ad-hoc query does not, and than takes the wrong decision.

OK, so what solves the problem?

I stated earlier that there is a workaround (at least until someone comes up with a better solution), and no, I don't mean using unicode for your strings.
I wanted to give an answer first, I still have to find why, but when using an Inline Function SQL Server behaves exactly the same as with the query (without view), so replacing the view with the function will give the same result, in a simple query, and with good peformance (at least in my environment).

My code suggestion for you is:

CREATE FUNCTION fn_anagrafiche2()
RETURNS table
AS
RETURN 
(
    SELECT * 
    FROM dolph2.agendasdn.dbo.vistaanagraficagrp
    UNION
    SELECT * 
    FROM dolph2.acampanet.dbo.vistaanagraficagrp
    UNION
    SELECT * 
    FROM municipio2.dbnet.dbo.vistaanagraficagrp
)
GO

The query will then be:

SELECT * 
FROM fn_anagrafiche2()
WHERE cognome = 'prova'

This works on my servers, but of course test it first.
Note: I do not recommend using SELECT * at all, as it is prone to future errors, I simply used it because it was in your question and there was no need for me to change that when I can add this remark instead :)

SQL Server 2008 – Using Linked Server with OPENQUERY in Database Project

I've managed to get it working:

I created a new database project master. In there I created a folder Server Object and a file LinkedServer.sql. In the SQL file i added the linked server:

GO
EXECUTE sp_addlinkedserver @server = N'LinkedServer', @srvproduct = N'sqlserver', @provider = N'SQLNCLI', @datasrc = N'LinkedServer.domain';

After adding the database Project master to my solution and referencing it in my original databse project, I was able to build the project using the initial syntax;

CREATE VIEW dbo.vwStatus
AS
SELECT     StatusID, StatusName
FROM       OPENQUERY(LinkedServer, 'SELECT * FROM [DB].[dbo].tbStatus') AS derivedtbl_1

Best Answer

Related Solutions

SQL Server Linked Server – Query Performance Tips

SQL Server 2008 – Using Linked Server with OPENQUERY in Database Project

Related Question