慢SQL查询返回 - 如何获得更好的结果?
问题描述:
我被要求编写一个只返回具有特定操作代码(内部名称)的帐户的查询。有6个代码必须在帐户上,并且不能有其他代码可用。该表存储在一个名为tblTrans表(见下文):慢SQL查询返回 - 如何获得更好的结果?
AccountNo ActionCode TransactionNo
1234 Code1 45646453
1234 Code2 88758475
1234 Code3 48978978
1234 Code4 15687898
1234 Code5 59878988
1234 Code6 12345677
2548 Code1 45464533
2548 Code2 89789489
2548 Code3 89789781
2548 Code4 16878983
2548 Code5 59889884
2548 Code6 12456776
2548 Code12 12348887
因此所需的输出将只返回帐户1234
目前这正与查询做过类似
SELECT AccountNo, ActionCode, TransactionNo
FROM tblTrans AS t1
INNER JOIN
tblTrans AS t2 ON t1.AccountNo = t2.AccountNo
tblTrans AS t3 ON t2.AccountNo = t3.AccountNo
tblTrans AS t4 ON t3.AccountNo = t4.AccountNo
tblTrans AS t5 ON t4.AccountNo = t5.AccountNo
tblTrans AS t6 ON t5.AccountNo = t6.AccountNo
WHERE t1.ActionCode = 'Code1'
AND t2.ActionCode = 'Code2'
AND t3.ActionCode = 'Code3'
AND t4.ActionCode = 'Code4'
AND t5.ActionCode = 'Code5'
AND t5.ActionCode = 'Code6'
AND t6.AccountNo NOT IN (SELECT ActionCode
FROM tblTrans
WHERE ActionCode IN ('Code12'))
对不起,如果语法出来了,我不得不为了安全原因改变一些细节!
这实际上运行非常缓慢,扼流系统很少。我的问题是,是否有更好的方式来编写这种类型的查询。我对CTE的了解不多,但听起来似乎适合?
答
这应该返回AccountNo的只有你想要的代码。
SELECT AccountNo
FROM tblTrans
GROUP BY AccountNo
HAVING Sum(Case When ActionCode = 'Code1' Then 1 End) > 0
And Sum(Case When ActionCode = 'Code2' Then 1 End) > 0
And Sum(Case When ActionCode = 'Code3' Then 1 End) > 0
And Sum(Case When ActionCode = 'Code4' Then 1 End) > 0
And Sum(Case When ActionCode = 'Code5' Then 1 End) > 0
And Sum(Case When ActionCode = 'Code6' Then 1 End) > 0
And Sum(Case When ActionCode IN ('Code1', 'Code2', 'Code3', 'Code4', 'Code5', 'Code6') Then 0 Else 1 End) = 0
我修改了查询。这一个应该表现更好。如果性能仍然不可接受,那么您应该考虑在表中添加索引。
要恢复所有的数据...
; With FilteredData As
(
SELECT AccountNo
FROM tblTrans
GROUP BY AccountNo
HAVING Sum(Case When ActionCode = 'Code1' Then 1 End) > 0
And Sum(Case When ActionCode = 'Code2' Then 1 End) > 0
And Sum(Case When ActionCode = 'Code3' Then 1 End) > 0
And Sum(Case When ActionCode = 'Code4' Then 1 End) > 0
And Sum(Case When ActionCode = 'Code5' Then 1 End) > 0
And Sum(Case When ActionCode = 'Code6' Then 1 End) > 0
And Sum(Case When ActionCode IN ('Code1', 'Code2', 'Code3', 'Code4', 'Code5', 'Code6') Then 0 Else 1 End) = 0
)
Select TblTrans.AccountNo, ActionCode, TransactionNumber
From TblTrans
Inner Join FilteredData
On tblTrans.AccountNo = FilteredData.AccountNo
为什么认为,这将有更好的表现? –
此版本比我的第一次尝试表现更好。我在比较执行计划的基础上这样说。 –
“DISTINCT”是否存在问题? –