You are only part way done. Plan on cutting that in half even still.

Someone already showed you what to do. Write a stored procedure that
receives all the values and attempts an insert, catches a duplicate key
violation and then does an update. This way, you just send the information
to the server and insert and update on exception. Data only crosses the wire
once instead of three times. No need to do the check first. Just use a
stored procedure.

Regards,
Jason Wharton
CPS - Mesa AZ
http://www.ibobjects.com

-- We may not have it all together --
-- But together we have it all --

----- Original Message -----
From: "Joe Martinez" <joe@...>
To: <IBObjects@yahoogroups.com>
Sent: Thursday, November 07, 2002 9:18 AM
Subject: Re: [IBO] Parameterized inserts?


>
> >Hmm, didn't think about the mid-stream commits: you will need them if the
> >batch is huge. You will need a Commit branch at suitable countered
> >periods. CommitRetaining isn't suitable because it blocks garbage
> >collection and would have a big impact on the hygiene of your database.
> >
> >This makes the concurrency of the two datasets a bit problemmatical.
>
> I'm not really worried about concurrency of the two datasets. In fact,
the
> source dataset is just a DBF file that I'm reading with the BDE. I'm just
> iterating through it. I don't see any reason these need to be
synchronized
> in any way.
>
>
> >This is a very inefficient way to do a batch transfer, especially such a
> >huge one. I hope your users are patient.
>
> Well, with all of this I've DRAMATICALLY improved the speed of my import
> function. I was originally just using a TIBOTable, and doing an Insert(),
> set FieldByName(), Post() for each record (plus a TIBOQuery to do the
> "exists" checking), with no commits. A 47,000 record import was taking
> over 24 hours. Now that I'm using a prepared parameterized TIB_Cursor for
> the checking and prepared parameterized TIB_DSQL for the inserts, it takes
> only about 10 minutes! I'd say my users will be happy! ;-)
>
> Helen, thank you very much for all of your help last night. I'm really
> enjoying learning the ins and outs of IBO, and my application is getting
> faster all the time because of it!
>
> -Joe