SQL Server 2014 stored procedure is slow

Question

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..

Is there a way to optimize this ?

Here the 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 > 0
        ROLLBACK TRANSACTION;
END CATCH;

IF @@TRANCOUNT > 0
    COMMIT TRANSACTION;
return
End

Thanks again!!

Have you tried to run the query with the [execution plan?](http://stackoverflow.com/questions/7359702/how-do-i-obtain-a-query-execution-plan) — litelite, May 15 '17 at 19:28
What are the **table structures**? What kind of indices do you have on your tables? — marc_s, May 15 '17 at 19:30
You have some logical issues in here as well. TOP 1 with no order by. There also seems to potentially be some normalization issues. When you start seeing repeating groups (TauxTaxe1, TauxTaxe2, TauxTaxe3) it is a red flag that perhaps the table is violating 1NF. — Sean Lange, May 15 '17 at 19:36
@SeanLange Not to really disagree with your first point, but if there is only one unique Taxe 1,2,3 combination for any ID/IDCompagnie combination, using TOP makes sense without an order by. Better than distinct to accomplish the same goal. If that is the case though, your second point is all the more justified. — Aaron Dietz, May 15 '17 at 19:52
@AaronDietz if there is only row per unique combination than top is totally pointless. :) — Sean Lange, May 15 '17 at 20:00
You might be able to roll those three selects up into a single query (once you overcome any one-many double counting issues), and if you can do that you can proabably slot it in with the `UPDATE` in one query and run it much faster. I'm curious - you pass in `@Taux1` as a parameter. Then you replace it with your first select query. Is the purpose of the `@Taux1` parameter as some kind of optional override if the tax record doesn't exist? Basically you could probably rewrite this as one single (complicated) SQL statement that would execute much faster — Nick.Mc, May 16 '17 at 00:29

score 0 · Answer 1 · answered May 16 '17 at 00:00

Depending on the cardinality of IdFacture and IDCompagnie in table tbfactureDetail, an index would help things along if one is missing.

if there are many different values of IdCompagnie for the many different values of IDFacture

CREATE NONCLUSTERED INDEX IX_NewIdx_Name
  ON tbfactureDetail (IdFacture,IdCompagnie)

If there are relatively few IdCompagnie per IDfacture, you could use an Include.

CREATE NONCLUSTERED INDEX IX_NewIdx_Name
ON tbfactureDetail (IdFacture)
INCLUDE (IdCompagnie)

I think this is mathematically the same and a bit more optimal for the code.

BEGIN TRY

select top 1 @Taux1 = TauxTaxe1
,            @Taux2 = TauxTaxe2
,            @Taux3 = TauxTaxe3
from tbFactureEntete
where ID = @IdFacture and IDCompagnie = @IDCie


select @sousTotal =sum(isnull(A.TotalLigne,0))
,      @TotalTx1 = sum(taxe1 * isnull(A.TotalLigne,0) * @Taux1 / 100)
,      @TotalTx2 = sum(taxe2 * isnull(A.TotalLigne,0) * @Taux2 / 100)
,      @TotalTx3 = sum(taxe3 * isnull(A.TotalLigne,0) * @Taux3 / 100)
,      @totalEscompte = sum(isnull(MontantEscompte,0) )
,      @totalFacture = 
 sum(taxe1 * isnull(A.TotalLigne,0) * @Taux1 / 100) +
 sum(taxe2 * isnull(A.TotalLigne,0) * @Taux2 / 100) +
 sum(taxe3 * isnull(A.TotalLigne,0) * @Taux3 / 100)
from tbfactureDetail A
where IdFacture = @IdFacture and A.IDCompagnie = @IDCie


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

SQL Server 2014 stored procedure is slow

1 Answers1