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

Your first view I would change differently depending on whether I used Firebird 1.5 or 2.1.

Firebird 1.5:
SELECT ...
FROM
Umpires U
join /*If you're interested in rows from U that has no
UB1 match, keep LEFT OUTER */
UmpireBadges UB1
ON
UB1.UmpID = U.UmpID
AND not exists(select * from UmpireBadges UB2
where UB2.UmpID = U.UmpID
and UB2.UmpBadgeDate > UB1.UmpBadgeDate)
...

Firebird 2.1:
WITH UmpMax(UmpID, UmpBadgeDate) AS
(SELECT UmpID, max(UmpBadgeDate)
FROM UmpireBadges
GROUP BY 1)
SELECT ...
FROM
Umpires U
join UmpMax UM
on U.UmpID = UM.UmpID
join /*If you're interested in rows from U that has no
UB1 match, use LEFT OUTER here and above*/
UmpireBadges UB1
ON UB1.UmpID = U.UmpID
AND UB1.UmpBadgeDate = UM.UmpBadgeDate
...

For your other view, I expect that the count you do is on the primary key, making it, well, unnecessary. However, this is one of the cases where LEFT OUTER JOIN can be useful as an alternative to NOT EXISTS:

VW_LASTALLOCDDATE:
Select
U.UmpID,
U.LastAllocdDate
From
Umpires U
left outer join
UmpireRosters UR
on
UR.UmpID = U.UmpID
left outer join
PrvdUmpRosterPairs PURP
on
PURP.UmpRosterID = UR.UmpRosterID
Where
PURP.PrvdUmpRstrPrID is null

UNION

select
U.UmpID,
Max(PD.PlayDayDate)
from
Umpires U
join
UmpireRosters UR
on
UR.UmpID = U.UmpID

join
PrvdUmpRosterPairs PURP
on
PURP.UmpRosterID = UR.UmpRosterID

join
PlayDays PD
on
PD.PlayDayID = PURP.PlayDayID
Group by
U.UmpID

HTH,
Set

-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Peter Bailey
Sent: 12. desember 2010 01:59
To: Firebird - support
Subject: [firebird-support] Re: V2.1.3 Faster than a speeding bullet


<http://groups.yahoo.com/group/firebird-support/message/111076;_ylc=X3oDMTJzcDhtMzRyBF9TAzk3MzU5NzE1BGdycElkAzI0NDI0MDYEZ3Jwc3BJZAMxNzA1MTE1Mzg2BG1zZ0lkAzExMTA3NgRzZWMDZG1zZwRzbGsDdm1zZwRzdGltZQMxMjkxNzIxOTgy>



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>


Mon Dec 6, 2010 11:15 pm (PST)



Hi Peter!

>From
> vw_XtnddUmpires vXU
>
>Where
> vXU.UmpID <> 0
>Order by
> vXU.FamilyName,
> vXU.GivenName,
> vXU.UmpID

No reason why this should have vastly different performance on Firebird
1.5 vs 2.1. Probably vw_XtnddUmpires is a view and the reason for the
time difference is partly due to the view definition. So, please show us
the view definition(s)
.
Confirming timings, (done in DBW), both done with Fetch All (1,656 rows)
V1.5: prepare 0.043 secs, processed 7 min 8.443 secs
V2.1: prepare 0.056 secs, processed 0.454 secs

Plans:
V1-5-1
Plan:
PLAN SORT (JOIN (JOIN (JOIN (JOIN (JOIN (VXU U NATURAL,VXU UB1 INDEX
(UQ_UMPIREBADGEDATE)),VXU B INDEX (PK_BADGES)),VXU G INDEX
(PK_GRADES)),VXU C INDEX (PK_CLUBS))SORT (JOIN (JOIN (VXU VLAD U
NATURAL,VXU VLAD UR INDEX (FK_UMPIRE_UMPROSTER)),VXU VLAD PURP INDEX
(FK_UMPROSTER_PRVDUMPRSTRPR)))
PLAN SORT (JOIN (JOIN (JOIN (VXU VLAD U NATURAL,VXU VLAD UR INDEX
(FK_UMPIRE_UMPROSTER)),VXU VLAD PURP INDEX
(FK_UMPROSTER_PRVDUMPRSTRPR)),VXU VLAD PD INDEX (PK_PLAYDAYS))))
PLAN )(VXU UMPIREBADGES INDEX (FK_UMPIRE_UMPBADGE))

