Without any PLANs or knowledge about your tables/indexes, it is
pretty difficult to guess what is wrong and hence - how to improve
anything. However, guessing that ProductKey is the primary key of
Product, why don't you simplify to something like

Select p.ProductKey, p.Name, p.VendorItemNumber, pt.ProductTypeName,
vc.VendorName
From TheaterVendorContractHeader tvch
Join VendorCorporate vc
on vc.VendorCorporateKey = tvch.VendorCorporateKey
Join Product p
on p.VendorCorporateKey = tvch.VendorCorporateKey
Join ProductSubTypes pst
on pst.ProductSubTypeKey = p.ProductSubTypeKey
Join ProductTypes pt
on pt.ProductTypeKey = pst.ProductTypeKey
Join TheaterProduct tp
on tp.TheaterCorporateKey = tvch.TheaterCorporateKey
And tp.ProductKey = p.ProductKey
Left Join TheaterVendorContractDetail tvcd
on tvcd.ProductKey = p.ProductKey
And tvcd.ContractKey = tvch.ContractKey
where tvch.ContractKey = 498
And tvcd.ContractDetailKey is null
And p.DiscontinuedYN = 'N'

I'm too tired to trust this to be an equal query as your original
query, but try it and report back here the results (including the
PLAN if the query gets the right result).

HTH,
Set

--- In firebird-support@yahoogroups.com, "slalom91" wrote:
>
> I am trying to produce a list of items which excludes items which
> already exist within a certain table. The entire list is about
> 40,000 items and the target table contains about 32,000 of the
> existing 40,000. I have tried various different techniques
including
> using "not in", "not exists", and a derived table as indicated in
the
> following statement...
>
> Select p.ProductKey, p.Name, p.VendorItemNumber,
pt.ProductTypeName,
> vc.VendorName
> From
>
> (Select pe.ProductKey, tvcde.ContractDetailKey
> From Product pe
> Inner Join TheaterVendorContractHeader tvche on (tvche.ContractKey
=
> 498 And pe.VendorCorporateKey = tvche.VendorCorporateKey)
> Left Outer Join TheaterVendorContractDetail tvcde on
> (tvcde.ProductKey = pe.ProductKey And tvcde.ContractKey =
> tvche.ContractKey)
> Where tvcde.ContractDetailKey is null) dt
>
> Inner Join TheaterVendorContractHeader tvch on (tvch.ContractKey =
> 498)
> Inner Join VendorCorporate vc on (vc.VendorCorporateKey =
> tvch.VendorCorporateKey)
> Inner Join Product p on (p.ProductKey = dt.ProductKey And
> dt.ContractDetailKey is null And p.DiscontinuedYN = 'N')
> Inner Join ProductSubTypes pst on (pst.ProductSubTypeKey =
> p.ProductSubTypeKey)
> Inner Join ProductTypes pt on (pt.ProductTypeKey =
pst.ProductTypeKey)
> Inner Join TheaterProduct tp on (tp.TheaterCorporateKey =
> tvch.TheaterCorporateKey and tp.ProductKey = p.ProductKey)
>
> the result set takes upwards of 60 seconds to produce results. The
> statistics on the query indicate that all reads are indexed and the
> most reads from any particular table are 85,000.
>
> Does anyone have any suggestions on how to produce results faster
for
> what seems to be a fairly standard request?