At 11:11 AM 24/06/2003 +0700, you wrote:

> > The process flow is well hidden by your pseudocode, but
> > your hard commit should NOT occur for every row. One of
> > your loops (and only you can know which one) should be
> > counting rows updated and, when this count gets to
> > 10,000, you call commit.
>
>Yup. I've optimized my code to do that. I think, beside the update
>process, the thing that slow my application down, is the commit itself.
>Because after a commit, the connection is broken and I have to
>re-connect to the server.

Erm, I don't think so. One connection can support as many transactions as
you need. When you commit, the transaction is finished, so you have to
start a new one to do the next batch of updates. That's what was so weird
about that pseudocode that you posted originally.

You can do gazillions of things with one open connection. You can have one
transaction in progress or you can have several. Your workflow for a
session looks like this:

Arrive at the office.
Hang up your coat.
Get your coffee.
Switch on the computer and drink your coffee.
Open your application.
Application connects to the server,
for i = 1 to 1 gazillion do begin
Select what you want to do and set it up
for j = 1 to howevermanytimesyouwanttodothis do begin
start a transaction
for k = 1 to about10000 do begin
if statement not prepared then prepare the statement **** see note
below
get values for the parameters and pass them to the statement
execute the statement
increment k
end
commit (ends the transaction)
increment j
end
increment i (do something else or the same again if you want)
end
look at clock
if time >= goinghometime then begin
disconnect from server
end application
switch off computer
clear up coffee cups
get coat
go to bus stop
else
sigh
carry on

>I hope I can optimize my code so it can finish
>its task below 5 minutes.

I hope you can do this task in less than 1 minute (unless you are doing
some really complicated updates)

**** make sure you are not recreating the statement every time this loop
runs. Use parameters!!!!

heLen