I would have thought exists would be faster than an inner join.
When you run the query in SSMS and also select execution plan or estimate execution plan, are there any index recommendations?
I would actually start writing the query using only exists:
SELECT pr.partid
from parts.Nop_Part pr with(nolock)
where exists(select *
from extractreports.dbo.RequiredPartsPL rp with(nolock)
where rp.partid=pr.partid
and exists(select *
from Parts.Nop_PackageAttribute pa WITH(NOLOCK)
where pa.packageid=pr.packageid
and exists(select *
from dbo.Core_DataDefinitionDetails dd WITH(NOLOCK)
where pa.[Key] = dd.ColumnNumber
and exists(select *
from extractreports.dbo.getrelatedkeyandvaluepackage g
where g.Featureid=dd.acceptedvalueid
and g.valueid=pa.value)
)
)
)