Re: A new proof of the superiority of set oriented approaches: numerical/time serie linear interpolation
Date: Mon, 30 Apr 2007 02:18:42 GMT
Message-ID: <6mcZh.29368$PV3.313454_at_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 Mon Apr 30 2007 - 04:18:42 CEST