V2-1-3
Plan:
PLAN (VXU UMPIREBADGES INDEX (FK_UMPIRE_UMPBADGE))
PLAN JOIN (JOIN (JOIN (JOIN (SORT (JOIN (VXU U NATURAL, VXU UB1 INDEX
(UQ_UMPIREBADGEDATE))), VXU B INDEX (PK_BADGES)), VXU G INDEX
(PK_GRADES)), VXU C INDEX (PK_CLUBS))JOIN (SORT (JOIN (VXU VLAD U INDEX
(PK_UMPIRES), VXU VLAD UR INDEX (FK_UMPIRE_UMPROSTER))), VXU VLAD PURP
INDEX (FK_UMPROSTER_PRVDUMPRSTRPR))
PLAN JOIN (JOIN (JOIN (VXU VLAD U ORDER PK_UMPIRES, VXU VLAD UR INDEX
(FK_UMPIRE_UMPROSTER)), VXU VLAD PURP INDEX
(FK_UMPROSTER_PRVDUMPRSTRPR)), VXU VLAD PD INDEX (PK_PLAYDAYS)))

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?

Details of views follow, (sorry, but this goes on a bit):
VW_XTNDDUMPIRES:

SELECT
U.UmpID,
U.FamilyName,
U.GivenName,
U.RegdClubID,
C.ClubCode,
UB1.UmpBadgeID,
UB1.UmpBadgedate,
B.BadgeCode,
UB1.UmpBadgeRating,
UB1.UmpBadgePanel,
UB1.UmpBadgeComment,
U.PrefdGradeID,
G.GradeCode,
U.BirthDate,
U.AddressLine1,
U.AddressLine2,
U.Suburb,
U.State,
U.PostCode,
U.PhoneHome,
U.PhoneWork,
U.PhoneMobile,
U.EMail,
vLAD.LastAllocdDate,
U.CreateStmp,
U.EditStmp,
U.UpdNo
FROM
Umpires U

left outer join
UmpireBadges UB1
ON
UB1.UmpID = U.UmpID
AND
UB1.UmpBadgeDate =
(
SELECT
Max(UmpBadgeDate)
FROM
UmpireBadges UB2
WHERE
UB2.UmpID = U.UmpID
)

left outer join
Badges B
ON
B.BadgeID = UB1.BadgeID

left outer join
Grades G
ON
G.GradeID = U.PrefdGradeID

left outer join
Clubs C
ON
C.ClubID = U.RegdClubID

left outer join
vw_LastAllocdDate vLAD
on
vLAD.UmpID = U.UmpID

VW_LASTALLOCDDATE:
Select
U.UmpID,
U.LastAllocdDate
From
Umpires U

left outer join
UmpireRosters UR
on
UR.UmpID = U.UmpID

left outer join
PrvdUmpRosterPairs PURP
on
PURP.UmpRosterID = UR.UmpRosterID
Group by
U.UmpID,
U.LastAllocdDate
Having
Count(PURP.PrvdUmpRstrPrID) = 0

UNION

select
U.UmpID,
Max(PD.PlayDayDate)
from
Umpires U

left outer join
UmpireRosters UR
on
UR.UmpID = U.UmpID

left outer join
PrvdUmpRosterPairs PURP
on
PURP.UmpRosterID = UR.UmpRosterID

left outer join
PlayDays PD
on
PD.PlayDayID = PURP.PlayDayID
Group by
U.UmpID
Having
Count(PURP.PrvdUmpRstrPrID) <> 0

That's it! Hope to hear a bit more.
Peter