Low performance set based storedprocedure

Question

I am using following set based code in sql to calculate the customers bill but it takes 3 Min to performs the operation on 40000 records !!! let me know what is the problem ???

 with cte (ID,[Date],Time,DocumentNumber,Title,TopicFK,Description,DocumentHeaderID,Debit,Credit,Balance,[Status])
as
(
    select ID,[Date],Time,DocumentNumber,Title,TopicFK,Description,DocumentHeaderID,Debit, Credit, Balance=(case when (Debit>Credit) then abs(Debit) else abs(Credit) end), [Status]=cast((case when (Debit>Credit) then 'Debit' else 'Credit' end)as nvarchar(10))
        from #t1
        where ID = 1
    union all
    select tbl.ID,tbl.[Date],tbl.Time,tbl.DocumentNumber,tbl.Title,tbl.TopicFK,tbl.Description,tbl.DocumentHeaderID,tbl.Debit, tbl.Credit
            , Balance=cast((case when ((tbl.Debit > 0 and cte.[Status] = 'Debit')) then abs(cte.Balance + tbl.Debit)
                when tbl.Debit > 0 and cte.[Status] = 'Credit' then abs(cte.Balance - tbl.Debit)
                when(tbl.Credit > 0 and cte.[Status] = 'Debit') then abs(cte.Balance - tbl.Credit)
                when(tbl.Credit > 0 and cte.[Status] = 'Credit') then abs(cte.Balance + tbl.Credit)
                                end )as decimal(18,0))
            , cast((case when ((tbl.Debit > 0 and cte.[Status] = 'Debit')) then 'Debit'
                when tbl.Debit > 0 and cte.[Status] = 'Credit' then 'Credit'
                when(tbl.Credit > 0 and cte.[Status] = 'Debit') then 'Debit'
                when(tbl.Credit > 0 and cte.[Status] = 'Credit') then 'Credit'

                end )as nvarchar(10)) as [Status]
        from #t1 tbl
            inner join cte cte
            on tbl.ID = cte.ID + 1

)

select [Date],Time,DocumentNumber,Title,TopicFK,Description,DocumentHeaderID, Debit, Credit, Balance, [Status] from cte
option (maxrecursion 0);

score 0 · Answer 1 · answered Sep 15 '14 at 07:54

0

The [Status] field will not have index, because it created in WITH clause. I will recomend you not to use [Status] field in your WHERE clause

cast((case when ((tbl.Debit > 0 and cte.Debit>cte.Credit)) then abs(cte.Balance + tbl.Debit)
                when tbl.Debit > 0 and cte.Debit<=cte.Credit then abs(cte.Balance - tbl.Debit)
                when(tbl.Credit > 0 and cte.Debit>cte.Credit) then abs(cte.Balance - tbl.Credit)
                when(tbl.Credit > 0 and cte.Debit<=cte.Credit) then abs(cte.Balance + tbl.Credit)
                                end )as decimal(18,0))

answered Sep 15 '14 at 07:54

MikkaRin

3,026
19
34

you mean remove line 4,5,6,7 ??? so let me know how to set status field in line 14 ?? – franchesco totti Sep 15 '14 at 08:00
What the hell is line 14? or 4,5,6,7? I just mean that despite of using [Status] you should use Debit and Credit fields. In the second CASE - you may store these data from prev document - despite of counting and store STATUS field. – MikkaRin Sep 15 '14 at 08:04
I tried your solution but didn't impact in the performance !! – franchesco totti Sep 15 '14 at 08:08
but I need to store status field to show in my report for the user – franchesco totti Sep 15 '14 at 08:11

Low performance set based storedprocedure

1 Answers1