8

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'

enter image description here

enter image description here

enter image description here

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;

enter image description here

enter image description here

enter image description here

enter image description here

using the query recommended by Frisbee

enter image description here

enter image description here

enter image description here

Using Hash join

enter image description here

enter image description here

  • How much time does the query needs to run now? And why is it executed 200 times per minute? Can you explain the need to save the results into a temp table so often? – ypercubeᵀᴹ Dec 18 '15 at 8:04
  • this query is in a SP and the temp table is used in the next query. this production has around 20 K request per min. – sebeid Dec 18 '15 at 15:14
2

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
| improve this answer | |
  • i tried your query but there is not much improvement. also i know it is not recommended to use INNER Hash Join in Production. Also i didn't get what you mean by"Still use the select in my answer" – sebeid Dec 18 '15 at 5:03
  • Not much improvement - is that a bad thing?. Not recommended to use INNER Hash Join in Production? Where did you hear that? Why don't you give it a try for grins? – paparazzo Dec 18 '15 at 5:17
  • added the execution plan to .. this query is most consuming CPU and IO on production. based on the number i am getting higher duration and more writes and same cpu – sebeid Dec 18 '15 at 15:00
  • stackoverflow.com/questions/800124/… .. but i will give it a try and let you know . thanks – sebeid Dec 18 '15 at 15:00
  • AF.Id = 6286 is not hard coded in the query , it is a parameter passed to the SP .. i just used this number for testing – sebeid Dec 18 '15 at 15:19
1

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

CREATE INDEX ix_Organization_academicInstitutionid
    ON Organization(academicInstitutionid, Id) INCLUDE (Name)
| improve this answer | |
  • there is a Non Clustered Index on academicInstitutionid and a Clustred Index on ID .. – sebeid Dec 18 '15 at 16:04
  • Specifically is there an include (name) on the index on academicInstitutionid? – Kenneth Fisher Dec 18 '15 at 16:12
  • yes name is included also .. does it matter if it is 1 index or 2 indexes – sebeid Dec 18 '15 at 16:14
  • 1
    You mean that you have the clustered index on id and the second one on academicInstitutionId include(name)? I'd go ahead and create the composite index. If you look at your plans most of them are doing clustered index seeks. The idea is to try to make it an index seek instead. – Kenneth Fisher Dec 18 '15 at 16:16
0

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.

| improve this answer | |
  • i rebuild statistics everyday and all indexes were rebuild – sebeid Dec 18 '15 at 5:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.