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

From: Bob Badour <bbadour_at_pei.sympatico.ca>
Date: Mon, 30 Apr 2007 02:18:42 GMT
Message-ID: <6mcZh.29368$PV3.313454@ursa-nb00s0.nbnet.nb.ca>

David Cressey wrote:

> "Brian Selzer" <brian_at_selzer-software.com> wrote in message
> news:jy5Zh.4247$uJ6.3542_at_newssvr17.news.prodigy.net...
>

>>"Cimode" <cimode_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.

>
> 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.

David, please don't be too dismayed when I say: Lately, I have been liking your answers better and better. Received on Sun Apr 29 2007 - 21:18:42 CDT