查询执行非常缓慢,有任何方法可以进一步改进它吗?

共9个回答,已解决, 标签: c# sql sql-server entity-framework stored-procedures

我有以下查询,因为很多SUM函数调用,我的查询运行太慢。我的数据库中有很多记录,我想得到今年和去年 (过去 30 天、过去 90 天和过去 365 天) 的报告对于每一个:

选择
B.id 为 [ID]
,D.[标题] 作为 [标题]
,E.Class as [Class]

,Sum (当 a.DateCol> = DATEADD (MONTH,-1,GETDATE ()),然后 a.col1 ELSE 0 结束) 作为 [当前-最近 30 天 Col1]
,Sum (当 a.DateCol> = DATEADD (MONTH,-1,GETDATE ()) 然后 a.col2 ELSE 0 结束) 作为 [当前-最近 30 天 Col2]

,Sum (当 a.DateCol> = DATEADD (季度,-1,GETDATE ()),然后 a.col1 ELSE 0 结束) 作为 [当前-最近 90 天 Col1]
,Sum (当 a.DateCol> = DATEADD (季度,-1,GETDATE ()) 然后 a.col2 否则 0 结束) 作为 [当前-最近 90 天 Col2]

,Sum (当 a.DateCol> = DATEADD (YEAR,-1,GETDATE ()),然后 a.col1 ELSE 0 结束) 作为 [当前-最近 365 天 Col1]
,Sum (当 a.DateCol> = DATEADD (YEAR,-1,GETDATE ()) 然后 a.col2 ELSE 0 结束) 作为 [当前-最后 365 天 Col2]

,Sum (当 a. dateCol> = DATEADD (月,-13,GETDATE ()) 和 a. dateCol <= DATEADD (月,-12,GETDATE ()) col1 ELSE 0 结束) 作为 [去年-过去 30 天 Col1]
,Sum (当 a. dateCol> = DATEADD (月,-13,GETDATE ()) 和 a. dateCol <= DATEADD (月,-12,GETDATE ()) col2 否则 0 结束) 作为 [去年-过去 30 天 Col2]

,Sum (当 a. dateCol> = DATEADD (QUARTER,-5,GETDATE ()) 和 a. dateCol <= DATEADD (季度,-4,GETDATE ()) 然后 a. col1 否则 0 结束) 作为 [去年-过去 90 天 Col1]
,Sum (当 a. dateCol> = DATEADD (QUARTER,-5,GETDATE ()) 和 a. dateCol <= DATEADD (季度,-4,GETDATE ()) 然后 a. col2 否则 0 结束) 作为 [去年-过去 90 天 Col2]

,Sum (当 a. dateCol> = DATEADD (YEAR,-2,GETDATE ()) 和 a. dateCol <= DATEADD (年,-1,GETDATE ()) col1 否则 0 结束) 作为 [去年-最后 365 天 Col1]
,Sum (当 a. dateCol> = DATEADD (YEAR,-2,GETDATE ()) 和 a. dateCol <= DATEADD (年,-1,GETDATE ()) col2 否则 0 结束) 作为 [去年-最后 365 天 Col2]


来自
Tb1 a
内联接
A.id = b.fid 和 a.col3 = b.col4 上的 tb2 b
内联接
Tb3 c on b.fid = c.col5
内联接
Tb4 d on c.id = d.col6
内联接
Tb5 e on c.col7 = e.id
按
B.id,d.Title,e.Class

有没有人知道如何改进我的查询,以便运行得更快?

编辑:我被鼓励移动DATEADD function call to the where先声明并加载两年,然后在列中过滤它们,但我不确定建议的答案是否执行和工作,可以在这里找到:Https://stackoverflow.com/a/ 59944426/12536284

如果您同意上述解决方案,请告诉我如何在当前查询中应用它?

仅供参考,我在 C # 中使用这个 SP,实体框架 (DB-First),如下所示:

