我需要帮助提高我的SQL查询拉最近的一份文件数
这是查询的只是一部分,但它似乎是瓶颈:我需要帮助提高我的SQL查询拉最近的一份文件数
SELECT CAST (CASE WHEN EXISTS
(SELECT 1
FROM dbo.CBDocument
WHERE (FirmId = R.FirmId) AND
(ContributionDate > DATEADD(m, -3, GETDATE())) AND
((EntityTypeId = 2600 AND EntityId = P.IProductId) OR
(EntityTypeId = 2500 AND EntityId = M.IManagerId)))
THEN 1 ELSE 0 END AS BIT) AS HasRecentDocuments
FROM dbo.CBIProduct P
JOIN dbo.CBIManager M ON P.IManagerId = M.IManagerId
JOIN dbo.CBIProductRating R ON P.IProductId = R.IProductId
JOIN dbo.CBIProductFirmDetail D ON (D.IProductId = P.IProductId) AND
(R.FirmId = D.FirmId)
CROSS APPLY (SELECT TOP 1 RatingDate, IProductRatingId, FirmId
FROM dbo.CBIProductRating
WHERE (IProductId = P.IProductId) AND (FirmId = R.FirmId)
ORDER BY RatingDate DESC) AS RD
WHERE (R.IProductRatingId = RD.IProductRatingId) AND (R.FirmId = RD.FirmId)
有很多其他列的,我通常拉回需要CROSS APPLY和其他连接。我需要优化的位是case语句中的子查询。这个子查询需要3分钟才能返回119k条记录。我对SQL知之甚少,但必须有一种方法来提高效率。
查询的要点就是返回一个标志,如果相关产品已被添加到系统中的最后3个月内的任何文件。
编辑:我的数据库托管在Azure中,数据库优化顾问将不会连接到它。 Azure中有一个调优顾问组件,但它没有提供任何建议。必须有更好的方法来查询。
编辑:在试图进一步简化和确定的罪魁祸首,我又缩减到这个查询:(而不是确定一个最近的文档存在,它只是计算最近的文档)。
SELECT D.FirmId, P.IProductId,
,(SELECT COUNT(DocumentId) FROM dbo.CBDocument WHERE
(FirmId = D.FirmId) AND
(ContributionDate > DATEADD(m, -3, GETDATE())) AND
((EntityTypeId = 2600 AND EntityId = P.IProductId) OR
(EntityTypeId = 2500 AND EntityId = M.IManagerId))) AS RecentDocCount
FROM dbo.CBIProduct P
FULL JOIN dbo.CBIProductFirmDetail D ON D.IProductId = P.IProductId
JOIN dbo.CBIManager M ON M.IManagerId = P.IManagerId
那运行3分53秒。
如果我声明一个变量来存储日期(DECLARE @Today DATE = GETDATE()
) 并在查询(DATEADD(m, -3, @Today)
)把变量到位GETDATE()的,它运行在12秒内。
是否与GETDATE一个已知的性能问题()?据我所知,我不能在视图定义中使用该变量。
这是否对任何可能指向解决方案的东西闪耀光芒?我想我可以把整个事情变成一个存储过程,但是我也必须调整应用程序代码。
谢谢。
根据的东西也可能是更快地使用左连接:
SELECT CAST(CASE when x.FirmId is not null THEN 1 ELSE 0 END AS BIT) AS HasRecentDocuments
FROM dbo.CBIProduct P
JOIN dbo.CBIManager M ON P.IManagerId = M.IManagerId
JOIN dbo.CBIProductRating R ON P.IProductId = R.IProductId
JOIN dbo.CBIProductFirmDetail D ON (D.IProductId = P.IProductId) AND (R.FirmId = D.FirmId)
LEFT JOIN dbo.CBDocument x ON x.FirmId = R.FirmId
AND x.ContributionDate > DATEADD(m, -3, GETDATE())
AND ( (x.EntityTypeId = 2600 AND x.EntityId = P.IProductId)
OR (x.EntityTypeId = 2500 AND x.EntityId = M.IManagerId))
CROSS APPLY (SELECT TOP 1 RatingDate, IProductRatingId, FirmId
FROM dbo.CBIProductRating
WHERE (IProductId = P.IProductId) AND (FirmId = R.FirmId)
ORDER BY RatingDate DESC) AS RD
WHERE (R.IProductRatingId = RD.IProductRatingId) AND (R.FirmId = RD.FirmId)
它肯定看起来简单。
不得不稍微调整语法以阻止SQL Server抱怨,但这似乎不会产生相同的结果......它的运行速度更快,但我必须弄清楚为什么我要得到不同的结果集。此外,CASE似乎与NULL检查有问题。我只得到0。 –
嗯,有什么变化(看到我错过了'什么时候'别的东西?) - 我不认为案件有空检查的问题 - 你是什么意思? – Hogan
这是你索赔需要优化查询:
SELECT CAST(CASE WHEN EXISTS (SELECT 1
FROM dbo.CBDocument d
WHERE (d.FirmId = R.FirmId) AND
(d.ContributionDate > DATEADD(m, -3, GETDATE())) AND
((d.EntityTypeId = 2600 AND d.EntityId = P.IProductId) OR
(d.EntityTypeId = 2500 AND d.EntityId = M.IManagerId)
)
)
. . .
我会相信你的判断。我觉得措辞本查询为您提供更多的路径优化:
SELECT CAST(CASE WHEN EXISTS (SELECT 1
FROM dbo.CBDocument d
WHERE d.FirmId = R.FirmId AND
d.ContributionDate > DATEADD(m, -3, GETDATE()) AND
d.EntityTypeId = 2600 AND d.EntityId = P.IProductId
) OR
EXISTS (SELECT 1
FROM dbo.CBDocument d
WHERE d.FirmId = R.FirmId AND
d.ContributionDate > DATEADD(m, -3, GETDATE()) AND
d.EntityTypeId = 2500 AND d.EntityId = M.IManagerId
)
. . .
那么你一定要在CBDocument(FirmId, EntityTypeId, EntityId, ContributionDate)
的索引。
诸如correlated subqueries
和full outer join
之类的操作相当昂贵,我建议您寻找替代方案。虽然我不熟悉你的数据模型和数据,我建议改变“从表”到CBIProductFirmDetail
和我进一步假设内部联接产品表,然后加入内对产品表管理器列表。如果该连接顺序正确,则会消除一些外连接的开销。
代替相关子查询,以确定一个数,我建议你可以把它看成这是左加入了一个子查询。
SELECT
d.FirmId
, p.IProductId
, COALESCE(Docs.RecentDocCount,0) RecentDocCount
FROM dbo.CBIProductFirmDetail d
JOIN dbo.CBIProduct p ON d.IProductId = p.IProductId
JOIN dbo.CBIManager m ON p.IManagerId = m.IManagerId
LEFT JOIN (
SELECT
FirmId
, EntityId
, EntityTypeId
, COUNT(DocumentId) recentdoccount
FROM dbo.CBDocument
WHERE ContributionDate > DATEADD(m, -3, GETDATE())
AND EntityTypeId IN (2500,2600)
GROUP BY
FirmId
, EntityId
, EntityTypeId
) AS docs ON d.FirmId = docs.FirmId
AND (
(docs.EntityTypeId = 2600 AND docs.EntityId = p.IProductId)
OR (docs.EntityTypeId = 2500 AND docs.EntityId = m.IManagerId)
)
;
可能有益处除以子查询过,以避免尴尬或以加盟,所以:
SELECT
d.FirmId
, p.IProductId
, COALESCE(d2500.DocCount,0) + COALESCE(d2600.DocCount,0) RecentDocCount
FROM dbo.CBIProductFirmDetail d
JOIN dbo.CBIProduct p ON d.IProductId = p.IProductId
JOIN dbo.CBIManager m ON p.IManagerId = m.IManagerId
LEFT JOIN (
SELECT
FirmId
, EntityId
, COUNT(DocumentId) doccount
FROM dbo.CBDocument
WHERE ContributionDate > DATEADD(m, -3, GETDATE())
AND EntityTypeId = 2500
GROUP BY
FirmId
, EntityId
) AS d2500 ON d.FirmId = d2500.FirmId
AND m.IManagerId = d2500.EntityId
LEFT JOIN (
SELECT
FirmId
, EntityId
, COUNT(DocumentId) doccount
FROM dbo.CBDocument
WHERE ContributionDate > DATEADD(m, -3, GETDATE())
AND EntityTypeId = 2600
GROUP BY
FirmId
, EntityId
) AS d2600 ON d.FirmId = d2600.FirmId
AND p.IProductId = d2600.EntityId
;
你的问题现在解决了吗?你仍然有关于这个答案的问题吗?要接受答案“[**点击Tick **](https://ibb.co/ikqyO6)”以获取更多信息,请参阅[help/accepting](https://*.com/help/someone-answers) –
https://docs.microsoft.com/en-us/sql/tools/dta/tutorial-database-engine-tuning-advisor做到这一点,你可能会很幸运,只需添加一些新索引 – Will
谢谢@Will。我一直在那条路上。我无法将DTA连接到Azure中的数据库,Azure的调优顾问不推荐任何内容。 –
如果你可以阻止数据库,并将其加载到本地sql服务器实例,你仍然可以尝试。我相信,索引在蔚蓝的天空中不会有所不同。 – Will