Subquery with TOP performing poorly-CodePudding

Ok I have a performance problem in SQL function. I have query like this:

SELECT
    Number,
    (SELECT TOP 1 Value 
     FROM Table_B 
     WHERE Table_B.Number = Table_A.Number 
     ORDER BY ID DESC)
FROM 
    Table_A

I want see all records from Table_A with the TOP 1 record from Table_B

CodePudding user response：

You may try adding the following index to Table_B:

CREATE INDEX idx_b ON Table_B (Number, ID) INCLUDE (Value);

This index should let the Number lookup be fast, and should allow the first Value, as sorted by the ID, to be found. We include Value at the end of the index since it is used in the select, but not needed in the index itself.

CodePudding user response：

Use ROW_NUMBER() for serializing value with id desc and then LEFT JOIN main table with matching number and retrieve only first record from subquery result. LEFT JOIN is used here because table_B have no value for a number but as per requirement corresponding value of table_A must come.

-- SQL Server
SELECT a.Number
     , b.value
FROM Table_A a
LEFT JOIN (SELECT Number, ID, value
                , ROW_NUMBER() OVER (PARTITION BY Number, ID ORDER BY id DESC) row_num
           FROM Table_B) b         
       ON a.Number = b.Number
      AND b.row_num = 1

CodePudding user response：

WITH CTE_Table_B
AS(
    SELECT
        x.[ID],
        x.[Value],
        x.[Number]
    FROM(
        SELECT
            [ID]
            [Value],
            [Number],
            DENSE_RANK() OVER(PARTITION BY [Number] ORDER BY [ID] DESC) AS [Ranking]
        FROM Table_B
    ) AS x
    WHERE (1=1)
        AND x.[Ranking] = 1
)

SELECT
    a.[Number],
    b.[Value] 
FROM Table_A AS a

LEFT JOIN CTE_Table_B AS b
    ON b.[Number] = a.[Number]

CodePudding user response：

This is the "first in group" method that SMor mentions:

WITH cte AS
(
   SELECT a.Number,
          b.Value, 
          ROW_NUMBER() OVER (PARTITION BY a.Number ORDER BY b.ID DESC) AS rn
   FROM Table_A a
   LEFT OUTER JOIN Table_B b ON a.Number = b.Number
)
SELECT Number, Value
FROM cte
WHERE rn = 1

CodePudding user response：

How about just using CROSS APPLY (assuming there will always be a match - if not use OUTER APPLY):

SELECT
    Number, tb.Value
FROM 
    Table_A
CROSS APPLY
    (SELECT TOP 1 Value 
     FROM Table_B 
     WHERE Table_B.Number = Table_A.Number 
     ORDER BY ID DESC) AS tb