Var 结果 = MyDBEntities.CalculatorSP ();
第1个答案(采用)
第2个答案
第3个答案
    I assume tb1 is a large table (relative to tb2, tb3, tb4 and tb5).

如果是这样,这里限制该表的选择 (使用 WHERE 子句) 是有意义的。

如果只使用 tb1 的一小部分,例如,因为与 tb2 、 tb3 、 tb4 和 tb5 的连接将所需的行减少到几个百分比, 然后,您应该检查表是否在联接中使用的列上编制了索引。

如果使用了大部分 tb1,那么在将其加入到 tb2 、 tb3 、 tb4 和 tb5 之前对其结果进行分组是有意义的。下面是一个例子。

选择
B.id 为 [ID]
,D.[标题] 作为 [标题]
,E.Class as [Class]
,总和 (a.[当前-最近 30 天 Col1]) 作为 [当前-最近 30 天 Col1]
,总和 (a.[当前-最近 30 天 Col2]) 作为 [当前-最近 30 天 Col2]
,总和 (a.[当前-最近 90 天 Col1]) 作为 [当前-最近 90 天 Col1]
-- 等等。
从 (
选择一个.id,a.col3

,Sum (当 a.DateCol> = DATEADD (MONTH,-1,GETDATE ()),然后 a.col1 ELSE 0 结束) 作为 [当前-最近 30 天 Col1]
,Sum (当 a.DateCol> = DATEADD (MONTH,-1,GETDATE ()) 然后 a.col2 ELSE 0 结束) 作为 [当前-最近 30 天 Col2]

,Sum (当 a.DateCol> = DATEADD (季度,-1,GETDATE ()),然后 a.col1 ELSE 0 结束) 作为 [当前-最近 90 天 Col1]
,Sum (当 a.DateCol> = DATEADD (季度,-1,GETDATE ()) 然后 a.col2 否则 0 结束) 作为 [当前-最近 90 天 Col2]

,Sum (当 a.DateCol> = DATEADD (YEAR,-1,GETDATE ()),然后 a.col1 ELSE 0 结束) 作为 [当前-最近 365 天 Col1]
,Sum (当 a.DateCol> = DATEADD (YEAR,-1,GETDATE ()) 然后 a.col2 ELSE 0 结束) 作为 [当前-最后 365 天 Col2]

,Sum (当 a. dateCol> = DATEADD (月,-13,GETDATE ()) 和 a. dateCol <= DATEADD (月,-12,GETDATE ()) col1 ELSE 0 结束) 作为 [去年-过去 30 天 Col1]
,Sum (当 a. dateCol> = DATEADD (月,-13,GETDATE ()) 和 a. dateCol <= DATEADD (月,-12,GETDATE ()) col2 否则 0 结束) 作为 [去年-过去 30 天 Col2]

,Sum (当 a. dateCol> = DATEADD (QUARTER,-5,GETDATE ()) 和 a. dateCol <= DATEADD (季度,-4,GETDATE ()) 然后 a. col1 否则 0 结束) 作为 [去年-过去 90 天 Col1]
,Sum (当 a. dateCol> = DATEADD (QUARTER,-5,GETDATE ()) 和 a. dateCol <= DATEADD (季度,-4,GETDATE ()) 然后 a. col2 否则 0 结束) 作为 [去年-过去 90 天 Col2]

,Sum (当 a. dateCol> = DATEADD (YEAR,-2,GETDATE ()) 和 a. dateCol <= DATEADD (年,-1,GETDATE ()) col1 否则 0 结束) 作为 [去年-最后 365 天 Col1]
,Sum (当 a. dateCol> = DATEADD (YEAR,-2,GETDATE ()) 和 a. dateCol <= DATEADD (年,-1,GETDATE ()) col2 否则 0 结束) 作为 [去年-最后 365 天 Col2]

从 tb1 a
其中 a.DateCol> = DATEADD (年,-2,GETDATE ())
按 a.id,a.col3 分组
) 作为
内联接
A.id = b.fid 和 a.col3 = b.col4 上的 tb2 b
内联接
Tb3 c on b.fid = c.col5
内联接
Tb4 d on c.id = d.col6
内联接
Tb5 e on c.col7 = e.id
按
B.id,d.Title,e.Class
第4个答案
    One important thing you are missing in explanation.

