Indexed query not optimized enough

Viewed 78

I am trying to optimize the following query and only been able to chip of 10 seconds. I have tried to apply non clustered index on foreign key table columns that are involved in the joins. Could somebody let me know what I could do to make the query faster ?

select
    c.companyId, 
    c.companyName, 
    c.city, 
    cst.companyStatusTypeName,
    ct.companyTypeName,
    sc.simpleIndustryDescription,
    c.officeFaxValue, 
    c.officePhoneValue, 
    c.streetAddress,
    c.streetAddress2, 
    c.yearFounded,
    c.zipCode,
    c.webpage,
    rep.templateTypeName, 
    rcg.isoCountry2,
    rs.abbreviation
from ciqCompany c
    inner join ciqCompanyStatusType cst on cst.companystatustypeid = c.companystatustypeid
    inner join ciqCompanyType ct on ct.companyTypeId = c.companyTypeId
    inner join refReportingTemplateType rep on rep.templateTypeId = c.reportingtemplateTypeId
    inner join refCountryGeo rcg on c.countryId = rcg.countryId
    inner join refState rs on rs.stateId = c.stateId
    inner join ciqSimpleIndustry sc on sc.simpleIndustryId = c.simpleIndustryId

Execution Plan

enter image description here

Table definition

enter image description here

2 Answers
Related