英文:
Good index for DATE range search in MSSQL
问题
以下是翻译好的部分:
我在Azure SQL数据库中有一张表,看起来像这样:
CREATE TABLE [dbo].[DayStatus](
[Date] [DATE] NOT NULL,
[LocationId] [INT] NOT NULL,
[TypeId] [INT] NOT NULL,
[Total] [DECIMAL](15, 5) NULL,
[Timezone] [NVARCHAR](70) NULL,
[Currency] [NVARCHAR](3) NOT NULL,
PRIMARY KEY CLUSTERED
(
[Date] ASC,
[LocationId] ASC,
[TypeId] ASC
)
) ON [PRIMARY]
GO
问题
我需要优化以下针对上述表的SELECT语句:
SELECT
[Date],
[LocationId],
[TypeId],
[Total],
[Timezone],
[Currency]
FROM [dbo].[DayStatus]
WHERE
Date >= '2022-06-01' and Date <= '2023-01-17'
and Currency = 'USD'
and LocationId in (1, 2, 3, 4, 6, 10)
and TypeId in (1, 2, 3, 5)
我考虑了以下索引,但似乎无法看到显著的性能差异。
哪一个更好,还有更好的吗?
测试1
CREATE NONCLUSTERED INDEX [IX__Test1] ON [dbo].[DayStatus]
(
[Date] ASC,
[Currency] ASC,
[LocationId] ASC,
[TypeId] ASC
)
GO
测试2
CREATE NONCLUSTERED INDEX [IX__Test2] ON [dbo].[DayStatus]
(
[Currency] ASC,
[LocationId] ASC,
[TypeId] ASC
)
INCLUDE([Date],[Timezone],[Total])
GO
编辑
是否使用以下查询更好?
SELECT
[Date],
[LocationId],
[TypeId],
[Total],
[Timezone],
[Currency]
FROM [dbo].[DayStatus]
WHERE
Currency = 'USD'
and LocationId in (1, 2, 3, 4, 6, 10)
and TypeId in (1, 2, 3, 5)
and Date >= '2022-06-01' and Date <= '2023-01-17'
英文:
I have a table in Azure SQL Database that looks like this:
CREATE TABLE [dbo].[DayStatus](
[Date] [DATE] NOT NULL,
[LocationId] [INT] NOT NULL,
[TypeId] [INT] NOT NULL,
[Total] [DECIMAL](15, 5) NULL,
[Timezone] [NVARCHAR](70) NULL,
[Currency] [NVARCHAR](3) NOT NULL,
PRIMARY KEY CLUSTERED
(
[Date] ASC,
[LocationId] ASC,
[TypeId] ASC
)
) ON [PRIMARY]
GO
Question
I need to optimize the following SELECT statement to the above table:
SELECT
[Date],
[LocationId],
[TypeId],
[Total],
[Timezone],
[Currency]
FROM [dbo].[DayStatus]
WHERE
Date >= '2022-06-01' and Date <= '2023-01-17'
and Currency = 'USD'
and LocationId in (1, 2, 3, 4, 6, 10)
and TypeId in (1, 2, 3, 5)
I have considered the following indexes, but it seems I cannot see a significant performance difference.
Which one is better, and is there an even better one?
Test 1
CREATE NONCLUSTERED INDEX [IX__Test1] ON [dbo].[DayStatus]
(
[Date] ASC,
[Currency] ASC,
[LocationId] ASC,
[TypeId] ASC
)
GO
Test 2
CREATE NONCLUSTERED INDEX [IX__Test2] ON [dbo].[DayStatus]
(
[Currency] ASC,
[LocationId] ASC,
[TypeId] ASC
)
INCLUDE([Date],[Timezone],[Total])
GO
EDIT
Would it be better to have the query below?
SELECT
[Date],
[LocationId],
[TypeId],
[Total],
[Timezone],
[Currency]
FROM [dbo].[DayStatus]
WHERE
Currency = 'USD'
and LocationId in (1, 2, 3, 4, 6, 10)
and TypeId in (1, 2, 3, 5)
and Date >= '2022-06-01' and Date <= '2023-01-17'
答案1
得分: 1
在“相等列”之前应该放置“范围列”。
可能最好的索引策略是
将Currency, LocationId, TypeId
的任何顺序作为前三列(选择对工作负载中的其他查询最有用的顺序,不要太过关注这里的选择性),然后将Date
作为第四个关键列,INCLUDE (Timezone, Total)
。
这将允许查询通过对24个不同索引查找结果进行“UNION ALL”来返回结果(因为所需的结果是以下不同范围的连接,可以使用这样的索引有效地查找)。
Currency = USD' and LocationId = 1 and TypeId = 1 and Date >= '2022-06-01' and Date <= '2023-01-17'
Currency = USD' and LocationId = 10 and TypeId = 1 and Date >= '2022-06-01' and Date <= '2023-01-17'
Currency = USD' and LocationId = 2 and TypeId = 1 and Date >= '2022-06-01' and Date <= '2023-01-17'
Currency = USD' and LocationId = 3 and TypeId = 1 and Date >= '2022-06-01' and Date <= '2023-01-17'
Currency = USD' and LocationId = 4 and TypeId = 1 and Date >= '2022-06-01' and Date <= '2023-01-17'
Currency = USD' and LocationId = 6 and TypeId = 1 and Date >= '2022-06-01' and Date <= '2023-01-17'
Currency = USD' and LocationId = 1 and TypeId = 2 and Date >= '2022-06-01' and Date <= '2023-01-17'
Currency = USD' and LocationId = 10 and TypeId = 2 and Date >= '2022-06-01' and Date <= '2023-01-17'
Currency = USD' and LocationId = 2 and TypeId = 2 and Date >= '2022-06-01' and Date <= '2023-01-17'
Currency = USD' and LocationId = 3 and TypeId = 2 and Date >= '2022-06-01' and Date <= '2023-01-17'
Currency = USD' and LocationId = 4 and TypeId = 2 and Date >= '2022-06-01' and Date <= '2023-01-17'
Currency = USD' and LocationId = 6 and TypeId = 2 and Date >= '2022-06-01' and Date <= '2023-01-17'
Currency = USD' and LocationId = 1 and TypeId = 3 and Date >= '2022-06-01' and Date <= '2023-01-17'
Currency = USD' and LocationId = 10 and TypeId = 3 and Date >= '2022-06-01' and Date <= '2023-01-17'
Currency = USD' and LocationId = 2 and TypeId = 3 and Date >= '2022-06-01' and Date <= '2023-01-17'
Currency = USD' and LocationId = 3 and TypeId = 3 and Date >= '2022-06-01' and Date <= '2023-01-17'
Currency = USD' and LocationId = 4 and TypeId = 3 and Date >= '2022-06-01' and Date <= '2023-01-17'
Currency = USD' and LocationId = 6 and TypeId = 3 and Date >= '2022-06-01' and Date <= '2023-01-17'
Currency = USD' and LocationId = 1 and TypeId = 6 and Date >= '2022-06-01' and Date <= '2023-01-17'
Currency = USD' and LocationId = 10 and TypeId = 6 and Date >= '2022-06-01' and Date <= '2023-01-17'
Currency = USD' and LocationId = 2 and TypeId = 6 and Date >= '2022-06-01' and Date <= '2023-01-17'
Currency = USD' and LocationId = 3 and TypeId = 6 and Date >= '2022-06-01' and Date <= '2023-01-17'
Currency = USD' and LocationId = 4 and TypeId = 6 and Date >= '2022-06-01' and Date <= '2023-01-17'
Currency = USD' and LocationId = 6 and TypeId = 6 and Date >= '2022-06-01' and Date <= '2023-01-17'
我将Date
添加为您的IX__Test2
索引的第四个关键列,执行计划显示索引查找,但深入属性显示SQL Server正在执行上述操作。
英文:
Equality columns should go before range columns.
Potentially the best indexing strategy would be
Any ordering of Currency, LocationId, TypeId
as first three columns (choose whichever ordering is most useful for other queries in your workload and don't get hung up on selectivity here) followed by Date
as the fourth key column and INCLUDE (Timezone, Total)
This would allow the query to return the results by UNION ALL
-ing the results of 24 distinct index seeks (as the desired results are a concatenation of the following distinct ranges which can be seeked efficiently with such an index).
Currency = USD' and LocationId = 1 and TypeId = 1 and Date >= '2022-06-01' and Date <= '2023-01-17'
Currency = USD' and LocationId = 10 and TypeId = 1 and Date >= '2022-06-01' and Date <= '2023-01-17'
Currency = USD' and LocationId = 2 and TypeId = 1 and Date >= '2022-06-01' and Date <= '2023-01-17'
Currency = USD' and LocationId = 3 and TypeId = 1 and Date >= '2022-06-01' and Date <= '2023-01-17'
Currency = USD' and LocationId = 4 and TypeId = 1 and Date >= '2022-06-01' and Date <= '2023-01-17'
Currency = USD' and LocationId = 6 and TypeId = 1 and Date >= '2022-06-01' and Date <= '2023-01-17'
Currency = USD' and LocationId = 1 and TypeId = 2 and Date >= '2022-06-01' and Date <= '2023-01-17'
Currency = USD' and LocationId = 10 and TypeId = 2 and Date >= '2022-06-01' and Date <= '2023-01-17'
Currency = USD' and LocationId = 2 and TypeId = 2 and Date >= '2022-06-01' and Date <= '2023-01-17'
Currency = USD' and LocationId = 3 and TypeId = 2 and Date >= '2022-06-01' and Date <= '2023-01-17'
Currency = USD' and LocationId = 4 and TypeId = 2 and Date >= '2022-06-01' and Date <= '2023-01-17'
Currency = USD' and LocationId = 6 and TypeId = 2 and Date >= '2022-06-01' and Date <= '2023-01-17'
Currency = USD' and LocationId = 1 and TypeId = 3 and Date >= '2022-06-01' and Date <= '2023-01-17'
Currency = USD' and LocationId = 10 and TypeId = 3 and Date >= '2022-06-01' and Date <= '2023-01-17'
Currency = USD' and LocationId = 2 and TypeId = 3 and Date >= '2022-06-01' and Date <= '2023-01-17'
Currency = USD' and LocationId = 3 and TypeId = 3 and Date >= '2022-06-01' and Date <= '2023-01-17'
Currency = USD' and LocationId = 4 and TypeId = 3 and Date >= '2022-06-01' and Date <= '2023-01-17'
Currency = USD' and LocationId = 6 and TypeId = 3 and Date >= '2022-06-01' and Date <= '2023-01-17'
Currency = USD' and LocationId = 1 and TypeId = 6 and Date >= '2022-06-01' and Date <= '2023-01-17'
Currency = USD' and LocationId = 10 and TypeId = 6 and Date >= '2022-06-01' and Date <= '2023-01-17'
Currency = USD' and LocationId = 2 and TypeId = 6 and Date >= '2022-06-01' and Date <= '2023-01-17'
Currency = USD' and LocationId = 3 and TypeId = 6 and Date >= '2022-06-01' and Date <= '2023-01-17'
Currency = USD' and LocationId = 4 and TypeId = 6 and Date >= '2022-06-01' and Date <= '2023-01-17'
Currency = USD' and LocationId = 6 and TypeId = 6 and Date >= '2022-06-01' and Date <= '2023-01-17'
I added Date
as a fourth key column to your IX__Test2
index and the execution plan shows an index seek but digging into the properties shows SQL Server is doing the above.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论