How are one table related to one another like one to one to one to many

如果加入,应该知道返回多少行,这有助于分析执行计划。

1 号改进 :

日期应该从午夜开始是正确的结果。

因此,除了性能之外,您的查询仍然不正确

DATEADD(MONTH,-1,GETDATE()) -- it return '2020-02-07 15:46:46'左右左右

它将给所有数据之前,'2020-02-07 15:46:46'

你需要像 “2020-02-07 00:00:00” 这样的输出,它会给出输出

Declare @CurDate Datetime=cast(getdate() as date)-- 一次性铸造

您可以在任何地方使用相同的变量。

A.DateCol> = DATEADD (月,-1,@ CurDate)

2 号改进: 您不需要使用的所有结果集dbo.tb1 a。您只需要过去 2 年的数据。

设置 NOCOUNT ON;

声明 @ CurDate Datetime = cast (getdate () 作为日期) -- 一次强制转换
声明 @ from date date = DATEADD (年,-2,@ CurDate)

-- 如果行数很多,请使用 # temp 表 -- 采取合适的数据类型 -- 它没有经过测试

创建表 # Tempa (id int 主键,最近 30 天 int,最近 90 天,最近 365 天,去年 30 天 int,去年 90 天,去年 int)

插入 # Tempa
选择
A.id 为 [ID]
,Sum (当 a.DateCol> = DATEADD (MONTH,-1,GETDATE ()),然后 a.col1 ELSE 0 结束) 作为 [当前-最近 30 天 Col1]
,Sum (当 a.DateCol> = DATEADD (MONTH,-1,GETDATE ()) 然后 a.col2 ELSE 0 结束) 作为 [当前-最近 30 天 Col2]

,Sum (当 a.DateCol> = DATEADD (季度,-1,GETDATE ()),然后 a.col1 ELSE 0 结束) 作为 [当前-最近 90 天 Col1]
,Sum (当 a.DateCol> = DATEADD (季度,-1,GETDATE ()) 然后 a.col2 否则 0 结束) 作为 [当前-最近 90 天 Col2]

,Sum (当 a.DateCol> = DATEADD (YEAR,-1,GETDATE ()),然后 a.col1 ELSE 0 结束) 作为 [当前-最近 365 天 Col1]
,Sum (当 a.DateCol> = DATEADD (YEAR,-1,GETDATE ()) 然后 a.col2 ELSE 0 结束) 作为 [当前-最后 365 天 Col2]

,Sum (当 a. dateCol> = DATEADD (月,-13,GETDATE ()) 和 a. dateCol <= DATEADD (月,-12,GETDATE ()) col1 ELSE 0 结束) 作为 [去年-过去 30 天 Col1]
,Sum (当 a. dateCol> = DATEADD (月,-13,GETDATE ()) 和 a. dateCol <= DATEADD (月,-12,GETDATE ()) col2 否则 0 结束) 作为 [去年-过去 30 天 Col2]

,Sum (当 a. dateCol> = DATEADD (QUARTER,-5,GETDATE ()) 和 a. dateCol <= DATEADD (季度,-4,GETDATE ()) 然后 a. col1 否则 0 结束) 作为 [去年-过去 90 天 Col1]
,Sum (当 a. dateCol> = DATEADD (QUARTER,-5,GETDATE ()) 和 a. dateCol <= DATEADD (季度,-4,GETDATE ()) 然后 a. col2 否则 0 结束) 作为 [去年-过去 90 天 Col2]

