problematic sql server query

I have the following query which is running slow:

 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开发者_JAVA百科 = (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  

How can i optimize this? To run faster?

Thank you

---

Should be faster with windowing function 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;

As I don't have your table structure nor any data, I obviously didn't test it so you may have to modify the query a bit, but you have the idea.

---

What if you pulled out your sub-query into a join:

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