I have the following query.  Its execution took 15 min and it's too much slow.  Is there a way to optimize it?SELECT Id,Fees,WeekOfMonth,CONVERT(NVARCHAR(MAX), StartDate, 103) AS StartDate,CONVERT(NVARCHAR(MAX), EndDate, 103) AS EndDate,'Temp Fees                      ' AS FeesName   ,@MonthName AS [MonthName]  INTO ##TempFeesMonthly  from  (SELECT DISTINCT 1 as Id,sum(((CNTI_THPAYE *(CNTI_THFACT / CNTI_THPAYE)) *(CASE WHEN CNTI_DURHEBDO IS NULL THEN 1 ELSE CNTI_DURHEBDO/5 END)*@NumberOfDays)) AS Fees,WeekOfMonth,StartDate,EndDateFROM SCHHAYS.dbo.WTVTAT TAT      LEFT JOIN    SCHHAYS.dbo.WTTIEINT INT         ON (            TAT.TIE_ID = INT.TIE_ID        )         AND (            TAT.VTAT_IORDRE = INT.TIEI_ORDRE        )         LEFT JOIN    SCHHAYS.dbo.PYCONTRAT CC         ON TAT.PER_ID = CC.PER_ID         AND TAT.CNT_ID = CC.CNT_ID         LEFT JOIN    SCHHAYS.dbo.CMTIERS T         ON TAT.TIE_ID = T.TIE_ID        LEFT JOIN    SCHHAYS.dbo.WTMISS M         ON CC.PER_ID = M.PER_ID         AND CC.CNT_ID = M.CNT_ID         LEFT JOIN    ##WTCNTIWeek COT1         ON M.PER_ID = COT1.PER_ID         AND M.CNT_ID = COT1.CNT_ID        INNER JOIN    SCHHAYS.dbo.WTPRH AS PRH         ON M.PER_ID = PRH.PER_ID         AND M.CNT_ID = PRH.CNT_ID         AND M.TIE_ID = PRH.TIE_ID         INNER JOIN    ##tempStartEndWeekDates AS Tsed         ON     PRH_DTEDEBSEM>=Tsed.StartDate         AND PRH_DTEFINSEM<=Tsed.EndDate         LEFT JOIN    SCHHAYS.dbo.WTSCCT C         ON CC.RGPCNT_ID = C.RGPCNT_ID         AND CC.PER_ID = C.PER_ID         AND CC.CNT_ID = C.CNT_ID       INNER JOIN    ##TempHaysStaffWeek HF         ON C.VAPO_CODE = HF.onetouch  COLLATE Latin1_General_CI_ASgroup byWeekOfMonth,StartDate,EndDate)t--CREATE INDEX IDX_TempFeesMonthly ON ##TempFeesMonthly(WeekOfMonth)--Calcul Temp Margin UNION ALL--INSERT INTO ##TempFeesMonthly(Id,Fees,WeekOfMonth,StartDate,EndDate,FeesName,[MonthName])SELECT 2,  sum(Fees) AS Fees  ,WeekOfMonth,CONVERT(NVARCHAR(MAX), StartDate, 103) AS StartDate,CONVERT(NVARCHAR(MAX), EndDate, 103) AS EndDate,'Temp Margin ' AS FeesName   ,@MonthName AS [MonthName]  from  ( SELECT DISTINCT sum((CASE WHEN CNTI_DURHEBDO IS NULL THEN 1 ELSE CNTI_DURHEBDO/5 END)*@NumberOfDays)-(CNTI_THPAYE *(CASE WHEN CNTI_DURHEBDO IS NULL THEN 1 ELSE CNTI_DURHEBDO/5 END)*1.453*1.21*@NumberOfDays) AS Fees,WeekOfMonth,StartDate,EndDateFROM SCHHAYS.dbo.WTVTAT TAT      LEFT JOIN    SCHHAYS.dbo.WTTIEINT INT         ON (            TAT.TIE_ID = INT.TIE_ID        )         AND (            TAT.VTAT_IORDRE = INT.TIEI_ORDRE        )         LEFT JOIN    SCHHAYS.dbo.PYCONTRAT CC         ON TAT.PER_ID = CC.PER_ID         AND TAT.CNT_ID = CC.CNT_ID         LEFT JOIN    SCHHAYS.dbo.CMTIERS T         ON TAT.TIE_ID = T.TIE_ID        LEFT JOIN    SCHHAYS.dbo.WTMISS M         ON CC.PER_ID = M.PER_ID         AND CC.CNT_ID = M.CNT_ID         LEFT JOIN    ##WTCNTIWeek COT1         ON M.PER_ID = COT1.PER_ID         AND M.CNT_ID = COT1.CNT_ID        INNER JOIN    SCHHAYS.dbo.WTPRH AS PRH         ON M.PER_ID = PRH.PER_ID         AND M.CNT_ID = PRH.CNT_ID         AND M.TIE_ID = PRH.TIE_ID         INNER JOIN    ##tempStartEndWeekDates AS Tsed         ON     PRH_DTEDEBSEM>=Tsed.StartDate         AND PRH_DTEFINSEM<=Tsed.EndDate         LEFT JOIN    SCHHAYS.dbo.WTSCCT C         ON CC.RGPCNT_ID = C.RGPCNT_ID         AND CC.PER_ID = C.PER_ID         AND CC.CNT_ID = C.CNT_ID       INNER JOIN    ##TempHaysStaffWeek HF         ON C.VAPO_CODE = HF.onetouch  COLLATE Latin1_General_CI_ASgroup byWeekOfMonth,StartDate,EndDate,CNTI_THPAYE,CNTI_DURHEBDO)tGROUP BY WeekOfMonth,StartDate,EndDate
Weekly fee calculation
performance;sql;datetime;sql server
To avoid to pass through tables join twice I insert all of the information into a temp table, and create the final calculation exactly as you do. If it remains slow you could create 2 indexes for the ##Temp1 table:WeekOfMonth, StartDate, EndDateWeekOfMonth, StartDate, EndDate, CNTI_THPAYE, CNTI_DURHEBDOThe query looks like this:SELECT      (CNTI_THPAYE * (CNTI_THFACT / CNTI_THPAYE)) * (CASE WHEN CNTI_DURHEBDO IS NULL THEN 1 ELSE CNTI_DURHEBDO / 5 END) * @NumberOfDays AS Fees1,CASE WHEN CNTI_DURHEBDO IS NULL THEN 1 ELSE CNTI_DURHEBDO / 5 END) * @NumberOfDays) - (CNTI_THPAYE * (CASE WHEN CNTI_DURHEBDO IS NULL THEN 1 ELSE CNTI_DURHEBDO / 5 END) * 1.453 * 1.21 * @NumberOfDays AS Fees2    ,WeekOfMonth    ,StartDate    ,EndDate    ,CNTI_THPAYE    ,CNTI_DURHEBDOINTO ##Temp1FROM SCHHAYS.dbo.WTVTAT AS TAT    LEFT JOIN SCHHAYS.dbo.WTTIEINT [INT]         ON TAT.TIE_ID = INT.TIE_ID        AND TAT.VTAT_IORDRE = INT.TIEI_ORDRE    LEFT JOIN SCHHAYS.dbo.PYCONTRAT CC         ON TAT.PER_ID = CC.PER_ID         AND TAT.CNT_ID = CC.CNT_ID         LEFT JOIN SCHHAYS.dbo.CMTIERS T         ON TAT.TIE_ID = T.TIE_ID        LEFT JOIN SCHHAYS.dbo.WTMISS M         ON CC.PER_ID = M.PER_ID         AND CC.CNT_ID = M.CNT_ID         LEFT JOIN ##WTCNTIWeek COT1         ON M.PER_ID = COT1.PER_ID         AND M.CNT_ID = COT1.CNT_ID        INNER JOIN SCHHAYS.dbo.WTPRH AS PRH         ON M.PER_ID = PRH.PER_ID         AND M.CNT_ID = PRH.CNT_ID         AND M.TIE_ID = PRH.TIE_ID         INNER JOIN ##tempStartEndWeekDates AS Tsed         ON PRH_DTEDEBSEM >= Tsed.StartDate         AND PRH_DTEFINSEM <= Tsed.EndDate         LEFT JOIN SCHHAYS.dbo.WTSCCT C         ON CC.RGPCNT_ID = C.RGPCNT_ID         AND CC.PER_ID = C.PER_ID         AND CC.CNT_ID = C.CNT_ID       INNER JOIN ##TempHaysStaffWeek HF         ON C.VAPO_CODE = HF.onetouch  COLLATE Latin1_General_CI_ASSELECT    Id    ,Fees    ,WeekOfMonth    ,CONVERT(NVARCHAR(MAX), StartDate, 103) AS StartDate    ,CONVERT(NVARCHAR(MAX), EndDate, 103) AS EndDate    ,'Temp Fees                      ' AS FeesName     ,@MonthName AS [MonthName]INTO ##TempFeesMonthlyFROM(    SELECT DISTINCT         1 AS Id        ,sum(Fee1) AS Fees        ,WeekOfMonth        ,StartDate        ,EndDate    FROM ##Temp1    GROUP BY WeekOfMonth, StartDate, EndDate) AS tUNION ALLSELECT    Id    , SUM(Fees) AS Fees    , WeekOfMonth    , CONVERT(NVARCHAR(MAX), StartDate, 103) AS StartDate    , CONVERT(NVARCHAR(MAX), EndDate, 103) AS EndDate    , 'Temp Margin ' AS FeesName     , @MonthName AS [MonthName]FROM(    SELECT DISTINCT         2 AS Id        SUM(Fees2) AS Fees        , WeekOfMonth        , StartDate        , EndDate    FROM ##Temp1    GROUP BY WeekOfMonth, StartDate, EndDate, CNTI_THPAYE, CNTI_DURHEBDO) AS tGROUP BY WeekOfMonth, StartDate, EndDateThis should reduce execution time.