<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


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