Thanx 4 all the help but its resolved n i get a dozen helps every day n need it 2 stop. U guys rock n ty n thanx 2 u im ok but plz. Stop all the e~mails. Happy holidays ! ! ! ! !

On Sat Dec 11th, 2010 7:58 PM EST Peter Bailey wrote:

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