使用count和order按函数优化查询

Question

我对这个查询的优化有一个问题，我有3个表(product =Catalogo.GTIN, Sales Header =TEDEF.Factura和Sales Detail =TEDEF.Farmacia)。

查询试图查找列VPRODEXENIGV_FAR的Mode。这个没有ORDER BY的查询执行不到3秒(详细信息表大约有3000万行)。

但是当我添加ORDER BY子句时，查询现在需要30多分钟才能运行。

我想知道如何优化这个查询或者我需要优化的索引。

SELECT *
FROM Catalogo.GTIN G
CROSS APPLY
(SELECT TOP 1 
COUNT(FAR.VPRODEXENIGV_FAR) [ROW],
YEAR(FAC2.VFECEMI_FAC) [AÑO],
MONTH(FAC2.VFECEMI_FAC) [MES],
FAR.VCODPROD_FAR_003,
CASE WHEN FAR.VPRODEXENIGV_FAR = 'A' THEN 1 ELSE 0 END AfectoIGV
FROM 
TEDEF.Factura FAC2
INNER JOIN 
TEDEF.Farmacia FAR ON FAC2.VTDOCPAGO_FAC = FAR.VTDOCPAGO_FAC
AND FAC2.VNDOCPAGO_FAC = FAR.VNDOCPAGO_FAC
WHERE 
G.CODIGO = FAR.VCODPROD_FAR_003
GROUP BY 
YEAR(FAC2.VFECEMI_FAC),
MONTH(FAC2.VFECEMI_FAC),
FAR.VCODPROD_FAR_003,
FAR.VPRODEXENIGV_FAR
ORDER BY 
1 DESC --- <----- THE PROBLEM IS HERE
) GG

Answer 1

哎哟!你有一个非常昂贵的从属子查询。这是昂贵的，因为SELECT TOP(n) ... ORDER BY col DESC做了大量的工作来创建一个结果集，只丢弃除一行外的所有结果。而且，它是一个依赖子查询，所以它对Catalogo.GTIN的每一行都运行。

看起来您想要为每个Catalogo.GTIN行计算最近一个月和一年的结果集行数。所以，让我们试着重构你的查询来做到这一点。

我们将从一个子查询开始，为每个目录条目获取最新Factura行的月起始日期。

SELECT CODIGO,
DATEFROMPARTS(YEAR(maxd), MONTH(maxd),1) maxmes
FROM (
SELECT MAX(FAC2.VFECEMI_FAC) maxd,
G.CODIGO
FROM Catalogo.GTIN G
JOIN TDEF.Farmacia FAR
ON G.CODIGO = FAR.VCODPROD_FAR_003
JOIN TEDEF.Factura FAC2
ON FAC2.VTDOCPAGO_FAC = FAR.VTDOCPAGO_FAC
AND FAC2.VNDOCPAGO_FAC = FAR.VNDOCPAGO_FAC
GROUP BY G.CODIGO
) maxd

这是明智的测试，并确保它正确工作，并执行得相当好。如果您在SSMS中进行测试，您可以使用"显示实际执行计划";看看它是否会推荐一个额外的索引。此子查询只需要运行一次，而不是每个G.CODIGO行运行一次。

那么我们将在更大的查询中使用它。

SELECT G.*,
COUNT(FAR.VPRODEXENIGV_FAR) [ROW],
YEAR(FAC2.VFECEMI_FAC) [AÑO],
MONTH(FAC2.VFECEMI_FAC) [MES],
FAR.VCODPROD_FAR_003,
CASE WHEN FAR.VPRODEXENIGV_FAR = 'A' THEN 1 ELSE 0 END AfectoIGV
FROM Catalogo.GTIN G
JOIN (
SELECT CODIGO,
DATEFROMPARTS(YEAR(maxd), MONTH(maxd),1) maxmes
FROM (
SELECT MAX(FAC2.VFECEMI_FAC) maxd,
G.CODIGO
FROM Catalogo.GTIN G
JOIN TDEF.Farmacia FAR
ON G.CODIGO = FAR.VCODPROD_FAR_003
JOIN TEDEF.Factura FAC2
ON FAC2.VTDOCPAGO_FAC = FAR.VTDOCPAGO_FAC
AND FAC2.VNDOCPAGO_FAC = FAR.VNDOCPAGO_FAC
GROUP BY G.CODIGO
) maxd
) maxmes ON G.CODIGO = maxmes.CODIGO
JOIN TEDEF.Farmacia FAR 
ON G.CODIGO = FAR.VCODPROD_FAR_003
JOIN TEDEF.Factura FAC2
ON FAC2.VTDOCPAGO_FAC = FAR.VTDOCPAGO_FAC
AND FAC2.VNDOCPAGO_FAC = FAR.VNDOCPAGO_FAC
AND FAC2.VFECEMI_FAC >= maxmes.maxmes
GROUP BY maxmes.maxmes, 
G.CODIGO,
FAR.VCODPROD_FAR_003,
FAR.VPRODEXENIGV_FAR

这里是棘手的地方:

DATEFROMPARTS(YEAR(maxd), MONTH(maxd),1) maxmes将任意日期maxd变为该月的第一天。

并且，FAC2.VFECEMI_FAC >= maxmes.maxmes过滤掉当月第一天之前的行(对于该CODIGO)。它以一种可sargable的方式做到这一点:一种可以利用FAC2.VFECEMI_FAC上的索引的方式。

这是执行TOP(1) ORDER BY d DESC的另一种方法。越来越快。

都是关于行集的。特别是在使用GROUP BY时，限制每个集合中的行数有助于提高性能。

显然我无法调试这个

Answer 2

又是我，最后我解决了优化的问题，现在查询延迟大约20秒(与排序指令和计数在一个表超过3000万行)，我希望这种方式可以帮助别人或可以被社区优化更多。我通过使用Row_Number指令解决了这个问题，这样服务器就可以获取排序指令的索引，并产生如下神奇的结果:

WITH x
AS
(
SELECT *, ROW_NUMBER() OVER (PARTITION BY GG.COD, GG.[AÑO], GG.[MES] ORDER BY GG.[ROW] DESC) [ID]
FROM Catalogo.GTIN G
CROSS APPLY
(
SELECT COUNT(FAR.VPRODEXENIGV_FAR) [ROW]
, YEAR(FAC2.VFECEMI_FAC) [AÑO]
, MONTH(FAC2.VFECEMI_FAC) [MES]
, FAR.VCODPROD_FAR_003 [COD]
, CASE WHEN FAR.VPRODEXENIGV_FAR = 'A' THEN 1 ELSE 0 END AfectoIGV
FROM TEDEF.Factura FAC2
INNER JOIN TEDEF.Farmacia FAR
ON FAC2.VTDOCPAGO_FAC = FAR.VTDOCPAGO_FAC
AND FAC2.VNDOCPAGO_FAC = FAR.VNDOCPAGO_FAC
WHERE G.CODIGO = FAR.VCODPROD_FAR_003
GROUP BY YEAR(FAC2.VFECEMI_FAC)
, MONTH(FAC2.VFECEMI_FAC)
, FAR.VCODPROD_FAR_003
, FAR.VPRODEXENIGV_FAR
--            ORDER BY 1 DESC   --- <---- this is the bad guy, please, don't do that xD
) GG
) SELECT *
FROM x WHERE ID = 1

通过这种方式，我可以对Count指令进行排序并计算列FAR的Mode。VPRODEXENIGV_FAR