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

On 29 avr, 20:33, "Brian Selzer" <b..._at_selzer-software.com> wrote:
> "Cimode" <cim..._at_hotmail.com> wrote in message
>
> news:1177863186.258396.136950_at_y5g2000hsa.googlegroups.com...
>
> > On 29 avr, 16:21, "Brian Selzer" <b..._at_selzer-software.com> 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. 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?
The issues I am trying here are not implementation specific. See the fundamental questions underlying. I can not how

> 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.
I can not say I disagree with what you are saying but I still think you are missing the point of the example.

As a practionner who used intensively both procedural and set based methods, on may manage to get response time to be faster using cursors (that still need s to be established) but that does not mean that performance as a whole is improved.

Self joins poor implementations is a known direct image systems limitation. That is the issue I was trying to underline here. Discussing tuning on a specific SQL DBMS implementation is not the point of the thread nor the point of the NG. THe main point here is to see if linear interpolation could be a way of handling systematically missing numeric/datetime data...

[Snipped]
> That has also been my experience--almost all of the time. Cursors should
> only be used as a last resort, and definitely not by the uninitiated. A
> thorough, intimate understanding of not only the database, but also the
> underlying implementation should be a prerequisite for breaking the rules.

> > ...And I have not found any satifying cursor
> > based solution....
>
> It should always put a bad taste in your mouth, but that doesn't mean you
> should dismiss it without due consideration.
See above...

> > ...Often the use of cursor and (procedural) dictates
> > unacceptable tarde off where evrything is given up for reponse time
> > sake...
>
> I agree. But eliminating self-joins doesn't trade anything off. Quite the
> opposite: it can improve both throughput and concurrency.
An additional hack in RM perspective.

[Snipped]
> > Speaking of optimizers in the case of direct image systems is like
> > asking a cow to run a race. Only a TRDBMS that would correctly
> > separate logicala and physical could inherentlyy allow optimization.
>
> Too bad none exists today.
Yep. And none will exist if our thinking is made in function of existing direct image optimizers. It is a duty for practitionners to identify the limitation of a specific implementation and point it out. Discussing fundamental theory according to implementation limitation simply is a dead end I don't want to get into. Doing my job is one thing, thinking freely and knowing what's wrong and what's right is another.

Regards... Received on Sun Apr 29 2007 - 21:07:08 CEST