Alaska Cowboy
asked on
would using current of cursor improve performance ?
I have a long running program that does a simple thing: reads records from Table A (staging) and loads them to the similarly structured Table B (Production).
I'm looking at all ways to improve performance.
After reading and loading 1 record to Table B-Prod, the corresponding record is updated to reflect "loaded".
the update statement updates based on the primary key (claim_seqno).
Would there be any potential performance improvement by using "current of cursor" ?
Currently it's:
UPDATE ODS.MEM_MEMB_SUB_GRP SET ODS_STUS_CD = 'L'
WHERE
MEMB_SUB_GRP_SEQNO = 123456
So I would make it
UPDATE ODS.MEM_MEMB_SUB_GRP SET ODS_STUS_CD = 'L'
WHERE current of cursor
. . . and I think I have to make the query "for update"
- I assume this locks the table from updates
I'm looking at all ways to improve performance.
After reading and loading 1 record to Table B-Prod, the corresponding record is updated to reflect "loaded".
the update statement updates based on the primary key (claim_seqno).
Would there be any potential performance improvement by using "current of cursor" ?
Currently it's:
UPDATE ODS.MEM_MEMB_SUB_GRP SET ODS_STUS_CD = 'L'
WHERE
MEMB_SUB_GRP_SEQNO = 123456
So I would make it
UPDATE ODS.MEM_MEMB_SUB_GRP SET ODS_STUS_CD = 'L'
WHERE current of cursor
. . . and I think I have to make the query "for update"
- I assume this locks the table from updates
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
helps my analysis that I need to present next week.
ASKER
yes, it's possible I can make one big update statement instead of 1 at a time, good idea.