Link to home
Start Free TrialLog in
Avatar of rourkas1
rourkas1

asked on

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
Avatar of Giggsip
Giggsip

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.
cursors are very very slow.
what are you needing the cursor for?
Avatar of rourkas1

ASKER

jeshbr:  I just need to iterate through and insert records
SOLUTION
Avatar of randeeps
randeeps

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
@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.
if you can possibly consolidate your cursor iteration into one insert you will save tons of time... usually.