Re: Self Joins and optimization

"David Cressey" <cressey73_at_verizon.net> wrote in message news:ZE%_h.163$D52.68_at_trndny04...

I'm putting my response to post in the other thread here, since it appears a more appropriate place.

"David Cressey" <cressey73_at_verizon.net> wrote in message news:dL__h.1686$XZ2.1572_at_trndny01...
>

[snip]
> 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?
>

No. I certainly don't think he was lying. I've experienced similar results. Used poorly, cursors can change big-O from O(n) to O(n^2). An extra database access within the fetch loop causes the iterative solution to operate similar to a loop join optimizer step. There is no question that a cursor that hits the database several times within the fetch loop will perform abysmally for large n.

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

I didn't dismiss his conclusion altogether, but I think his example supports an argument against a poor use of a cursor, not every possible use.

[snip] Received on Sat May 05 2007 - 16:51:36 CEST