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
Table definition

