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.
I have a feeling this might be one of those niche case items. But when it comes to single vs. wide use things, I have typically just inserted the necessary customer numbers into a #customers temp table at the start of my procedure and then just joined to that. And I usually just use an IF/ELSE statement for that with two separate insert statements, one for the single row and one for everything.
Sure, it may seem weird to have a table with a single row and customer number in it, but that takes the filtering out right at the start for the single use case. Of course it does not really simplify things for the long use case, but when I am filtering/updating against the entire database, I know I am going to be running that overnight and a slightly longer delay there does not bother me.
Yeah, I tried that (admittedly with a table variable) and that multiplied time by 20+x for the "all" case. As you say, probably not really an issue for a nightly job, but it rankles when you see your beautifully optimized merge spiral out of control.
Table variables and temp tables have some fun back and forth in terms of optimization, especially when your queries get more and more complex. Might be worth trying the temp table as well. I will go back and forth when things are taking too long just to make sure one is not better than the other.
But yeah, I have no other flash of brilliant insight for such a thing. The only other thought is if there is some way to at least put the majority of that code into some other procedure so you do not have to keep replicating it for all 3 things but simply call the procedure from each of them. But that is not really the greatest solution either and might even be impossible based on the nature of what you are doing.
Good luck.
Along the lines of what John A. Moskal II is doing, could you have a local table that just holds spid and customer #, with a unique key constraint containing both columns? Then you could populate the table for the current run (like John is doing with his temporary table) and reference that table filtered by spid for the rest of the proc. At the end, you could delete the rows from the local table for the current spid.
I'm not sure if that's any better then John's approach, but it may be worth a try.
Have you tried creating a Dynamic List and inner joining against that?
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.