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

David Cressey wrote:

> "Brian Selzer" <brian_at_selzer-software.com> wrote in message
> news:8kY_h.7025$2v1.1573_at_newssvr14.news.prodigy.net...
>

>>"David Cressey" <cressey73_at_verizon.net> wrote in message
>>news:QvJ_h.11$rk5.8_at_trndny06...
>>
>>>"Brian Selzer" <brian_at_selzer-software.com> wrote in message
>>>news:eFF_h.7202$rO7.3983_at_newssvr25.news.prodigy.net...
>>>
>>>>"Cimode" <cimode_at_hotmail.com> wrote in message
>>>>news:1178260494.811737.293620_at_y5g2000hsa.googlegroups.com...
>>>>
>>>>>On 4 mai, 02:35, "Brian Selzer" <b..._at_selzer-software.com> wrote:
>>>>>
>>>>>>"Cimode" <cim..._at_hotmail.com> wrote in message
>>>>>>
>>>>>>news:1178221012.371056.145700_at_u30g2000hsc.googlegroups.com...
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>>On 3 mai, 21:04, "Brian Selzer" <b..._at_selzer-software.com> wrote:
>>>>>>>
>>>>>>>>"Cimode" <cim..._at_hotmail.com> wrote in message
>>>>>>
>>>>>>>>>On 3 mai, 18:45, "David Cressey" <cresse..._at_verizon.net> wrote:
>>>>>>>>>
>>>>>>>>>>"Bob Badour" <bbad..._at_pei.sympatico.ca> wrote in message
>>>>>>>>>
>>>>>>>>>[Snipped]
>>>>>>>>>
>>>>>>>>>>Maybe I cut him too much slack.  If the past is any guide,
>>>>>>>>>>optimizers
>>>>>>>>>>will
>>>>>>>>>>get better, and Brian won't.  It's instructive that Both

>>>and
>>>
>>>>>>>>>>Kevin
>>>>>>>>>>provide actual data, while Brian merely states his claim.
>>>>>>>>>
>>>>>>>>>For my defense, I am trying to trigge questions here.  It would
>>>>>>>>>be
>>>>>>>>>illusional ton my part to hope to get definite answers.
>>>>>>
>>>>>>>>>>But even if he wins this particular race,  he will not

>>>me
>>>
>>>>>>>>>>that
>>>>>>>>>>cursors are the way to go.  I've seen too much evidence to the
>>>>>>>>>>contrary.
>>>>>>>>>
>>>>>>>>>You will soon realize that Brian mainly tries to convince
>>>>>>>>>himself.
>>>>>>>>>Getting out of procedural mindset is nothing but *natural*
>>>
>>>instinct.
>>>
>>>>>>>>Could you please elaborate?  I don't need convincing.  Sometimes

>>>dip
>>>
>>>>>>>>into
>>>>>>>>waters that a bit too deep and end up chewing on my foot, but

>>>>>>>>I
>>>>>>>>know
>>>>>>>>I'm right, I don't need convincing, and I certainly don't try to
>>>>>>>>convince
>>>>>>>>myself.
>>>>>>>
>>>>>>>You are in denial.  Don't you realize that at least 4 people have
>>>
>>>told
>>>
>>>>>>>you the exact opposite of your claims and you are refusing to

>>>>>>>they may just be right.  Is 'nt there a slight doubt in your mind
>>>
>>>that
>>>
>>>>>>>you may be missing something ? that some aspect of fundamental
>>>>>>>theory
>>>>>>>may have elluded you?
>>>>>>
>>>>>>There is an understandable but unreasonable bias against cursors in
>>>
>>>this
>>>
>>>>>>group.  I wouldn't be surprised if 15 people told me I was full of

>>>>>>since
>>>>>>most of the time, cursors are used incorrectly or where a set-based
>>>>>>solution
>>>>>>would perform better, and once someone has had a bad experience,

