|
|
Posted by Alan Little on 10/13/06 00:39
Carved in mystic runes upon the very living rock, the last words of
Anonymous of comp.lang.php make plain:
> Alan Little wrote:
>>
>> Carved in mystic runes upon the very living rock, the last words of
>> <petersprc@gmail.com> of comp.lang.php make plain:
>>
>> > Alan Little wrote:
>> >> I have affiliates submitting batches of anywhere from 10 to
>> >> several hundred orders. Each order in the batch must include an
>> >> order ID, originated by the affiliate, which must be unique across
>> >> all orders in all batches ever submitted by that affiliate. I'm
>> >> trying to figure out the most efficient way to check the
>> >> uniqueness of the order ID.
>> >>
>> >> The two ways I'm considering are: 1) a single query to load all
>> >> the affiliate's order IDs into an array for checking as I process
>> >> the batch; 2) creating a temporary table with just the affiliate's
>> >> IDs, querying it for each order in the batch.
>> >>
>> >> Any ideas which would be more efficient? Would there be any
>> >> significant difference either way?
>> >
>> > Perhaps this could work: If you keep the submissions in a set of
>> > staging tables, then your unique key would work.
>>
>> Thanks, but that isn't really practical; all orders are maintained in
>> the orders table, so inserting them into an intermediate table would
>> just be another step. Using a single table, I would have to flag the
>> records as temporary, as I added them, then remove the flag upon
>> successful completion, or delete the flagged records upon failure.
>> With an intermediate table, I wouldn't have the deletion problem, but
>> I would still then have to copy them to the permanent table upon
>> success, which (one would hope) would be the majority of cases.
>>
>> > Of the things you're considering, a single query would be more
>> > scalable, but it's like you won't notice much difference at all
>> > until the volume went up.
>>
>> That's kind of what I figured, that even with an order of magnitude
>> of tens of thousands of past records, it wouldn't make much
>> difference either way. I just wondered about the efficiency of having
>> to loop over all those records from the single query, and load them
>> into an array, but I think that would be the most efficient.
>
> Nope, the most efficient way is to let the database do the checking.
>
> [snip]
Thanks for your detailed response. However, it doesn't work, because I
don't want to wait until I've already attempted the insert, to know if it
fails. The issue of two different affiliates having the same order ID
isn't a problem, as there is an affiliate ID as well, so what needs to be
unique is AID + OID, as you've described.
The affiliates are providing their own order IDs, since they're going to
be using whatever systems they use to collect the orders. When they
submit the orders for fufillment, here's the sequence:
1) Affiliate submits a batch of (say) 100 orders
2) Check all the order data
3) Submit the batch as a single order to ZC
4) Store the sub-orders in custom tables
The problem with doing the checking at insert time is that the first 90
orders may be OK, with a problem on order #91. The whole batch has to be
rejected and re-submitted, so that means I have to delete the 90 sub-
orders already entered, *and* the order submitted to ZC. Thus I want to
do the ID check at step 2.
Hmmm..... I just had a thought, though. If I do a bulk insert, the whole
thing would fail if there was a dup, and then I wouldn't have to delete
the already-processed sub-orders, but I'd still have to delete the ZC
order. I'll have to think about it some more.
--
Alan Little
Phorm PHP Form Processor
http://www.phorm.com/
[Back to original message]
|