Re: Combining SQL Updates

On 1/30/2018 3:45 PM, dlclark@xxxxxxxxxxxxxxxx wrote:

Thanks. I did get that to work (see after statistics). But I
thought I'd check with Visual Explain and, with two runs of each
statement, here are the results from the second run of each statement.
So, what do y'all think? Better to keep it simple even with more than one
pass of the file? Or, better to be more complex as long as you can do it
all in one statement?


Visual Explain Closed Not Closed Totaled AllInOne
---------------------- ------ ---------- ------- --------
Optimization Time (ms) 958 919 1,877 1,881
Run Time (ms) 380 231 611 882
Rows Fetched 1,014 191 1,205 1,205
CPU Time 8 3 11 18

I've become less and less interested in optimising /for the machine/ and
more and more interested in optimising /for me/. Which of these is
easier for me to understand and modify (for modify it we surely shall!)?
I think a pair of simple statements, each one doing one thing and one
thing well, is what would be better for me.

I don't know enough about the optimiser to extend the statistics above
to the general situation, but my naïve model is that there are two
non-overlapping sets: Closed, Open. The database manager does not need
to read all the rows in the table (for example Processing, Backorder) in
order to get at the desired rows. Whether we process the desired rows
with separate statements or a single statement seems about the same
level of work (remember, naïve). I might be tempted to think that the
optimiser might have more work to do to optimise a complex statement,
but that's probably me projecting my own weakness.