时间戳

首页 > 解决方案 > 提高大表上选择查询的性能

sql - 提高大表上选择查询的性能

问题描述

我有一个大表并且正在使用 SQL Server 2017。该表包含 4 列，如下所示。

 Column name     Data type
 IndexDate       date
 Code            nvarchar(20)
 Sedol           nvarchar(7)
 Wgt             float

该表包含不同股票市场指数（例如标准普尔 500、罗素 1000、富时 100 等）的每日数据以及指数中每只股票的权重。因此，通常每天大约有 16,000 行数据被添加到数据库中。

数据从 2000 年之前开始。目前大约有 6000 万行。

最近人们想查询该表并具有如下结构，

    Sedol     06-03-2019    06-04-2019   06-05-2019 ...
    ABC       0.03          0.0301       0.031
    MNB       0.015         0.0147       0.0145
    LPK       0.02          0.0201       0.0201

所以我创建了一个使用数据透视函数的动态存储过程。如下所示。它需要三个参数，2 个日期用于日期范围，另一个用于索引代码。

 declare @dates nvarchar(max) = ''

select @dates = @dates + QUOTENAME(date) + ',' 
    from DVLP_QES_MS.dbo.DateDimension where date >= @dateFrom and date <= @dateTo and IsWeekend = 0
set @dates = SUBSTRING(@dates, 1, len(@dates) - 1)

declare @q nvarchar(max) = ''


 set @q = 'select * from
(
    select Sedol, wgt, w.Date 
    from tblBMWeights w right join tblDates d on w.Date = d.Date
    where Code = ''' + @bm + ''' and d.IsWeekend = 0 and d.Date >= ''' + convert(varchar(10), @dateFrom, 110) + ''' and d.Date <= ''' + convert(varchar(10), @dateTo, 110) + '''
)source pivot(max(wgt) for Date in (' + @dates + ' )) as pvt order by Sedol'

exec(@q)

通常这似乎有效，查询 6 个月的数据大约需要 2 秒。然而，现在需要进一步查询数据。

18 个月大约需要 14 秒 30 个月大约需要 40 秒 3 年大约需要 1 分钟

我在我的表上创建了索引以帮助提高查询的效率。我的问题是我的表应该如何构造以及如何提高性能，以便 3 年的数据不需要 1 分钟来查询？显然其他人有更大的表，他们如何处理超过 1 亿行的表？我应该有多张桌子吗？

我已经保存了执行计划，但似乎无法将其附加到这篇文章中。

更新

这是我的计划的链接

三大指数

第 1)

 NONCLUSTERED INDEX [IDX_tblBenchmarkWeights_CodeDate_Sedol] ON [dbo]. 
 [tblBenchmarkWeights]
 (
    [Code] ASC,
    [Date] ASC
 )
 INCLUDE (  [Sedol]) 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

第二）

  NONCLUSTERED INDEX [IDX_tblBenchmarkWeights_CodeDateSedolWgt] ON [dbo]. 
 [tblBenchmarkWeights]
  (
     [Code] ASC,
     [Date] ASC
  )
  INCLUDE (     [Sedol],[Wgt]) 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

第三）

 NONCLUSTERED INDEX [IDX_tblBenchmarkWeights_DateSedol] ON [dbo]. 
 [tblBenchmarkWeights]
 (
    [Date] ASC
 )
  INCLUDE (     [Sedol]) 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

我的日期表上的最后一个索引

  NONCLUSTERED INDEX [IDX_DateDimension_IsWeekendDate] ON [dbo].[DateDimension]
 (
    [IsWeekend] ASC,
    [Date] ASC
  )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

标签： sqlsql-serversql-server-2017

解决方案

要提高选择查询性能，请遵循以下约束：

仅选择您需要的字段，快速创建索引以快速查找表，而不是依赖表扫描，使用 EXISTS 而不是 IN 检查数据是否存在，在查询任何数据时在 Select 和 Joins 上使用 WITH (NOLOCK) 表提示表为了避免死锁，使用 try catch 块来避免死锁条件，在列字段上使用适当的数据类型，在 SQL 对象名之前使用架构名称，在查询中使用事务

推荐阅读