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.