>>>>>>difficult to set aside emotionalism and examine a similar solution
>>>>>>dispassionately.
>>>>>
>>>>>Have you considered the idea that some people here were once in your
>>>>>exact shoes and they once may had the unshakable belief in what you
>>>>>just wrote.
>>>>>
>>>>
>>>>I only know what I have experienced, and to me it makes sense.
>>>
>>>This raises the following question:  does "what you have experienced"
>>>include what you have learned from other people?  If not,  what will it
>>>take
>>>to persuade you that some of the writers in this newgroup are onto
>>>something
>>>you might find worth while.
>>>
>>
>>I admit that I am more willing to trust my own personal experience than

>>of others.  And even more so when I have a logical rationale to back it

>>What will it take?  In this case, hard evidence.
>>
>>
>>>>Every
>>>>optimizer step is implemented in the engine by using some form of
>>>
>>>iteration.
>>>
>>>>What I'm suggesting effectively replaces several optimizer steps with a
>>>>single user-defined one that makes a single pass through the
>>>>data--similar
>>>>to what a step that computes aggregates does.
>>>
>>>I think you are greatly underestimating what a good optimizer can do.

>>>judging from a limited understanding of the Rdb/VMS optimizer,  going

>>>to 1994.  I can only assume that things have gotten better since then.
>>>Even
>>>if they haven't gotten better, I'm sure they haven't gotten worse.
>>>
>>
>>When you realize that you have to squeeze a batch process that's taking

>>30 hours to run into a 2-hour window...when you've tweaked, rewritten, and
>>rewritten again a set-based solution, spending weeks pouring over

>>plans and traces...when you've tried indexes, optimizer hints, forced
>>plans--even thrown hardware at it, and still have a solution that performs
>>better, but not good enough to meet requirements...when you've thrown up
>>your hands and polished your resume...you gain a far too intimate
>>understanding of what an optimizer can and cannot do.  The most efficient
>>set-based solution I could come up with still took 7 hours to run--even
>>after adding hardware.  But after introducing 5 cursors at key points in

>>process, the execution time dropped to just over an hour.  In three
>>instances a cursor replaced a complex section of an execution plan

>>from multiple self-joins; in another instance, two cursors executing
>>simultaneously replaced a complex section of an execution plan resulting
>>from several correlated subqueries.

What can one say? Selzer's rant speaks to a shitty optimizer. It condemns a specific un-named product and nothing else.

> And yet, someone else in this newgroup (not me) reported that he took a
> batch job that was taking 45 hours using legacy code, rewrote it using
> about 300 lines of SQL, and got something to run in about 45 minutes. Do
> you think he was lying? Do you think he was using cursors?

I have done lots of optimization. I have never achieved a gain from using a cursor, and I have achieved numerous huge big-O improvements by getting rid of cursors. Like taking an O(n^5) cursor algorithm and replacing it with a single select statement involving multiple joins, quantification and correlated subqueries that executed in O(n) -- and that was using a small footprint embedded dbms one could buy for a couple hundred dollars.

Within a few moments of inheriting the cursor based code mentioned above, I recognized the cursor solution would not scale and I changed it. My boss was suitably impressed when he asked me to look into a performance problem found in the field, and I replied that I had already found it and fixed it.

> I also have a long history of personal experience to draw on, including
> optimizer workarounds. But I am not as dismissive of what others report as
> you are. Cimode is offering as close to "hard evidence" as anything that
> can be squeezed through the narrow pipeline of a newgroup. And yet you've
> dismissed his conclusion out of hand, without really putting it to the
> test.

Selzer is long on opinion and short on facts or sense.

> (Cimode, I realize that your main point was about interpolation, and not
> about performance. But it seems to me that, as long as someone dismisses
> your inputs because "he can make it run faster by manual algorithm
> selection" that your flowers are wasting their freshness on the desert
> air.)
Received on Sat May 05 2007 - 15:06:51 CEST