Re: Self Joins and optimization
From: Bob Badour <bbadour_at_pei.sympatico.ca>
Date: Sat, 12 May 2007 09:22:30 -0300
Message-ID: <4645b145$0$4013$9a566e8b_at_news.aliant.net>
>
>
>
> at
>
>
> but
>
>
> that
>
>
>
> Brian, don't take this the wrong way, either.
>
> I understand that you solved this problem some time ago, and that your
> solution is satisfactory to you. That's not the question I'm addressing at
> all. What I'm interested in exploring is your assertion of the
> non-existence of a set oriented solution to the problem, where the set
> oriented solution runs at approximately the same speed as your sequential
> processing solution.
>
> I'm not convinced that multiple self joins are in fact required in order to
> produce a set oriented solution. In particular, I'm interested in finding
> a solution that bypasses one of the steps in the set oriented solution you
> posted at my request. It's the step where each TX ON is matched with its
> corresponding TX OFF. That step seems unnecessary to me, although I can't
> prove it, yet.
>
> If there is any point at issue between you and me, its your assertion, in
> the thread that gave rise to this thread, that Cimode would have been
> better off using cursors in his approach to interpolation. I doubt that
> assertion, and I don't think your example, even if it stands up, proves
> that assertion.
>
> Your assertion has enormous implications, both practical and theoretical,
> if it turns out to be true. I can comment better on the practical
> implications than on the theoretical ones.
>
> In practice, many database consultants and/or developers have made a minor
> industry out of weeding cursor oriented code out of database applications
> and substituing well engineered set oriented soultions. If your assertion
> is correct, nearly all of these improvements were substantially less than
> optimal, at least from a speed point of view. For example, December 28,
> 2006 Kevin Kirkpatrick (I think) outlined a case where he wrote about 300
> lines of SQL that did, in 45 minutes, what would have taken about 300
> hours using the procedural solution he replaced. Kevin's example is more
> startling than yours. And, IMO, more convincing.
>
> I hasten to add that there ARE cases where I would adopt a procedural
> approach, but I would NOT make the performance claims that you made. If
> you're a practical person, you have more tools than just a hammer in your
> tool kit.
>
> I'll leave the theoretical implications of your assertion that cursors are
> better to the theoreticians in this ng.
Date: Sat, 12 May 2007 09:22:30 -0300
Message-ID: <4645b145$0$4013$9a566e8b_at_news.aliant.net>
David Cressey wrote:
> "Brian Selzer" <brian_at_selzer-software.com> wrote in message
> news:ej81i.8350$rO7.3542_at_newssvr25.news.prodigy.net...
>
>>[big snip]
>
>
>>Don't take this the wrong way, I understand that this is a first attempt
>
> at
>
>>a very complicated problem (I spent several weeks on it back in 2002.),
>
> but
>
>>I really don't think the above solution is a move in the right direction. >>In fact, it is very similar to what I moved away from. It is essentially >>the same file layout that existed in the old DOS-based Btrieve database
>
> that
>
>>I replaced, except that ....
>
>
> Brian, don't take this the wrong way, either.
>
> I understand that you solved this problem some time ago, and that your
> solution is satisfactory to you. That's not the question I'm addressing at
> all. What I'm interested in exploring is your assertion of the
> non-existence of a set oriented solution to the problem, where the set
> oriented solution runs at approximately the same speed as your sequential
> processing solution.
>
> I'm not convinced that multiple self joins are in fact required in order to
> produce a set oriented solution. In particular, I'm interested in finding
> a solution that bypasses one of the steps in the set oriented solution you
> posted at my request. It's the step where each TX ON is matched with its
> corresponding TX OFF. That step seems unnecessary to me, although I can't
> prove it, yet.
>
> If there is any point at issue between you and me, its your assertion, in
> the thread that gave rise to this thread, that Cimode would have been
> better off using cursors in his approach to interpolation. I doubt that
> assertion, and I don't think your example, even if it stands up, proves
> that assertion.
>
> Your assertion has enormous implications, both practical and theoretical,
> if it turns out to be true. I can comment better on the practical
> implications than on the theoretical ones.
>
> In practice, many database consultants and/or developers have made a minor
> industry out of weeding cursor oriented code out of database applications
> and substituing well engineered set oriented soultions. If your assertion
> is correct, nearly all of these improvements were substantially less than
> optimal, at least from a speed point of view. For example, December 28,
> 2006 Kevin Kirkpatrick (I think) outlined a case where he wrote about 300
> lines of SQL that did, in 45 minutes, what would have taken about 300
> hours using the procedural solution he replaced. Kevin's example is more
> startling than yours. And, IMO, more convincing.
>
> I hasten to add that there ARE cases where I would adopt a procedural
> approach, but I would NOT make the performance claims that you made. If
> you're a practical person, you have more tools than just a hammer in your
> tool kit.
>
> I'll leave the theoretical implications of your assertion that cursors are
> better to the theoreticians in this ng.