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](
[source_curve_def_id] [int] NOT NULL,
[as_of_date] [datetime] NOT NULL,
[Assessment_curve_type_value_id] [int] NOT NULL,
[curve_source_value_id] [int] NOT NULL,
[maturity_date] [datetime] NOT NULL,
[curve_value] [float] NOT NULL,
[create_user] [varchar](50) NULL,
[create_ts] [datetime] NULL,
[update_user] [varchar](50) 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_type_value_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[source_price_curve] WITH NOCHECK ADD CONSTRAINT [FK_source_curve_def_id] FOREIGN KEY([source_curve_def_id])
REFERENCES [dbo].[source_price_curve_def] ([source_curve_def_id])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[source_price_curve] CHECK CONSTRAINT [FK_source_curve_def_id]
GO
The following indexes available in the table source_price_curve
(many were created long back and may not be used)
CREATE CLUSTERED INDEX [source_curve_def_id_index] ON [dbo].[source_price_curve]
(
[as_of_date] ASC,
[source_curve_def_id] ASC,
[maturity_date] ASC,
[is_dst] ASC,
[curve_source_value_id] ASC
)
CREATE NONCLUSTERED INDEX [source_price_curve_123] ON [dbo].[source_price_curve]
(
[source_curve_def_id] ASC,
[as_of_date] ASC
)
INCLUDE ( [maturity_date])
CREATE NONCLUSTERED INDEX [IX_PT_source_price_curve_as_of_date_curve_source_value_id]
ON [dbo].[source_price_curve]
(
[as_of_date] ASC,
[curve_source_value_id] ASC
)
INCLUDE ( [curve_value],
[maturity_date],
[source_curve_def_id])
CREATE NONCLUSTERED INDEX [IX_PT_source_price_curve_as_of_date_curve_source_value_id_Assessment_curve_type_value_id]
ON [dbo].[source_price_curve]
(
[as_of_date] ASC,
[curve_source_value_id] ASC,
[Assessment_curve_type_value_id] ASC
)
INCLUDE ( [curve_value],
[maturity_date],
[source_curve_def_id])
CREATE NONCLUSTERED INDEX [IX_PT_source_price_curve_maturity_date] ON [dbo].[source_price_curve]
(
[maturity_date] ASC
)
CREATE NONCLUSTERED INDEX [IX_PT_source_price_curve_source_curve_def_id_curve_source_value_id]
ON [dbo].[source_price_curve]
(
[source_curve_def_id] ASC,
[curve_source_value_id] ASC
)
INCLUDE ( [as_of_date],
[Assessment_curve_type_value_id],
[curve_value],
[is_dst],
[maturity_date])
CREATE NONCLUSTERED INDEX [IX_PT_source_price_curve_source_curve_def_id111]
ON [dbo].[source_price_curve]
(
[source_curve_def_id] ASC,
[Assessment_curve_type_value_id] ASC,
[curve_source_value_id] ASC,
[maturity_date] ASC,
[as_of_date] ASC
)
I hope all these explanations will help to analyze! Thanks in advance.
Best Answer
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:Good performance relies on there being many rows per
price_curve_id
. You may need an index of the form: