sql - 调整 SQL Server 查询
问题描述
我的目标是清除块中的数据,并考虑在将 ID_COl 值放入 #temptable 后循环遍历它。
DECLARE @PD datetime = DATEADD(day, -5, GETUTCDATE());
DELETE GP
FROM dbo.Table_A N
INNER JOIN dbo.Table_BL GP
ON N.NOTIF_ID = GP.NOTIF_ID
WHERE N.FCT_AUD_IND = 'Y'
AND N.CRE_TMST < @PD
AND EXISTS ( SELECT 1 ID_COl
FROM dbo.Table_A AS N1
WHERE N1.NOTIF_APPL_ID = N.NOTIF_APPL_ID
AND N1.SOR_NOTIF_APPL_GP_ID = N.SOR_NOTIF_APPL_GP_ID
AND N1.NOTIF_DLT_IND = 'Y');
解决方案
您正在强制 SQL Server 执行嵌套循环以查找 Table_A 中的记录,但是当您让他决定并改为 JOIN 时,SQL Server 可能会找到更好的方法:
DECLARE @PD datetime = DATEADD(day, -5, GETUTCDATE());
WITH N1 AS (
SELECT DISTINCT NOTIF_APPL_ID, SOR_NOTIF_APPL_GP_ID
FROM dbo.Table_A
WHERE NOTIF_DLT_IND = 'Y'
)
DELETE GP
FROM dbo.Table_A N
INNER JOIN N1 ON N.NOTIF_APPL_ID = N1.NOTIF_APPL_ID
AND N.SOR_NOTIF_APPL_GP_ID = N1.SOR_NOTIF_APPL_GP_ID
INNER JOIN dbo.Table_BL GP ON N.NOTIF_ID = GP.NOTIF_ID
WHERE N.FCT_AUD_IND = 'Y'
AND N.CRE_TMST < @PD
此外,定义索引可能会有所帮助。
推荐阅读
- python - 覆盖嵌套序列化程序的 create 方法会引发 TypeError: create() got multiple values for keyword argument
- ios - 如何有效地解析数据
- azure - 有没有人可以告诉我如何解决错误“远程服务器返回错误:(409)冲突。”
- powerbi - 基于多个切片器值 Power BI 显示用户计数
- javascript - 无法获取动态创建的输入字段的更改值
- android - 颤振 - 在应用购买插件中运行时出错:“MissingPluginException for BillingClient”
- python - What is difference between soup of selenium and requests?
- laravel - Laravel - Error: App\Exports\View must be compatible with Maatwebsite\Excel\Concerns\FromView::view(): Illuminate\Contracts\View\View
- angular - 如何在 Angular 应用程序的 cypress 测试中公开/访问数据存储状态?
- python - I keep getting a 404 error when django is looking for static files