请教大神如何优化这段SQL，谢谢

[熊德华]

select a.*,c.name Contract_TypeName,d.ecc_orga_name UnitName,d.ecc_orga_name VehicleTeamName
,e.name SettlementName,f.Vehicle_Number VehicleNo,g.Vehicle_Number TrailerNo
,case when a.Contract_Status=10 then '初始创建'when a.Contract_Status=20 then '合同作废'
when a.Contract_Status=30 then '审核完毕' end StatusName
,case when a.Old_Contract_KeyId is null
and a.Contract_Status = 30
and not exists(select * from Contract_Income_Contract where Old_Contract_KeyId = a.keyid and Is_Delete=0) then 1 else 0 end IsDifferAdjust
from Contract_InCome_Contract a
left join Dic_Contract_Type c on a.Dic_Contract_Type=c.keyid
left join Dic_Organization d on a.PartyA=d.keyid
left join Dic_Customer_Trans e on a.PartyB=e.keyid
left join Device_Vehicle f on a.fk_vehicle=f.keyid
left join Device_Vehicle g on a.fk_trailer=g.keyid
where a.keyid is not null

红色部分明显影响了查询速度，有更好的优化方法吗

[碧水幽幽泉]

子查询换成表连接，再试试

select a.*,c.name Contract_TypeName,d.ecc_orga_name UnitName,d.ecc_orga_name VehicleTeamName
            ,e.name SettlementName,f.Vehicle_Number VehicleNo,g.Vehicle_Number TrailerNo
            ,case when a.Contract_Status=10 then '初始创建'when a.Contract_Status=20 then '合同作废'
            when a.Contract_Status=30 then '审核完毕' end StatusName
	case when a.Old_Contract_KeyId is null and a.Contract_Status = 30 and h.Old_Contract_KeyId is null then 1 else 0 end IsDifferAdjust
            from Contract_InCome_Contract   a
            left join         Dic_Contract_Type    c on a.Dic_Contract_Type=c.keyid
            left join         Dic_Organization      d on a.PartyA=d.keyid 
            left join         Dic_Customer_Trans e on a.PartyB=e.keyid 
            left join        Device_Vehicle           f  on a.fk_vehicle=f.keyid
            left join        Device_Vehicle          g  on a.fk_trailer=g.keyid 
			left join Contract_Income_Contract h on h.Old_Contract_KeyId = a.keyid and h.Is_Delete = 0
 where a.keyid is not null

[碧水幽幽泉]

子查询换成表连接，再试试

select a.*,c.name Contract_TypeName,d.ecc_orga_name UnitName,d.ecc_orga_name VehicleTeamName
            ,e.name SettlementName,f.Vehicle_Number VehicleNo,g.Vehicle_Number TrailerNo
            ,case when a.Contract_Status=10 then '初始创建'when a.Contract_Status=20 then '合同作废'
            when a.Contract_Status=30 then '审核完毕' end StatusName
	case when a.Old_Contract_KeyId is null and a.Contract_Status = 30 and h.Old_Contract_KeyId is null then 1 else 0 end IsDifferAdjust
            from Contract_InCome_Contract   a
            left join         Dic_Contract_Type    c on a.Dic_Contract_Type=c.keyid
            left join         Dic_Organization      d on a.PartyA=d.keyid 
            left join         Dic_Customer_Trans e on a.PartyB=e.keyid 
            left join        Device_Vehicle           f  on a.fk_vehicle=f.keyid
            left join        Device_Vehicle          g  on a.fk_trailer=g.keyid 
			left join Contract_Income_Contract h on h.Old_Contract_KeyId = a.keyid and h.Is_Delete = 0
 where a.keyid is not null