Can anyone optimize this cursor query

This cursor query is taking to long is anyone able to suggest a more efficient approach ????

There is a couple hundred thousand records in each input table and tables have index's on.

DECLARE ExtList CURSOR  FAST_FORWARD FOR
SELECT RIITAHE.AccountNo,
            RIITAHE.AccountType,
            RIITAHE.ExtractDate,
            RIITAHE.ProdTypeCode,
            RIITAHE.LimitExpDate,
            RIITAHE.DebitIntRate,
            RIITAHE.GrossDebtAmt,
            RIITAHE.LimitAmt,
            RIITAHE.AccrIntAmt,
            RAHE.GrossDebtAmt
FROM   RamIntIbisTLAcctHistExtl RIITAHE
LEFT join      RamAccount RA
ON  RIITAHE.AccountNo  = RA.AccountNo Collate Latin1_general_CI_AS and
      RIITAHE.AccountType= RA.AccountType Collate Latin1_general_CI_AS and
      RA.EndDateExtl > @ExtractDate
LEFT join      RAMAcctHistExtl  RAHE
ON RIITAHE.AccountNo  = RAHE.AccountNo Collate Latin1_general_CI_AS and
     RIITAHE.AccountType= RAHE.AccountType Collate Latin1_general_CI_AS and
     RAHE.ExtractDate = (
                                    select MAX(ExtractDate)
                                    from   RAMAcctHistExtl  
                where  AccountNo       = RIITAHE.AccountNo Collate Latin1_general_CI_AS and
                           AccountType = RIITAHE.AccountType Collate Latin1_general_CI_AS
                )
WHERE RA.AccountNo is not null
ORDER BY       RIITAHE.AccountNo,
      RIITAHE.AccountType
rourkas1Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

GiggsipCommented:
I'm not much of a Microsoft  SQL guy but in oracle you can give a hint for the DB to use an index descending.
If you can do the same in Microsoft SQL you can take this join:

LEFT join     RAMAcctHistExtl  RAHE
ON RIITAHE.AccountNo  = RAHE.AccountNo Collate Latin1_general_CI_AS and
     RIITAHE.AccountType= RAHE.AccountType Collate Latin1_general_CI_AS and
     RAHE.ExtractDate = (
                                    select MAX(ExtractDate)
                                    from   RAMAcctHistExtl  
              where  AccountNo      = RIITAHE.AccountNo Collate Latin1_general_CI_AS and
                         AccountType = RIITAHE.AccountType Collate Latin1_general_CI_AS
              )

and really make the inner select much more efficient by just taking

select /*+ INDEX_DESC (RAMAcctHistExtl  INDEX_NAME) */ ExtractDate
from   RAMAcctHistExtl  
where  AccountNo      = RIITAHE.AccountNo Collate Latin1_general_CI_AS and
AccountType = RIITAHE.AccountType Collate Latin1_general_CI_AS
and rownum = 1

this could save you an entire table scan and sort.
hope this helps in any way.
jeshbrCommented:
cursors are very very slow.
what are you needing the cursor for?
rourkas1Author Commented:
jeshbr:  I just need to iterate through and insert records
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

randeepsCommented:
try this as you are unnecessarily using correlated query


DECLARE ExtList CURSOR  FAST_FORWARD FOR
SELECT RIITAHE.AccountNo,
            RIITAHE.AccountType,
            RIITAHE.ExtractDate,
            RIITAHE.ProdTypeCode,
            RIITAHE.LimitExpDate,
            RIITAHE.DebitIntRate,
            RIITAHE.GrossDebtAmt,
            RIITAHE.LimitAmt,
            RIITAHE.AccrIntAmt,
            RAHE.GrossDebtAmt
FROM   RamIntIbisTLAcctHistExtl RIITAHE
LEFT join     RamAccount RA
ON  RIITAHE.AccountNo  = RA.AccountNo Collate Latin1_general_CI_AS and
      RIITAHE.AccountType= RA.AccountType Collate Latin1_general_CI_AS and
      RA.EndDateExtl > @ExtractDate
LEFT JOIN (
             select AccountNo, AccountType, MAX(ExtractDate) As ExtractDate
             from   RAMAcctHistExtl  
             Group by AccountNo, AccountType
              )

           AS RAHE    ON RIITAHE.AccountNo  = RAHE.AccountNo Collate Latin1_general_CI_AS and
     RIITAHE.AccountType= RAHE.AccountType Collate Latin1_general_CI_AS

WHERE RA.AccountNo is not null
ORDER BY      RIITAHE.AccountNo,
     RIITAHE.AccountType


For query's faster performance (if huge no of records are outputted), if possible, you shoul have a clustered index on Account No and AccountType (if these are not changed very frequently) and non clustered index on following fields

RamIntIbisTLAcctHistExtl.EndDateExtl
RamIntIbisTLAcctHistExtl.AccountNo (if not as clustered)
RamIntIbisTLAcctHistExtl.AccountType (if not part of clustered)
RamAccount.AccountNo
RamAccount.AccountType


Also note that if the subquery              (select AccountNo, AccountType, MAX(ExtractDate) As ExtractDate
             from   RAMAcctHistExtl  
             Group by AccountNo, AccountType) returns lots of records then it may be better to declare a TABLE variable with index on the account no and account type and insert the data in this table and use it in the join. This will make join with this resuilt even faster


jeshbrCommented:
i have had a similar problem.

if you need to insert record and fast then try something like this:

insert into TableName
     (AccountNo,
            AccountType,
            ExtractDate,
            ProdTypeCode,
            LimitExpDate,
            DebitIntRate,
            GrossDebtAmt,
            LimitAmt,
            AccrIntAmt,
            GrossDebtAmt
      )
SELECT RIITAHE.AccountNo,
            RIITAHE.AccountType,
            RIITAHE.ExtractDate,
            RIITAHE.ProdTypeCode,
            RIITAHE.LimitExpDate,
            RIITAHE.DebitIntRate,
            RIITAHE.GrossDebtAmt,
            RIITAHE.LimitAmt,
            RIITAHE.AccrIntAmt,
            RAHE.GrossDebtAmt
FROM   RamIntIbisTLAcctHistExtl RIITAHE
LEFT join     RamAccount RA
ON  RIITAHE.AccountNo  = RA.AccountNo Collate Latin1_general_CI_AS and
      RIITAHE.AccountType= RA.AccountType Collate Latin1_general_CI_AS and
      RA.EndDateExtl > @ExtractDate
LEFT JOIN (
             select AccountNo, AccountType, MAX(ExtractDate) As ExtractDate
             from   RAMAcctHistExtl  
             Group by AccountNo, AccountType
              )

           AS RAHE    ON RIITAHE.AccountNo  = RAHE.AccountNo Collate Latin1_general_CI_AS and
     RIITAHE.AccountType= RAHE.AccountType Collate Latin1_general_CI_AS

WHERE RA.AccountNo is not null
ORDER BY      RIITAHE.AccountNo,
     RIITAHE.AccountType

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jeshbrCommented:
i have been using this approach to some data conversion procedures

the original took 45 minutes to run with a cursor and now take only 1 or 2 minutes
randeepsCommented:
@jeshbr - You do have a point. It really depends on how big the cursor is and the business logic used for each record.

@rourkas1 - keeping the performance in mind - can you paste the business logic that you are going to use when iterating the cursor - we may just be able to get it done in a query instead to get a performance boost.
jeshbrCommented:
if you can possibly consolidate your cursor iteration into one insert you will save tons of time... usually.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.