Tuning a query with temp table join

I have a query as follows:

SELECT @tenor_from =CONVERT(DATETIME,MIN(spc.maturity_date),103)  
FROM source_price_curve spc  
INNER JOIN #source_price_curve_list spcl
    ON spc.source_curve_def_id  = spcl.price_curve_id  
WHERE spc.as_of_date >= @as_of_date_from

It takes almost 12 seconds to run.Removing the temp table #source_price_curve_list from join gives the result in less than 1 second.

The source_price_curve table has 130 million records. The temp table #source_price_curve_list has one record in output(as given below).It may contain more data in future

select * from #source_price_curve_list
 rowID  price_curve_id
  1          1

Why does the inner join to a one record temp table make the query take so much longer time? I need to reduce the query run time to less than 1 sec.

The execution plan of the query with the join is provided in the below link:

https://www.brentozar.com/pastetheplan/?id=BksaaWjSb

The query without the join to temp table runs in less than 1 sec.The execution plan link is as follows:

https://www.brentozar.com/pastetheplan/?id=BJ2_3boSZ

The table source_price_curve is created as follows:

```
CREATE TABLE [dbo].source_price_curve NULL,
[create_ts] [datetime] NULL,
[update_user] varchar NULL,
[update_ts] [datetime] NULL,
[bid_value] [float] NULL,
[ask_value] [float] NULL,
[is_dst] [int] NOT NULL,
CONSTRAINT [IX_unique_source_curve_def_id_index] UNIQUE NONCLUSTERED
(
[as_of_date] ASC,
[source_curve_def_id] ASC,
[maturity_date] ASC,
[is_dst] ASC,
[curve_source_value_id] ASC,
[Assessment_curve

Why does the inner join to a one record temp table make the query take so much longer time?

Without the join, the optimizer is smart enough to work out that it can find the minimum value by reading one row from the end of the index.

Unfortunately, it is not currently equipped to apply the same sort of logic when the query is more complicated (with a join or grouping clause, for example). To work around this limitation, you can rewrite the query to compute local minimums per row in the temporary table, then find the global minimum.

Perhaps the easiest way to express this in T-SQL is to use the APPLY operator:

SELECT
    -- Global minimum
    @tenor_from = MIN(MinMaturityPerCurveID.maturity_date)
FROM #source_price_curve_list AS SPCL
CROSS APPLY
(
    -- Minimum maturity_date per price_curve_id
    SELECT TOP (1) 
        SPC.maturity_date
    FROM  dbo.source_price_curve AS SPC
    WHERE
        SPC.source_curve_def_id = SPCL.price_curve_id
         and as_of_date >= @as_of_date_from 
    ORDER BY
        SPC.maturity_date ASC
) AS MinMaturityPerCurveID;

Good performance relies on there being many rows per price_curve_id. You may need an index of the form:

CREATE NONCLUSTERED INDEX
    [IX dbo.source_price_curve source_curve_def_id, maturity_date, as_of_date]
ON dbo.source_price_curve 
(
    source_curve_def_id,
    maturity_date,
    as_of_date
);

StackExchange Database Administrators Q#177838, answer score: 7