Re: Self Joins and optimization

"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. Received on Sat May 12 2007 - 14:14:35 CEST