AnsweredAssumed Answered

Use of Cursor in Stored Procedure

Question asked by navzjoshi00 Champion on May 31, 2013
Latest reply on Jun 25, 2013 by navzjoshi00
We have a stored procedure which inserts data (time related data from "other work") into 4 tables -

a_prestage
a_stage
a_prefinal
a_final

This is the way it works -

1. select cursor created for "a_prestage" on 3 fields

2. truncation of -
a_prestage
a_stage
a_prefinal
a_final

3. data inserted into "a_prestage"

4. cursor opened / loops for each of the records inserted "a_prestage", and records (that were not inserted in "a_prestage" with some extra conditions in the select clause checking the fields form "step 1.") are inserted in "a_prestage"

5. data inserted into "a_stage"

6. data inserted into "a_prefinal"

7. data inserted into "final"


The issue is with the "step 4". As of date, for 1000 records, the procedure takes close to 5 hours.

Is there a better way to counter this ? Any help would be much appreciated

NJ

Outcomes