Thanks, that explains it. Since there was no other connections to the
server, I assumed there wouldn't be much overhead in a large
transaction.
I guess I'll have to pull everything back and forth across the network,
but since the appserver and the DB have their own connection it
shouldn't be too bad.

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

I'll be using Jaybird, so it'll be whatever java.sql.Connection.commit()
resolves to.

I wish I was allowed to hire a DBA :-(
Hopefully Helen's book will turn me into one :-)

-----Original Message-----
From: Dan Wilson [mailto:dwilson@...]
Sent: Tuesday, July 27, 2004 10:24 AM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Slow stored procedure

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.