At 6:56 PM +1100 3/25/05, Paul White wrote:
>After digesting all those embedded parenthesis, I thought I'd go
>find out what Marty was talking about. Yes, this query seems to
>writes no entries to the log.
>
>update fred set mary = mary;
>
>Does that mean it doesn't need to do the physical write to the table either?
>I've got a bunch of queries similar to Marty's updating calculated
>fields for reporting purposes. I thought I could save time / IO by
>updating only if the field had the wrong answer in it.
>
>UPDATE fred SET mary = (complicated calculation)
>WHERE (mary <> (complicated calculation)) or (mary is null)
>
>I'm thinking the operation might run better if it didnt have to do
>the calculation twice.

That would depend on how many rows qualify to be updated.
There's a certain amount of work involved in deciding that a row
qualifies for update, materializing the replacement expressions,
and giving the row back to DMF. It's fairly deep down within DMF
that it realizes that the row didn't actually change. Unless
nearly all the rows are going to get that far anyway, you're
probably better off doing the calculation twice to exclude rows
that won't change.

One of my current semi-background projects is improving the performance
of expression evaluation. Just how complicated is this complicated
calculation?

Karl

"Betty & Karl Schendel" <schendel@kbcomputer.com> wrote in message
news:mailman.1111750085.29601.info-ingres@cariboulake.com...
> At 6:56 PM +1100 3/25/05, Paul White wrote:
> >After digesting all those embedded parenthesis, I thought I'd go
> >find out what Marty was talking about. Yes, this query seems to
> >writes no entries to the log.
> >
> >update fred set mary = mary;
> >
> >Does that mean it doesn't need to do the physical write to the table
either?
> >I've got a bunch of queries similar to Marty's updating calculated
> >fields for reporting purposes. I thought I could save time / IO by
> >updating only if the field had the wrong answer in it.
> >
> >UPDATE fred SET mary = (complicated calculation)
> >WHERE (mary <> (complicated calculation)) or (mary is null)
> >
> >I'm thinking the operation might run better if it didnt have to do
> >the calculation twice.
>
> That would depend on how many rows qualify to be updated.
> There's a certain amount of work involved in deciding that a row
> qualifies for update, materializing the replacement expressions,
> and giving the row back to DMF. It's fairly deep down within DMF
> that it realizes that the row didn't actually change. Unless
> nearly all the rows are going to get that far anyway, you're
> probably better off doing the calculation twice to exclude rows
> that won't change.
>
> One of my current semi-background projects is improving the performance
> of expression evaluation. Just how complicated is this complicated
> calculation?

I seriously doubt any real application is ever going to benefit from
improving the performance of expression evaluation. Real applications spend
almost all their time doing inter-process and network I/O. The piddly extra
microseconds spent evaluating an expression inside the server in a
less-than-ideal way is completely irrelevant. It's like worrying about the
streamlining of a steam-roller.

But of course if you have expertly-crafted TPC benchmark tests on your mind
(as opposed to real applications) then pay me no heed! :-)

Roy

At 12:09 PM +0000 3/25/05, Roy Hann wrote:
>"Betty & Karl Schendel" <schendel@kbcomputer.com> wrote in message
>.
> >
>> One of my current semi-background projects is improving the performance
>> of expression evaluation. Just how complicated is this complicated
>> calculation?
>
>I seriously doubt any real application is ever going to benefit from
>improving the performance of expression evaluation. Real applications spend
>almost all their time doing inter-process and network I/O. The piddly extra
>microseconds spent evaluating an expression inside the server in a
>less-than-ideal way is completely irrelevant. It's like worrying about the
>streamlining of a steam-roller.
>
>But of course if you have expertly-crafted TPC benchmark tests on your mind
>(as opposed to real applications) then pay me no heed! :-)

Actually, we've measured real benefits (several percentage points)
on real queries when we've hand simplified some expressions as a test.
Until I saw those numbers, I thought the same thing you did.
Also, we're seeing CPU bottlenecks on machines with serious I/O
capabilities, like massive SATA arrays. Most of that's in the row-
at-a-time stuff in QEF, which is next on the list; but some of it was
in expression evaluation.

I will hasten to add that the queries I'm looking at are handling many
millions of rows in an otherwise optimal manner. You are absolutely
right about the normal, garden variety application, where there are
almost certainly dozens of problems far greater than expression
evaluation.

Karl