On 7/27/2004 at 9:57 AM Rick DeBay wrote:

> I left P_CONVERT_DATES running overnight (15 hrs) on my laptop and it
> still hadn't completed. I'm trying to import and convert dates from
> varchar to date or time. Claimsraw has 83919 rows and no indexes,
> claimspaidreversed has 102153 rows and the PK
> (RXCLAIMNBR,CLMSEQNBR,CLAIMSTS). X_claim_dates has 82028 rows.
> The SP P_CONVERT_DATES loops through all rows in x_claim_dates, converts
> the strings to dates and times using P_CVRT_YYYMMDD and P_CVRT_HHMMSS,
> and updates the two tables.
> Am I doing anything incorrectly or making performance mistakes?
>

This query is going to read 82000 rows and issue updates to up to 164000 rows, if I have understood it properly. It is a very bad idea to try to do all of that in a single transaction without committing in the middle. The symptom of problems is that the server just gets slower and slower until it grinds to a halt. Since you can't commit from within an SP, you will need to do one of two things:

1. Modify the SP so that it can work in sections: i.e., send in a "starting point" parameter of some kind. Then only do a few thousand rows before returning to the client so that the client can issue a commit and start a new transaction.
2. Move the operation out of an SP and onto the client completely, then issue periodic hard-commits as you process the data. For that, you might need to use two transactions: one in which you issue the "select", which doesn't get committed until you are done, and the second to perform the updates, which would get committed every 10,000 updates or so.

Make sure you do hard-commits, not commit-retaining.

HTH,

Dan.