Re: A new proof of the superiority of set oriented approaches: numerical/time serie linear interpolation


"Brian Selzer" <brian@xxxxxxxxxxxxxxxxxxx> wrote in message
news:jy5Zh.4247$uJ6.3542@xxxxxxxxxxxxxxxxxxxxxxxxxxxxx

"Cimode" <cimode@xxxxxxxxxxx> wrote in message
news:1177863186.258396.136950@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
On 29 avr, 16:21, "Brian Selzer" <b...@xxxxxxxxxxxxxxxxxxx> wrote:
I would have done it differently. I would have used a cursor to
generate
[Snipped]
I respectfully think you miss the point of the entire thread.
Thinking procedurally (use of cursors) is inherently inferior to set
oriented approaches independently of a specific implementation and
regardless of how one defines performance as response time, ability to
run well in concurrent query environment and so forth.


You're right. As a rule, set-based thinking is superior to procedural
thinking. But there are times when it becomes necessary to break the
rules.
It is critical to think procedurally in order to understand the execution
plans that an optimizer produces.

This is correct, but it largely misses the point. One thinks about how the
optimizer does its job in the (hopefully rare) cases where the optimizer's
output is materially suboptimal.

My own experience some 15 years ago with DEC Rdb is a case in point. The
DEC optimizer was so good that, in most cases, it was simply not necessary
to think about how it worked. That allowed the query designer (and for that
matter the database designer) to focus on the other aspects of quality
without sacrificing much in the way of speed.


Consider a merge join: both inputs are
sorted, and then a single pass is made through each result--similar to an
old COBOL program that updates a master file from a transaction file. If
you don't understand what each step in an execution plan does and how it
works, then how can you possibly optimize a query?

When it becomes clear that an iterative approach could eliminate several
self-joins, thus decreasing the duration that locks are held, reducing the
impact on temporary storage and decreasing cpu utilization, then it might
be
a good idea to break the rules. You mentioned that switching from a
poorly
designed procedural solution to one that is set-based dropped the time it
took for the process to run from 10 hours to 30 minutes. That's great! I
merely suggest that reintroducing a cursor to eliminate the several
self-joins in your solution might further decrease the time it would take
for the process to run--without any negative impact on concurrency or
overall performance.


All of these comments should lead to one conclusion: one engages in this
sort of analysis when one is intent on writing a better optimizer, or when
coming up with a workaround in the case of a deficient optimizer. Not in
the case of using a good optimizer to good effect.



I would bet the process would run much faster than your solution. In
rare
cases, cursors can actually increase performance.
I don't think one can establish that for a certainty.


.

Relevant Pages