问题的SQL Server查询
我有以下查询其运行缓慢:问题的SQL Server查询
WITH AcdTran
AS (select SequenceNo,
ReqID,
PolNumber,
transaction_id,
application_data,
trans_type,
retries,
status,
direction
from dbo.acord_transaction_benchmark with (nolock)
where direction = 'OUT')
select top 1 *
from AcdTran a
where a.transaction_id = (select top 1 transaction_id
from AcdTran b
where b.PolNumber = a.PolNumber
order by ReqID,
SequenceNo,
transaction_id)
and (status = 'New'
or status = 'Resubmit')
and retries > 0
我如何优化呢?跑得更快?
谢谢
应与窗口函数ROW_NUMBER更快:
WITH AcdTran AS (
SELECT SequenceNo,
ReqID,
PolNumber,
transaction_id,
application_data,
trans_type,
retries,
status,
direction,
ROW_NUMBER() OVER(PARTITION BY transaction_id ORDER BY ReqID, SequenceNo, transaction_id) N
FROM dbo.acord_transaction_benchmark with (nolock)
WHERE direction = 'OUT')
SELECT *
FROM AcdTran
WHERE (status = 'New'
OR status = 'Resubmit')
AND retries > 0
AND N = 1;
由于我没有你的表结构也没有任何数据,我明明没有测试,所以你可能要修改查询一下,但你有想法。
好吧,这是返回,多个记录。我只需要1条记录.. – JustMe
@JustMe - 你能否提供一些示例数据和期望的结果。 –
JustMe:我的错误,它应该是'分区由transaction_id',我编辑我的帖子。 –
如果你拿出你的子查询转换为连接:
WITH AcdTran
AS (select SequenceNo,
ReqID,
PolNumber,
transaction_id,
application_data,
trans_type,
retries,
status,
direction
from dbo.acord_transaction_benchmark with (nolock)
where direction = 'OUT')
select top 1 *
from AcdTran a
inner join AcdTran b on a.SequenceNo = b.SequenceNo --or whatever the PK is
where a.transaction_id = b.transaction_id and
a.PolNumber = b.PolNumber and
(a.status = 'New' or a.status = 'Resubmit') and
a.retries > 0
order by b.ReqID,
b.SequenceNo,
b.transaction_id
什么是它应该做的?你的'select top 1 *'缺少一个'order by' –
它应该按顺序选择一个新的状态为New或重新提交的记录。所以,如果我有2个记录的某个PolNumber,并且1有序列1,另一个有序列2,它应该选取序列1的那个。 – JustMe
好吧,这只是一个标准的“最大n个每组”查询,然后我认为。有3种方法[在此评估](http://www.sqlmag.com/article/departments/optimizing-top-n-per-group-queries)。最好的使用取决于你的索引和数据分布。 –