1

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.

0

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.

1
  • Thanks! It seems like a reasonable answer. I'll try to modify the flow of the final view according to your suggestions. Jun 30 '14 at 12:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.