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
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
cursors are very very slow.
what are you needing the cursor for?
what are you needing the cursor for?
ASKER
jeshbr: I just need to iterate through and insert records
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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.
@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.
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.