First of all, you're looking at the percentages; What is the cost of each operation compared to others in this query.
Since it's a percentage, the sum will always be 100 so if one percentage is reduced another gets higher. So looking only at the percentages is a wild goose chase.
The obvious question: Is the query slow and causing problems? If not, no need to go further.
If the query is causing problems you should consider which plan would produce the result as easy as possible. Since we don't have your database nor your data it's impossible to do this on your behalf but some food for thought
- What is the conditions that restricts the most is it
(FT.MappingDoneFlag=0 and FT.PartLevel=0)
or
d.partid is null
. Which ever is the most restrictive, place that table first. Looking at the join types, probably
ft
since
d
is left join. Actually this is not always necessary but helps the mindset
- If the condition
(FT.MappingDoneFlag=0 and FT.PartLevel=0)
fetches only small portion of rows from the table, ensure that those columns are indexed, if possible in a single index having the most restrictive column first
- Ensure that
Parts.Nop_Part.PartID
is indexed
- Ensure that
Parts.Nop_PartsFamilyAttribute.PartFamilyID
is indexed preferably along with
[Key]
as the second column in the index
- Ensure that
ExtractReports.dbo.TPls.ZPLID
is indexed
- Ensure that
ExtractReports.dbo.TPLNewData.PartID
is indexed with
CodeTypeID
- Ensure that
[ExtractReports].[dbo].[TradeCodesDelete].partid
is indexed along with
codetypeid
and
partlevel
. Again the most restrictive column first
Having that said you also have a peculiar condition. You make a left join to
[ExtractReports].[dbo].[TradeCodesDelete]
with
d.partid=FT.partid and d.codetypeid=FT.codetypeid and d.partlevel=0
however in WHERE clause you explicitly state that partid must be null
d.partid is null
So
d.partid
will never be equal to
FT.partid
, is this intentional?