Re: Need some help with SQL case/when performance

On Thu, Feb 25, 2021 at 9:20 AM <smith5646midrange@xxxxxxxxx> wrote:

I'm not understanding your two passes?

I assume that you mean pass 1 will prefix the 0 on all 5 fields whether it
is blank or not and pass 2 will clean up what have a value of a single 0
only ("0 ") but I can't do that with a single SQL so I have to run 5
SQLs to clean up the single 0 so now I am running 6 passes...
Update to set all fields with the 0 prefix
Update file set vendor = ' ' where vendor = '0 '.
Update file set maxinv = ' ' where maxinv = '0 '.
And 3 more, one for each remaining field.

What did I miss?

I think Kevin meant two passes for one field. So, naively, that could
be 10 passes for 5 fields, but your 6 total passes does suffice. I
think you understand Kevin's suggestion just fine.

I am guessing (and please understand, only guessing) that this will be
faster than one pass that tries to do everything with CASE, because
CASE has to execute for every row, for every field being updated.
That's a lot of "function-calling" rather than leveraging the SQL
engine.

One "dumb" pass, followed by five WHERE-leveraging passes strikes me
as very possibly more efficient than one really "smart" pass.

John Y.