title: SQL Server 2014 stored procedure is slow
body: <p>I have a stored procedure to calculate receipt and up to today I didn't have any problems with it until I had to execute it in a database with 100'000 rows. It's much too slow and runs for at least 3 hours, and it's not finished yet.. </p>
  
  <p>Is there a way to optimize this ? </p>
  
  <p>Here the code:</p>
  
  <pre><code>Declare @sousTotal numeric(16,2),
      @TotalTx1 numeric(18,4),
      @TotalTx2 numeric(18,4),
      @TotalTx3 numeric(18,4),
      @Taux1 numeric(18,4),
      @Taux2 numeric(18,4),
      @Taux3 numeric(18,4),
      @totalEscompte numeric(16,2),
      @totalFacture numeric(16,2),
      @MontantPaiement numeric(16,2)
  Begin
  BEGIN TRANSACTION;
  
  BEGIN TRY
  
      select top 1 @Taux1 = TauxTaxe1,@Taux2 = TauxTaxe2  ,@Taux3 = TauxTaxe3 from tbFactureEntete where ID = @IdFacture and IDCompagnie = @IDCie 
        select @sousTotal =sum(sousTotal),@TotalTx1 = sum(totalTx1),@TotalTx2 = sum(totalTx2),@TotalTx3 = sum(totalTx3),
      @totalEscompte = sum(totalEscompte ),@totalFacture = sum(totalFacture)    from(
      select  SUM(isnull(A.TotalLigne,0)) AS sousTotal   ,
      case taxe1 when  0 then 0
                   when  1 then SUM(isnull(A.TotalLigne,0)) * @Taux1 / 100
                   end AS totalTx1,
      case taxe2 when 0 then 0
                   when 1 then SUM(isnull(A.TotalLigne,0)) * @Taux2 / 100
                   end AS totalTx2,
      case taxe3 when 0 then 0
                   when 1 then SUM(isnull(A.TotalLigne,0)) * @Taux3 / 100
                   end AS totalTx3,                                
       sum(isnull(MontantEscompte,0))  as totalEscompte ,sum(isnull(TotalLigne,0)) + case taxe1 when  0 then 0
                   when  1 then SUM(isnull(A.TotalLigne,0)) * @Taux1 / 100
                   end +
      case taxe2 when 0 then 0
                   when 1 then SUM(isnull(A.TotalLigne,0)) * @Taux2 / 100
                   end +
      case taxe3 when 0 then 0
                   when 1 then SUM(isnull(A.TotalLigne,0)) * @Taux3 / 100
                   end as totalFacture
      from tbfactureDetail A
      where IdFacture = @IdFacture  and A.IDCompagnie = @IDCie 
      group by taxe1,taxe2,taxe3) as AA
  
      select @MontantPaiement = SUM(MontantPaiement) from tbFacturePaiement 
      where IdFacture = @IdFacture 
  
      update tbFactureEntete 
      set Total_AvantTaxe = @sousTotal,
          totalTaxe1 = @TotalTx1, 
          totalTaxe2 = @TotalTx2,
          totalTaxe3 = @TotalTx3, 
          TotalEscompte = isnull(@totalEscompte,0), 
          TotalFacture = isnull(@totalFacture,0),
          TotalPaiementCR = isnull(@MontantPaiement,0) ,
          TotalSolde = isnull(@totalFacture,0) - isnull(@MontantPaiement,0) 
      where ID = @IdFacture and IDCompagnie = @IDCie
  
  
  
  
  END TRY
  BEGIN CATCH
      SELECT 
          ERROR_NUMBER() AS ErrorNumber
          ,ERROR_SEVERITY() AS ErrorSeverity
          ,ERROR_STATE() AS ErrorState
          ,ERROR_PROCEDURE() AS ErrorProcedure
          ,ERROR_LINE() AS ErrorLine
          ,ERROR_MESSAGE() AS ErrorMessage;
  
      IF @@TRANCOUNT &gt; 0
          ROLLBACK TRANSACTION;
  END CATCH;
  
  IF @@TRANCOUNT &gt; 0
      COMMIT TRANSACTION;
  return
  End
  </code></pre>
  
  <p>Thanks again!!</p>