sql-server - 用于游标循环更新触发数据的 TSQL 替代方案
问题描述
在这个案例的答案中
由于性能原因,有人建议我不应该使用游标。在更新触发器中循环更新数据的最佳实践是什么?
更新:
以下是用于创建该更新触发器的 TSQL。
CREATE TRIGGER [dbo].[trAfterUpdateInfoDoc]
ON [dbo].[InfoDocs]
AFTER UPDATE
AS
BEGIN
DECLARE @infodoctemplateid INT;
DECLARE @infodocid INT;
DECLARE @requireccount FLOAT(2);
DECLARE @filledcount FLOAT(2);
DECLARE @pcnt FLOAT(2);
DECLARE c CURSOR FOR
SELECT id
FROM InfoDocs ifd
WHERE exists (SELECT 1 FROM Inserted AS i WHERE i.id = ifd.id)
OPEN c
FETCH NEXT FROM c INTO @infodocid
WHILE @@Fetch_Status = 0
BEGIN
SELECT @infodoctemplateid = InfoDocTemplateId
FROM InfoDocs
WHERE id = @infodocid;
SELECT @requireccount = COUNT(*)
FROM InfoDocTemplateFields
WHERE InfoDocTemplateId = @infodoctemplateid
AND IsRequired = 1;
IF (@requireccount = 0)
BEGIN
set @pcnt = 100;
END
ELSE
BEGIN
select @filledcount = count(*) from InfoDocFields
where InfoDocId = @infodocid
and InfoDocTemplateFieldId in (select id from InfoDocTemplateFields where InfoDocTemplateId = @infodoctemplateid and IsRequired = 1)
and (BooleanValue is not null or (StringValue is not null and StringValue <> '') or IntValue is not null or DateValue is not null)
set @pcnt = @filledcount / @requireccount * 100.0;
END
update InfoDocs set PercentageCompleted = @pcnt Where id = @infodocid;
Fetch next From c into @infodocid
End
Close c
Deallocate c
END
解决方案
我已经尝试将您的光标翻译成基于集合的代码,但是我无法测试我的解决方案是否正确,而且我昨晚没有睡太多觉,所以我可能在这里和那里错过了一些东西 - 并且它可能是比我写的更短、更高效的代码,但它应该给你一个很好的起点:
CREATE TRIGGER [dbo].[trAfterUpdateInfoDoc]
ON [dbo].[InfoDocs]
AFTER UPDATE
AS
BEGIN
WITH CTE1 AS
(
SELECT ifd.Id,
SUM(CASE WHEN IsRequired = 1 THEN 1 ELSE 0 END) As RequiredCount,
(
select count(*)
from InfoDocFields
where InfoDocFields.InfoDocId = ifd.Id,
and InfoDocTemplateFieldId in (
select id
from InfoDocTemplateFields
where InfoDocTemplateId = idtf.InfoDocTemplateId
and IsRequired = 1
)
and
InfoDocFields.BooleanValue is not null
or (InfoDocFields.StringValue is not null and InfoDocFields.StringValue <> '')
or InfoDocFields.IntValue is not null
or InfoDocFields.DateValue is not null
) As Filledcount
FROM InfoDocs ifd
JOIN InfoDocTemplateFields idtf
ON ifd.InfoDocTemplateId = idtf.InfoDocTemplateId
WHERE exists (SELECT 1 FROM Inserted AS i WHERE i.id = ifd.id)
GROUP BY ifd.Id, idtf.InfoDocTemplateId
), CTE2 AS
(
SELECT ifd.Id,
CASE WHEN RequiredCount = 0 THEN
100
ELSE
Filledcount / RequiredCount * 100.0
END As Completed
FROM CTE1
)
UPDATE docs
SET PercentageCompleted = Completed
FROM InfoDocs docs
JOIN cte2
ON docs.id = cte2.Id
END
推荐阅读
- python - 消息在 Python firebase-admin 中不起作用
- mongodb - mongodb 的每个分片是否对一个集群中的其他分片不可见?
- flutter - 嵌套的 ListView 不可见 - Flutter
- python - matplotlib 中不寻常的条形图
- odoo - 如何在odoo13中仅从pos源位置加载产品
- asp.net-mvc - 托管服务器中的内存不足异常
- java - 我们可以在浏览器中发出一个包含多个标头作为 URL 的 GET API 调用请求吗
- c# - 如何在 ASP.NET MVC 中将条带支付与自定义表单集成?
- javascript - 有没有办法从后端自动渲染项目?
- jenkins - 在 Jenkins 中启用永久禁用的项目