Sql-server – How to improve performance of SELECT query to same tables in Linked Server

linked-serverperformancequery-performancesql server

I have a SELECT query to a linked server. It looks similar to this

SELECT     @Var1 = (SELECT  COL1
                    FROM    [LinkedServer].[dbo].[TableA]
                    WHERE   <Condition1>),
           @Var2 = (SELECT  COL1
                    FROM    [LinkedServer].[dbo].[TableA]
                    WHERE   <Condition2>),
           @Var3 = (SELECT  COL1
                    FROM    [LinkedServer].[dbo].[TableA]
                    WHERE   <Condition3>),
           ...
FROM       [LinkedServer].[dbo].[TableB]
WHERE      <Condition4>

So there are multiples SELECT statements to the same table in this linked server. Is there a way to improve the performance? Is copying TableA to a temp table in local database server a bad idea? Size of TableA is large.

[UPDATE1]: Updated my query

SELECT      VAR_1, 
            (SELECT  SOL
            FROM     [LinkedServer].[dbo].[TableSol]
            WHERE    COL_1 = [TableA].COL_1
            AND      COL_2 = [TableA].COL_2
            AND      COL_A = [TableA].COL_A) AS VAR_2,
            (SELECT  SOL
            FROM     [LinkedServer].[dbo].[TableSol]
            WHERE    COL_1 = [TableA].COL_3
            AND      COL_2 = [TableA].COL_4
            AND      COL_A = [TableA].COL_A) AS VAR_3,
            (SELECT  SOL
            FROM     [LinkedServer].[dbo].[TableSol]
            WHERE    COL_1 = [TableA].COL_5
            AND      COL_2 = [TableA].COL_6
            AND      COL_A = [TableA].COL_A) AS VAR_4,
FROM        [LinkedServer].[dbo].[TableA]
INNER JOIN  [LinkedServer].[dbo].[TableB] ON [TableA].COL_10 = [TableB].COL_11

It took me 15 secs to run the above query. I changed to use crosstab like below:

SELECT      VAR_1
            SOL.VAR_2,
            SOL.VAR_3,
            SOL.VAR_4,
FROM        [LinkedServer].[dbo].[TableA]
INNER JOIN  [LinkedServer].[dbo].[TableB] ON [TableA].COL_10 = [TableB].COL_11
CROSS APPLY (SELECT 
                CASE 
                    WHEN COL_1 = [TableA].COL_1 AND COL_2 = [TableA].COL_2 THEN SOL
                    ELSE ''
                END AS VAR_2,
                CASE 
                    WHEN COL_1 = [TableA].COL_3 AND COL_2 = [TableA].COL_4 THEN SOL
                    ELSE ''
                END AS VAR_3,
                CASE 
                    WHEN COL_1 = [TableA].COL_5 AND COL_2 = [TableA].COL_6 THEN SOL
                    ELSE ''
                END AS VAR_4
             FROM    [LinkedServer].[dbo].[TableSol]
             WHERE    COL_A = [TableA].COL_A
            ) AS SOL (VAR_2, VAR_3, VAR_4)

But it took me much longer (> 10 mins and I just stop because it did not finish).
Did I miss something?

Best Answer

The query can be rewritten with a crosstab, in order to avoid hitting TableA multiple times:

DECLARE @TableA TABLE (col1 int, col2 int);
DECLARE @TableB TABLE (col3 int, col4 int);

INSERT INTO @TableA 
VALUES 
    (1,1),
    (2,2),
    (3,3),
    (4,4);

INSERT INTO @TableB
VALUES 
    (1,1),
    (2,2),
    (3,3),
    (4,4);

DECLARE @Var1 int, @Var2 int, @Var3 int;

SELECT     @Var1 = CA.var1,
           @Var2 = CA.var2,
           @Var3 = CA.var3
FROM @TableB AS TB
CROSS APPLY (
    SELECT 
        SUM(CASE WHEN col2 = 1 THEN col1 END) AS var1,
              -- ^^ This is <Condition1>
        SUM(CASE WHEN col2 = 2 THEN col1 END) AS var2,
              -- ^^ This is <Condition2>
        SUM(CASE WHEN col2 = 3 THEN col1 END) AS var3
              -- ^^ This is <Condition3>
    FROM @TableA AS TA
) AS CA(var1, var2, var3)
WHERE TB.col3 > 2 -- <Condition4>

SELECT @Var1, @Var2, @var3

This should make it faster. If it doesn't, please post the execution plan, so that we can investigate it further.

Related Question