Posted by: "Svein Erling Tysvær" svein.erling.tysvaer@...
<mailto:svein.erling.tysvaer@...?Subject=%20Re%3A%20V2%2E1%2E3%20Faster%20than%20a%20speeding%20bullet>
svein_erling <http://profiles.yahoo.com/svein_erling>


Sun Dec 12, 2010 11:51 pm (PST)

>Set, you are correct in your deduction that the Select is accessing
>View/s ... but why would that make such a big difference between the two
>embedded server versions?

Well, the original sql you showed us was so simple that I couldn't
believe the difference to be as big as you reported. Your views,
however, is complex enough to confuse me and I have no problem believing
that some optimization in Firebird 2.5 can have influenced the result
you're getting (though I've no idea what is the difference).

I do think your views look overly complicated regarding a couple of
things. Below is how I would have changed them, and one of the changes
(I don't know which) might improve Firebird 1.5 (though test it, I might
be wrong in some assumptions so they might give a different result).
Generally, I find that you use LEFT OUTER JOIN all the time. Normally,
INNER JOIN is preferable and LEFT OUTER JOIN should be used only when
needed. Normally, that is when you're interested in returning rows that
doesn't have any match in the right table, but there are a few other
less common situations where outer joins are appropriate as well.

Hi Set

Thanks for your further comments.

After doing some work in DBW I can see that the immediate cause of the
lengthy processing time in V1.5 is the embedded View,
(vw_LastAllocdDate), within the main View.

I attempted to reduce the views down to plain old SQL code, but,
unfortunately the view vw_LastAllocdDate contains a Union. And, in a
subquery, Unions were not allowed until V2.1.
I also attempted to investigate whether the difference between V1.5 and
V2.1 was simple due to better optimisation, ie., a better plan. This
went nowhere as at least one part of the V2.1 plan was not accepted by
the V1.5 dll.

All a bit frustrating! Still, I take on board your comments about Left
Inner Join vs Inner Join.
As to the speed aspect ... I am in the process of moving to V2.1 and
then to V2.5 so that will go away.
This query to the Support list has also brought to light that a
relatively small change to the database design will completely remove
the problem.

Thanks again.
Peter


[Non-text portions of this message have been removed]