title: Performance function SQL Server
body: <p>I'm creating function to return me a number of worked minutes between two dates.</p>
  
  <p>This returns me the exact number of minuts but when I use it on many records, the treatment is very long.</p>
  
  <p>I have 3 functions:</p>
  
  <pre><code>CREATE FUNCTION FN_FERIES_SELON_ANNEE (@YEAR INT) 
  RETURNS @FERIES TABLE (JourId INT NOT NULL, 
                         JourDate DATETIME NOT NULL, 
                         JoURLabel VARCHAR(50) NULL)
  AS
  BEGIN
      DECLARE @JoursFeries TABLE  (      
          [JourId] [INT] IDENTITY(1,1) NOT NULL,      
          [JourDate] [DATETIME] NOT NULL,      
          [JoURLabel] [VARCHAR](50) NULL 
      )       
  
      DECLARE @an INT 
      DECLARE @G INT     
      DECLARE @I INT     
      DECLARE @J INT     
      DECLARE @C INT     
      DECLARE @H INT     
      DECLARE @L INT     
      DECLARE @JourPaque INT     
      DECLARE @MoisPaque INT     
      DECLARE @DimPaque DATETIME     
      DECLARE @LunPaque DATETIME     
      DECLARE @JeuAscension DATETIME     
      DECLARE @LunPentecote DATETIME     
      DECLARE @NouvelAn DATETIME     
      DECLARE @FeteTravail DATETIME     
      DECLARE @Armistice3945 DATETIME     
      DECLARE @Assomption DATETIME     
      DECLARE @Armistice1418 DATETIME     
      DECLARE @FeteNationale DATETIME     
      DECLARE @ToussaINT DATETIME     
      DECLARE @Noel DATETIME   
  
      SET @an = @YEAR 
  
      SET @G = @an % 19      
      SET @C = @an / 100      
      SET @H = (@C - @C / 4 - (8 * @C + 13) / 25 + 19 * @G + 15) % 30      
      SET @I = @H - (@H / 28) * (1 - (@H / 28) * (29 / (@H + 1)) * ((21 - @G) / 11))      
      SET @J = (@an + @an / 4 + @I + 2 - @C + @C / 4) % 7      
  
      SET @L = @I - @J      
      SET @MoisPaque = 3 + (@L + 40) / 44      
      SET @JourPaque = @L + 28 - 31 * (@MoisPaque / 4)      
  
      -- Jours fériés mobiles      
  
      SET @DimPaque = cast(cast(@an AS VARCHAR(4)) + '-'     
                      + cast(@MoisPaque AS VARCHAR(2)) + '-'     
                      + cast(@JourPaque AS VARCHAR(2)) AS DATETIME)      
      SET @LunPaque = DATEADD(DAY, 1, @DimPaque)      
      SET @JeuAscension = DATEADD(DAY, 39, @DimPaque)      
      SET @LunPentecote = DATEADD(DAY, 50, @DimPaque)      
  
      -- Jours fériés fixes      
  
      SET @NouvelAn = cast(cast(@an AS VARCHAR(4))+'-01-01 00:00:00' AS DATETIME)      
      SET @FeteTravail = cast(cast(@an AS VARCHAR(4))+'-05-01 00:00:00' AS DATETIME)      
      SET @Armistice3945 = cast(cast(@an AS VARCHAR(4))+'-05-08 00:00:00' AS DATETIME)      
      SET @Assomption = cast(cast(@an AS VARCHAR(4))+'-08-15 00:00:00' AS DATETIME)      
      SET @Armistice1418 = cast(cast(@an AS VARCHAR(4))+'-11-11 00:00:00' AS DATETIME)      
      SET @FeteNationale = cast(cast(@an AS VARCHAR(4))+'-07-14 00:00:00' AS DATETIME)      
      SET @ToussaINT = cast(cast(@an AS VARCHAR(4))+'-11-01 00:00:00' AS DATETIME)      
      SET @Noel = cast(cast(@an AS VARCHAR(4))+'-12-25 00:00:00' AS DATETIME)      
  
      INSERT  INTO @JoursFeries (JourDate, JoURLabel)  
      SELECT  @LunPaque, 'Lundi de Pâques' 
      UNION
      SELECT  @JeuAscension, 'Jeudi de l''Ascension'   
      UNION   
      SELECT  @LunPentecote, 'Lundi de Pentecôte'  
      UNION 
      SELECT  @NouvelAn, 'Nouvel an'  
      UNION 
      SELECT  @FeteTravail, 'Fête du travail'
      UNION   
      SELECT  @Armistice3945, 'Armistice 39-45'
      UNION
      SELECT  @Assomption, 'Assomption'
      UNION 
      SELECT  @FeteNationale, 'Fête Nationale'
      UNION 
      SELECT  @ToussaINT, 'Toussaint'
      UNION 
      SELECT  @Armistice1418, 'Armistice 14-18'  
      UNION 
      SELECT  @Noel, 'Noël'
  
      INSERT INTO @FERIES
      SELECT * FROM @JoursFeries
      RETURN
  END
  GO
  
  CREATE FUNCTION FN_JOUR_TRAVAILLE (@Date1 DATETIME)
  RETURNS INT AS
  BEGIN   
      DECLARE @FLAG INT
      SET @FLAG = 1
      DECLARE @YEAR INT
      SET @YEAR = DATEPART(YEAR, @DATE1)
  
      IF EXISTS(SELECT * FROM FN_FERIES_SELON_ANNEE(@YEAR) WHERE JourDate = @Date1) BEGIN
          SET @FLAG = 0
      END
      ELSE
          IF DatePart(weekday, @DATE1) = 7 OR DatePart(weekday, @DATE1) = 1 BEGIN
              SET @FLAG = 0
          END
  
      RETURN @FLAG
  END
  GO
  
  CREATE FUNCTION FN_DATEDIFF_SELON_HORAIRES_ENTREPRISE2 (@Date1 DATETIME, @Date2 DATETIME)
      RETURNS INT AS
      BEGIN
          DECLARE @NB_Jours INT       
          DECLARE @Cpt INT
          DECLARE @Jours_Travailles INT
          DECLARE @Date1_at_8_am DATETIME
          DECLARE @Date2_at_6_pm DATETIME
          DECLARE @Excedent INT
  
          SET @NB_Jours = DATEDIFF(day, @Date1, @Date2) + 1
          SET @Cpt = 0
          SET @Jours_Travailles = 0
          SET @Excedent = 0
  
          SET @Date1_at_8_am = @Date1
          SET @Date1_at_8_am = DATEADD(hour, - (DATEPART(hour, @Date1) - 8), @Date1_at_8_am)
          SET @Date1_at_8_am = DATEADD(minute, - DATEPART(minute, @Date1_at_8_am), @Date1_at_8_am)
          SET @Date1_at_8_am = DATEADD(second, - DATEPART(second, @Date1_at_8_am), @Date1_at_8_am)
  
          SET @Date2_at_6_pm = @Date2
          SET @Date2_at_6_pm = DATEADD(hour, 18 - DATEPART(hour, @Date2), @Date2_at_6_pm)
          SET @Date2_at_6_pm = DATEADD(minute, - DATEPART(minute, @Date2_at_6_pm), @Date2_at_6_pm)
          SET @Date2_at_6_pm = DATEADD(second, - DATEPART(second, @Date2_at_6_pm), @Date2_at_6_pm)
  
          IF dbo.FN_JOUR_TRAVAILLE(@Date1) = 1 AND @Date1 &gt; @Date1_at_8_am BEGIN
              SET @Excedent = @Excedent + DATEDIFF(minute, @Date1_at_8_am, @Date1)
          END
          IF dbo.FN_JOUR_TRAVAILLE(@Date2) = 1 AND @Date2 &lt; @Date2_at_6_pm BEGIN
              SET @Excedent = @Excedent + DATEDIFF(minute, @Date2, @Date2_at_6_pm)
          END 
  
          WHILE @Cpt &lt; @NB_Jours
          BEGIN
              IF dbo.FN_JOUR_TRAVAILLE(DATEADD(day, @Cpt, @Date1)) = 1 
              BEGIN
                  SET @Jours_Travailles = @Jours_Travailles + 1
              END
              SET @Cpt = @Cpt + 1
          END
  
          RETURN @Jours_Travailles*600 - @Excedent
  END
  GO
  
  -----------------------------------------------------------------------
  -----------------------------------------------------------------------
  -----------------------------------------------------------------------
  </code></pre>
  
  <p>The problem is in the function FN_JOUR_TRAVAILLE at this line:</p>
  
  <pre><code>IF EXISTS(SELECT * FROM FN_FERIES_SELON_ANNEE(@YEAR) WHERE JourDate = @Date1) BEGIN
          SET @FLAG = 0
  END
  </code></pre>
  
  <p>For each day of each period I calculate I generate the table of public holidays for the year.</p>
  
  <p>The best solution would be to check if I did not already create this table, whether I search in it, if not I create it and I store it.</p>
  
  <p>But I dont know how to do this.</p>
  
  <p>I need to create a table in a superior scope and pass it as parameter in my functions I think.</p>