I know I've brought this up before on some forum, but this pattern keeps emerging and giving me grief.
Basically, I'm building a utility that updates records, typically customer records. It does some complex thing that I spend a lot of time proofing and optimizing. And when I'm done, I want to be able to run the utility in one of two ways: either I want to update a specific customer record, or I want to update them all. I have always done this the same way, and I cannot come up with another, basically I have a line that says:
and ((@customer_no is null) or (c.customer_no = @customer_no))
This isn't a problem for the case of doing them all, but for doing a single record, the difference between that and c.customer_no = @customer_no is on the order of 20x slower, which is a big deal for the single customer run where you are likely to have significant performance considerations (i.e. stuffing it into LP_CUSTOMER_RANK).Is there a better pattern for this? And heaven help you if you wanted to be able to optionally add lists...
and c.customer_no = @customer_no
The end result is that I am having to build separate utilities with 99% of the code copied back and forth.
Gawain Lavers said:It does some complex thing
Hard to comment without knowing more, but, again and again, I find that if new code is slow, I break it up. Ten little updates can/will tend to run much faster than one complex one. Also, I tend to use the temp table method others mention, rather that pull data from a List. Sometimes with an index on the customer_no column.