Tuning query with multiple joins

Question

i have this query .. 214 Execution/min , 44.42 Avg CPU(ms) is there a way to make it much faster

SELECT        P.Id id0,
              P.ProgramId ProgramId1,
              P.ProgramName ProgramName2,
              P.ProgramLevel ProgramLevel3,
              P.Department Department4,
              P.Track Track5,
              P.AcademicYear AcademicYear6,
              P.StartTerm StartTerm7,
              P.Delivery Delivery8,
              P.Fee Fee9,
              P.City City10,
              P.STATE State11,
              P.StartDate StartDate12,
              P.Deadline Deadline13,
              P.DeadlineDisplay DeadlineDisplay14,
              P.ProgramType ProgramType15,
              O.Id as OrganizationId16,
              O.NAME OrganizationName17,
              P.ApplicationType ApplicationType18,
              P.Concentration Concentration19,
              P.ZipCode ZipCode20,
              P.Campus Campus21,
              P.WADisplayName WADisplayName22,
              P.UpdatedDate updateDate23,
              AF.Id InstanceId24,
              RD.Id stateId33
INTO #TempGetFullProgramSelectionInfo            
FROM    unicas_config..applicationForm AF  
  INNER JOIN UNICAS_CONFIG.. AcademicInstitution AI
           ON AF.casid=AI.casid 
  INNER JOIN UNICAS_CONFIG..Organization O 
           ON O.academicInstitutionid=AI.id   
  INNER JOIN UNICAS_CONFIG..AssociationOrg asOrg
           ON asOrg.FormId=AF.id
  INNER JOIN  UNICAS_CONFIG..Program P
           ON P.AssociationOrgId=asOrg.Id and asOrg.OrganizationId=O.id AND AF.Id = 6286
  INNER JOIN  unicas_config..ReferenceData RD 
                 ON P.STATE = RD.ValueId AND RD.ValueAbbr ='US'
  INNER JOIN  unicas_config..ReferenceDataSet RS  
           ON RD.ReferenceSetId = RS.SetId AND RS.NAME = 'LK_States'

Now this if i split to 2 queries

select RD.ValueId, RD.id into #temp1
from   unicas_config..ReferenceData RD
INNER JOIN  unicas_config..ReferenceDataSet RS   ON RD.ReferenceSetId= RS.SetId AND RS.NAME= 'LK_States'
where RD.ValueAbbr='US';


SELECT        P.Id id0,
              P.ProgramId ProgramId1,
              P.ProgramName ProgramName2,
              P.ProgramLevel ProgramLevel3,
              P.Department Department4,
              P.Track Track5,
              P.AcademicYear AcademicYear6,
              P.StartTerm StartTerm7,
              P.Delivery Delivery8,
              P.Fee Fee9,
              P.City City10,
              P.STATE State11,
              P.StartDate StartDate12,
              P.Deadline Deadline13,
              P.DeadlineDisplay DeadlineDisplay14,
              P.ProgramType ProgramType15,
              O.Id as OrganizationId16,
              O.NAME OrganizationName17,
              P.ApplicationType ApplicationType18,
              P.Concentration Concentration19,
              P.ZipCode ZipCode20,
              P.Campus Campus21,
              P.WADisplayName WADisplayName22,
              P.UpdatedDate updateDate23,
              AF.Id InstanceId24,
              RD.Id stateId33
INTO #TempGetFullProgramSelectionInfo            
FROM    unicas_config..applicationForm AF  
  INNER JOIN UNICAS_CONFIG.. AcademicInstitution AI
           ON AF.casid=AI.casid 
  INNER JOIN UNICAS_CONFIG..Organization O 
           ON O.academicInstitutionid=AI.id   
  INNER JOIN UNICAS_CONFIG..AssociationOrg asOrg
           ON asOrg.FormId=AF.id
  INNER JOIN  UNICAS_CONFIG..Program P
           ON P.AssociationOrgId=asOrg.Id and asOrg.OrganizationId=O.id AND AF.Id = 6286
  INNER JOIN    #temp1 RD ON P.STATE= RD.ValueId;

using the query recommended by Frisbee

Using Hash join

Answer 1

Give this a try
You have some (potentially) misplaced join conditions

SELECT        P.Id id0,
              P.ProgramId ProgramId1,
              P.ProgramName ProgramName2,
              P.ProgramLevel ProgramLevel3,
              P.Department Department4,
              P.Track Track5,
              P.AcademicYear AcademicYear6,
              P.StartTerm StartTerm7,
              P.Delivery Delivery8,
              P.Fee Fee9,
              P.City City10,
              P.STATE State11,
              P.StartDate StartDate12,
              P.Deadline Deadline13,
              P.DeadlineDisplay DeadlineDisplay14,
              P.ProgramType ProgramType15,
              O.Id as OrganizationId16,
              O.NAME OrganizationName17,
              P.ApplicationType ApplicationType18,
              P.Concentration Concentration19,
              P.ZipCode ZipCode20,
              P.Campus Campus21,
              P.WADisplayName WADisplayName22,
              P.UpdatedDate updateDate23,
              AF.Id InstanceId24,
              RD.Id stateId33
INTO #TempGetFullProgramSelectionInfo            
FROM       UNICAS_CONFIG..Organization O                 
INNER JOIN UNICAS_CONFIG..AcademicInstitution AI
            ON AI.id = O.academicInstitutionid        
INNER JOIN unicas_config..applicationForm AF
            ON AF.casid = AI.casid  
           AND AF.Id = 6286   
INNER JOIN UNICAS_CONFIG..AssociationOrg asOrg
            ON asOrg.FormId         = AF.id
           and asOrg.OrganizationId = O.id
INNER JOIN UNICAS_CONFIG..Program P
            ON P.AssociationOrgId = asOrg.Id 
INNER JOIN unicas_config..ReferenceData RD 
            ON RD.ValueId = P.STATE  
           AND RD.ValueAbbr ='US'
INNER JOIN unicas_config..ReferenceDataSet RS  
            ON RS.SetId = RD.ReferenceSetId   
           AND RS.NAME = 'LK_States'

If #temp has indexes then sort on the one that makes the most sense

If that does not improve response then just force a hash join on all
Still use the select in my answer - add the HASH to the query above

INNER HASH JOIN

Answer 2

Try creating the following index (assuming it doesn't already exist)

CREATE INDEX ix_Organization_academicInstitutionid
    ON Organization(academicInstitutionid, Id) INCLUDE (Name)

Answer 3

Before making any change to script, just do an index rebuid on all the involved tables and see if there is any significant increase. (at least, do a table level statistics rebuild with full scan)

[Program] seems like a big table, make ensure P.AssociationOrgId is indexed.