Brian Rasmusson wrote:
>
> Hi,
>
> I have a tricky problem that i would like to discuss.
>
> Imagine this scenario:
>
> Table X contains transactions, and may contain dublicates.
> Table Y contains the processed transactions, and no dublicates may
> exists. This is handled by having a primary key that the raw
> transactions does not have.
>
> Several machines deliver transactions to the master, placing them in
> table X. My application reads the transactions, and inserts them into
> the destination table Y after processing. If an insert of one
> transaction fail, i simply roll back.
>
> Now, i would like to optimize this. I now read 1000 transactions into a
> hostarray. I now want to do something like a FOR
> :number_of_records_in_hostarray delete from table Y where keycol1=:k1
> and keycol2=:k2 so i _know_ that i can insert the remaining records in
> one large bulk insert.
>
> Any suggestions on how to do this?
>
> Thanks in advance,
> Brian
>
> ------------------------------------------------------------------------
> Brian Rasmusson, Software engineer & analyst e-mail: br_at_belle.dk
> Belle Systems A/S web : www.belle.dk
> Network, Internet and communication specialists phone : +45 59442500
> ------------------------------------------------------------------------
Could you process your transactions from X, putting the records to be
inserted into :host_array, omitting the dupes. Then from :host_array
INSERT into Y.
Omitting the known dupes before hand will cutdown on Rollbacks due
to Dupe PK and increase overall speed.
Received on Thu Apr 23 1998 - 21:22:28 CDT