,Sum (当 a. dateCol> = DATEADD (YEAR,-2,GETDATE ()) 和 a. dateCol <= DATEADD (年,-1,GETDATE ()) col1 否则 0 结束) 作为 [去年-最后 365 天 Col1]
,Sum (当 a. dateCol> = DATEADD (YEAR,-2,GETDATE ()) 和 a. dateCol <= DATEADD (年,-1,GETDATE ()) col2 否则 0 结束) 作为 [去年-最后 365 天 Col2]
来自 dbo.tb1 a
其中 a.DateCol> = @ FromDateTime
按 a 分组。id

这将大大提高Cardianility EstimateGroup by on wide varchar column like d.Title, e.Class是个坏主意。

现在将 # temp 表与其他表连接起来

选择 a.*
,D.标题,e.类
来自 # Tempa a
内联接
A.id = b.fid 和 a.col3 = b.col4 上的 tb2 b
内联接
Tb3 c on b.fid = c.col5
内联接
Tb4 d on c.id = d.col6
内联接
Tb5 e on c.col7 = e.id
按
B.id,d.Title,e.Class

我不太清楚b.id我不确定,因为我不知道他们之间的关系,但它给了我的方法。

3 号改进

第5个答案
    Just use computed colums

示例

修改表 tb1 添加 [当前-最后 30 天 Col1] 作为 (当 a. dateCol> = DATEADD (月,-1,GETDATE ()) 然后是 a. col1 ELSE 0 端) 持续;

在表中指定计算列

第6个答案
    For optimizing such calculations you man consider pre-calculating some of the values. The idea of pre-calculations is to reduce the number of rows that need to be read or proceed.

实现这一目标的一种方法是使用索引视图让引擎自己做计算。由于这种类型的视图有一些限制,您最终会创建一个简单的表并执行计算。基本上,这取决于业务需求。

因此,在下面的例子中,我创建了一个表RowID and RowDatetime列并插入 100 万行。我使用索引视图来计算每天的实体数,所以我将每年查询 100 万行来计算这些指标,而不是每年查询 365 行。

删除表 (如果存在) [dbo].[数据源];
开始

创建表 [dbo].[数据源]
(
[RowID] BIGINT 标识 (1,1) 主键
,[RowDateTime] DATETIME2
);

开始

如果存在,则删除视图 [dbo]。[vw_DataSource];
开始

使用 SCHEMABINDING 创建视图 [dbo].[vw_DataSource]
作为
选择年份 ([RowDateTime]) 作为 [年份]
,月 ([RowDateTime]) 作为 [月]
,日 ([RowDateTime]) 作为 [日]
,COUNT_BIG (*) AS [Count]
来自 [dbo]。[数据源]
按年份分组 ([RowDateTime])
,月 ([RowDateTime])
,日 ([RowDateTime]);
开始

在 [dbo] 上创建唯一聚集索引 [IX_vw_DataSource]。[vw_DataSource]
(
[年] ASC,
[月] ASC,
[日] ASC
);

开始

声明 @ min bigint,@ max bigint
选择 @ Min = 1,@ Max = 1000000

