提高BETWEEN性能

Question

我有一个货币兑换率表MngCurrencyRate

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[MngCurrencyRate](
    [DwDimCurrencyRatePk] [int] IDENTITY(0,1) NOT NULL,
    [currencyRef] [int] NOT NULL,
    [FromDate] [datetime] NOT NULL,
    [ToDate] [datetime] NOT NULL,
    [UsdRate] [numeric](19, 6) NOT NULL,
    [EuroRate] [numeric](19, 6) NOT NULL
) ON [PRIMARY]
GO

它具有以下形式的数据：

currencyRef FromDate    ToDate  UsdRate EuroRate
44  2020-02-01 00:00:00.000 2020-02-02 00:00:00.000 0.013986    0.012606
44  2020-02-02 00:00:00.000 2020-02-03 00:00:00.000 0.013986    0.012608
44  2020-02-03 00:00:00.000 2020-02-04 00:00:00.000 0.014028    0.012684
44  2020-02-04 00:00:00.000 2020-02-05 00:00:00.000 0.014065    0.012735
44  2020-02-05 00:00:00.000 2020-02-06 00:00:00.000 0.014046    0.012771
44  2020-02-06 00:00:00.000 2020-02-07 00:00:00.000 0.014025    0.012772

它有一个聚集的列存储索引，以及这个索引：

CREATE NONCLUSTERED INDEX [Ix_MngCurrencyRate_CurrencyRef_Dates] ON [dbo].[MngCurrencyRate]
(
    [currencyRef] ASC,
    [FromDate] ASC,
    [ToDate] ASC
)
INCLUDE (   [DwDimCurrencyRatePk],
    [UsdRate],
    [EuroRate]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

它有109,934条记录，并且以每天大约30条的速度增长。

这个表的每个连接(在我的ETL过程中有很多连接)都是这样的：

declare @fromDate datetime = '2020-01-01';

select
    Preservation.UserRef,
    Preservation.CurrencyRef,
    Preservation.Amount as AmountUserCurrency,
    Preservation.Amount * MngCurrencyRate.UsdRate as AmountUSD,
    Preservation.Amount * MngCurrencyRate.EuroRate as AmountEUR
from
    Preservation
    inner join
    MngCurrencyRate
        on (Preservation.CurrencyRef = MngCurrencyRate.currencyRef
            and Preservation.DateCreated >= MngCurrencyRate.FromDate
            and Preservation.DateCreated < MngCurrencyRate.ToDate
            )
where
    DatePreservationReleased >= @fromDate

如果我只在保留表上运行此查询，而不连接到MngCurrecyRate，则查询在5秒内返回681,550条记录。当加入MngCurrencyRate时，需要20秒才能获得相同的681,550条记录。

>=和<上的连接导致查询花费的时间比奇偶校验连接(=)长得多。为了测试起见，我将连接改为

        on (Preservation.CurrencyRef = MngCurrencyRate.currencyRef
            and MngCurrencyRate.ToDate = '9000-12-31' --latest record per currency
            )

并且它在5秒内返回相同的681,550。

比较两个查询的估计执行计划，第二个查询占用1%的负载，而我实际需要的查询消耗99%的负载。

这两个估计的执行计划都可以在这里查看：https://www.brentozar.com/pastetheplan/?id=By8JZOkLI

我可以做些什么来提高这些查询的性能，无论是在连接中还是在数据结构中？

Answer 1

在Preservation上建立索引很有可能提高性能

create index Preservation_CurrencyRef_DateCreated_idx on Preservation (CurrencyRef, DateCreated)