0

i would like your help in the best way to tune and re-write a stored procedure. it is consuming a lot of time and don't know what should i do to make it faster

CREATE PROCEDURE [dbo].[xxxxxxxxxxxx] (@InstanceId INTEGER, @ProfileId INTEGER)
AS


BEGIN

SET NOCOUNT ON

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 = @InstanceId
  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'

SELECT     DISTINCT TE.id0,
              TE.ProgramId1,
              TE.ProgramName2,
              TE.ProgramLevel3,
              TE.Department4,
              TE.Track5,
              TE.AcademicYear6,
              TE.StartTerm7,
              TE.Delivery8,
              TE.Fee9,
              TE.City10,
              TE.State11,
              TE.StartDate12,
              TE.Deadline13,
              TE.DeadlineDisplay14,
              TE.ProgramType15,
              TE.OrganizationId16,
              TE.OrganizationName17,
              TE.ApplicationType18,
              TE.Concentration19,
              TE.ZipCode20,
              TE.Campus21,
              TE.WADisplayName22,
              TE.updateDate23,
              TE.InstanceId24,
              PS.Id  ProgramSelectedId25,
              PS.CreatedDate ProgramSelectedCreatedDate26,
              PS.ProfileId ProfileId27,
              PS.IsPaid IsPaid28,
              PS.SubmissionStatus ProgramSelectedSubmissionStatus29,
              PAS.PaymentStatus  ProgramSelectedPaymentStatus30,
              PAS.PaymentReceivedDate PaymentReceivedDate31,
              0 AS ProgramPercentage32,
              TE.stateId33,
              CASE WHEN T2.SupplimentalSection IS NULL THEN 0
                   ELSE T2.SupplimentalSection
              END AS  SupplimentalSection34
          FROM #TempGetFullProgramSelectionInfo   TE
          INNER JOIN unicas_ux..ProgramSelected PS  
                       ON TE.Id0 = PS.ProgramId  AND PS.iNstanceId = TE.InstanceId24 AND PS.ProfileId = @ProfileId
          LEFT OUTER JOIN
                        (SELECT PMS.ProgramId,PMS.ProfileID,Sup.ProgramId ProgSelProgramID, MAX(PMS.SectionId) AS  SupplimentalSection
                                                           FROM  unicas_ux.dbo.ProgramMaterialStatus  PMS
                                                           INNER JOIN [unicas_config]..[SupplementalSection] Sup
                                                                   ON Sup.Id = PMS.SectionID 
                         GROUP BY PMS.ProgramId,PMS.ProfileID,Sup.ProgramId
                        ) T2
                       ON T2.ProgramId = PS.Id AND  T2.ProfileID = PS.ProfileId   AND T2.ProgSelProgramID = TE.Id0  
          INNER JOIN unicas_ux..Profile PR  
                       ON PR.Id = PS.ProfileId  
          LEFT OUTER JOIN  unicas_ux.dbo.PaymentStatus PAS   
                       ON PAS.ProfileId  = PS.ProfileId AND  PAS.ProgramId = PS.ProgramId
                       AND PAS.InstanceId = PS.iNstanceId AND PAS.PaymentStatus='Received' 

          ORDER BY TE.Id0 ASC;

 DROP TABLE #TempGetFullProgramSelectionInfo  

   END
  • How many rows is it returning? What's the execution plan look like. The query doesn't appear to be doing anything overtly stupid. – Zane Oct 29 '15 at 17:23
  • Post the execution plan. Do you have Sentry Plan Explorer? it's an awesome tool. What is the size of the table ( number of rows )? Did you try to create some indexes? If yes, post it it too. – Racer SQL Oct 29 '15 at 17:24
  • It depends on the size of the temp table, but assuming it's a moderately sized, a lack of index in the temp table can slow down. Have you tried defining the temp table with index? – Kentaro Oct 29 '15 at 19:40
  • Can you run the procedure with Set Statistics IO ON and Set Statistics TIME ON, as well as grabbing the execution plan as mentioned above. You could certainly create covering indexes for everything that is used within joins and within the select and it would probably be worth your time to verify that parameter sniffing is not causing you an issue (brentozar.com/archive/2013/06/…). There are lots of options on just these two statements but it would require more information to really be able to help. – mskinner Nov 4 '15 at 22:43
  • @Kentaro the temp table is small , less than 100 .. i don't believe it worth to create index on it – sebeid Nov 5 '15 at 22:26

Your Answer

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

Browse other questions tagged or ask your own question.