插入到 [dbo] 中。[数据源] ([RowDateTime])
选择顶部 (@ Max-@ Min 1) 日期部分 (2019,1.0 层 (12 * 兰德 (转换 (varbinary,newid (), 1.0 楼 (28 * 兰德 (转换 (varbinary,newid ()
从主..spt_values t1
交叉连接主..spt_values t2

开始


选择 *
来自 [dbo]。[vw_DataSource]


选择 SUM (当 datefrom parts ([年],[月],[日])> = DATEADD (月,-1,GETDATE ()),然后选择 [计数] 否则 0 结束) 作为 [当前-过去 30 天 Col1]
,SUM (当 DATEFROMPARTS ([年],[月],[日])> = DATEADD (季度,-1,GETDATE ()) 然后 [计数] 否则 0 结束) 作为 [当前-过去 90 天 Col1]
,SUM (当 DATEFROMPARTS ([年],[月],[日])> = DATEADD (年,-1,GETDATE ()) 然后 [计数] 否则 0 结束) 作为 [当前-最后 365 天 Col1]
来自 [dbo]。[vw_DataSource];

这种解决方案的成功在很大程度上取决于数据是如何分布的以及您有多少行。例如,如果一年中的每一天每天有一个条目,视图和表将具有相同的行匹配,因此 I/O 操作不会减少。

此外,以上只是数据具体化和阅读的一个例子。在您的情况下,您可能需要在视图定义中添加更多列。

第7个答案
    I would use a lookup table "Dates" table to join my data to with an index on DatesId. I use the dates as a filter when I want to browse historical data. The join is fast and so it the filtering as the DatesId is clustered primary index (primary key). Add the date column (as included column) for your data table as well.

日期表具有以下列:

DatesId,日期,年份,季度,年份季度,月份,月份,年份,WeekNum,DayOfYear,DayOfMonth,DayNumOfWeek,DayName

示例数据: 20310409 2031-04-09 2031 2 2031-Q2 4 月 4 日 2031 年 4 月 _ 15 99 9 3 星期三

如果你想要一个 csv,你可以 PM 我,这样你就可以把它导入数据库,但是我相信你可以很容易地在网上找到这样的东西并自己制作。

我也添加了一个标识列,以便您可以为每个日期获得一个整数。这使得它更容易使用,但不是一个要求。

从 dbo 中选择 *。日期,其中日期索引介于 (getDateIndexDate (getDate ())-30 和 getDateIndexDate (getDate ()) 0 之间)-30 天前

这让我可以轻松地跳回到某个时期。在这一点上创建自己的观点很容易。当然,您也可以使用 ROW_NUMBER () 函数来执行几年、几周等操作。

一旦我有了我想要的 daterange,我就加入到数据中。工作非常快!

第8个答案
    Since you are always grouping values based on a whole number of months, I would first group by month in a subquery in the from clause. This is similar to using a temporary table. Not certain if this would actually speed up your query.
选择 f.id,f.[Title],f.Class,
总和 (当 f.MonthDiff = 1,然后 col1 否则 0 结束的情况) 作为 [当前-最后 30 天 Col1],
-- Etc
从 (
选择
B.id,
D.[标题],
E.类,
DateDiff (月,a.DateCol,GETDATE ()) 作为 MonthDiff,
总和 (a.col1) 作为 col1,
总和 (a.col2) 作为 col2
从 tb1 a
A.id = b.fid 和 a.col3 = b.col4 上的内连接 tb2 b
内部连接 tb3 c on b.fid = c.col5
内部连接 tb4 d 在 c.id = d.col6
C.col7 = e.id 上的内部连接 tb5 e
其中 a.DateCol 介于 DATEADD (年,-2,GETDATE () 和 GETDATE () 之间
按 b.id 、 d.Title 、 e.Class 、 DateDiff (月、 a.DateCol 、 GETDATE ()) 分组
) F
按 f.id,f.[标题],f.Class 分组
第9个答案
    To improve the speed of SQL query, you must add indexes. For each joined table, you have to add one index.

像这个代码示例为 oracle:

创建索引 supplier_idx
关于供应商 (供应商 _ 名称);

相关问题

什么是 Null 引用异常, 以及如何修复它? 什么是 Null 引用异常, 以及如何修复它? 如何将 bool 更改为0或 1, 我可以将其转换为0或1? 如何防止 PHP 中的 SQL 注入? 何时在 MySQL 中使用单引号、双引号和回刻度 让父母和孩子的树文件夹结构在我的 sql< 8 and no CTEs 8="" and="" no=""></ 8 and no CTEs> SQL 偏移总行计数与 IN 子句一起变慢 查询执行非常缓慢,有任何方法可以进一步改进它吗? 错误: TCP 提供程序: 错误代码 0x2746。在 linux 下通过终端进行 Sql 设置时 无法翻译 LINQ 表达式。以可以翻译的形式重写查询,或者切换到客户端评估 EF Core 3.1 实体框架核心: 没有主键的表