Hi all,

For whom is interested in the solution:
I have been able to bring down the query time even further from 2.3 secs to
0.19 secs by noticing that in the query Plan, for a connect table that
contains two ID's the indexes on these two ID's (which are the indexes that
come with the foreign keys) were not used. In my opinion this is a strange
decisson of the SQL Optimizer.
At adding an aditional index that contains both ID fields, the Optimizer
starts to use this index and the query time is drastically reduced

regards
Tanno



"Helen Borrie" <helebor@...> wrote in message
news:5.1.0.14.2.20021103082548.076494d0@......

> At 04:57 PM 02-11-02 +0100, Tanno Vorenkamp wrote:
> >Hi All,
> >
> >The following select query takes 4 seconds
> >Even though all ID's used in the Joins and all fields used in the where
> >conditions have an index
> >
> >Does anyone know how to improve this ?
> >
> >SELECT
> >t.TenderID,
> >l.OrganisationID AS SellerOrgID,
> >o1.Name AS SellerOrgName,
> >tb.Organisationid as BidOrgID,
> >o2.Name AS BidOrgName,
> >l.Captive,T.EndDate,
> >c.CarID,c.Licenseno,
> >l.Price,
> >tlb.BidPrice as CarBidPrice,
> >c.Brand,c.Model,c.CarType,c.Fuel,c.CarDate,
> >c.Kilometers from
> >Tender t join TenderLot tl on t.Tenderid = tl.Tenderid
> >join Lot l on tl.lotid = l.lotid
> >join Organisation o1 on l.organisationid = o1.organisationid
> >join Car c on l.carid = c.carid
> >left outer join Tenderbid tb
> >on ((t.tenderid = tb.tenderid) and (tb.highestbid = 1))
> >left outer join Organisation o2
> > on tb.organisationid = o2.organisationid
> >left outer join Tenderlotbids tlb on ((l.lotid = tlb.lotid)
> > and (tlb.bidorgid = tb.organisationid))
> >WHERE
> >(L.LotState = 3
> >AND l.OrganisationID = 1598
> >AND T.EndDate >= 37550
> >AND L.Terminated = 0)
> >
> >The Interbase Query Plan is
> >
> >OIN (JOIN (JOIN (JOIN (C NATURAL,L INDEX
> >(I_LOT_LOTSTATE,RDB$FOREIGN67,RDB$FOREIGN49),O1 INDEX
> >(I_ORGANISATION_ORGTYPE),TL INDEX (RDB$FOREIGN81),T INDEX
> >(RDB$PRIMARY28,I_TENDER_ENDDATE)),TB INDEX
> >(I_TENDERBID_HIGHESTBID,RDB$FOREIGN79)),O2 INDEX
> >(I_ORGANISATION_ORGTYPE)),TLB INDEX (RDB$FOREIGN10,RDB$FOREIGN13))
>
> Tanno,
>
> Look at the index I_ORGANISATION_ORGTYPE on your Organisation table. If

it

> has but few possible values throughout the table then you will do better

by

> dropping that index, because of low selectivity.
>
> heLen
>
>
>
> To unsubscribe from this group, send an email to:
> ib-support-unsubscribe@egroups.com
>
>
>
> Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
>
>
>