Low speed SQL query in balance sheet calculation

Question

I am using Tarazc4 sp to calculate the balance sheet of my application but it is very time consuming when my records reached to 50000 (time out error), please help on this !! it is out of my mind now !!!

SELECT * 
INTO #levels
FROM dbo.topics_getlevel()

SELECT * 
INTO #t1 
FROM accounting.documentdetail 
WHERE accounting.documentdetail.date BETWEEN @FromDate AND @ToDate

SELECT * 
FROM 
   (SELECT TOP (100) PERCENT 
        Accounting.Topics.Code, Accounting.Topics.TopicID, Accounting.Topics.ParentID,  
        Accounting.Topics.Description, 
        (SELECT Debit
         FROM dbo.SubTopics_GetSum(Accounting.Topics.TopicID, @FromDate, @ToDate) AS SubTopics_GetSum_2) AS Debit,
        (SELECT Credit
         FROM dbo.SubTopics_GetSum(Accounting.Topics.TopicID, @FromDate, @ToDate) AS SubTopics_GetSum_1) AS Credit, 
        (CASE 
            WHEN ((SELECT     Credit
                              FROM          dbo.subtopics_getsum(Accounting.Topics.TopicID,@FromDate,@ToDate)) -
                          (SELECT     Debit
                             FROM         dbo.subtopics_getsum(Accounting.Topics.TopicID,@FromDate,@ToDate))) < 0 THEN
                          ((SELECT     Debit
                              FROM          dbo.subtopics_getsum(Accounting.Topics.TopicID,@FromDate,@ToDate)) -
                          (SELECT     Credit
                             FROM         dbo.subtopics_getsum(Accounting.Topics.TopicID,@FromDate,@ToDate))) ELSE 0 END) AS ResDEBIT, (CASE WHEN
                          ((SELECT     Credit
                              FROM          dbo.subtopics_getsum(Accounting.Topics.TopicID,@FromDate,@ToDate)) -
                          (SELECT     Debit
                             FROM         dbo.subtopics_getsum(Accounting.Topics.TopicID,@FromDate,@ToDate))) > 0 THEN
                          ((SELECT     Credit
                              FROM          dbo.subtopics_getsum(Accounting.Topics.TopicID,@FromDate,@ToDate)) -
                          (SELECT     Debit
                             FROM         dbo.subtopics_getsum(Accounting.Topics.TopicID,@FromDate,@ToDate))) ELSE 0 END) AS ResCREDIT,(select [Level] from #Levels where TopicID=Accounting.Topics.TopicID) as [Level]
FROM         Accounting.Topics Left OUTER JOIN
                      #t1 ON Accounting.Topics.TopicID = #t1.TopicFK

GROUP BY Accounting.Topics.TopicID, Accounting.Topics.ParentID, Accounting.Topics.Code, Accounting.Topics.Description

ORDER BY Accounting.Topics.TopicID
)
t2

--------------------------------END Tarazc4

and used " dbo.subtopics_getsum " function in Tarazc4 sp to get sum of each account ,check it out plz :

ALTER Function [dbo].[SubTopics_GetSum]

(
@TopicID int,@FromDate char(10),@ToDate char(10)
)
RETURNS TABLE 
AS
RETURN 
(
     WITH cte AS (
     SELECT  T1.TopicID, T1.Code, T1.Description, T1.ParentID, 
     T1.ParentID AS NewParentID, 
     CAST(T1.Code AS nvarchar(MAX)) AS TopicCode, 
     CAST(T1.Description AS nvarchar(MAX)) AS TopicDescription,
     isnull((Accounting.DocumentDetail.Debit),0) AS  Debit,
     isnull((Accounting.DocumentDetail.Credit),0) AS Credit
     FROM Accounting.Topics AS T1 
     LEFT OUTER JOIN
     Accounting.DocumentDetail ON T1.TopicID = Accounting.DocumentDetail.TopicFK
     where (Accounting.DocumentDetail.date between @FromDate and @ToDate) 
     and NOT EXISTS(
          SELECT    T2.TopicID, T2.Code, T2.Description, T2.ParentID, 
          isnull((Accounting.DocumentDetail.Debit),0) AS Debit,
          isnull((Accounting.DocumentDetail.Credit),0) AS Credit
          FROM Accounting.Topics AS T2 
          LEFT OUTER JOIN
          Accounting.DocumentDetail 
          ON T2.TopicID = Accounting.DocumentDetail.TopicFK
          WHERE (Accounting.DocumentDetail.date between @FromDate and @ToDate) 
          and (ParentID = T1.TopicID)
     )
     UNION ALL                          
     SELECT c.TopicID, c.Code, c.Description, c.ParentID, T1.ParentID AS NewParentID, 
     CAST(T1.Code AS nvarchar(MAX)) + c.TopicCode AS TopicCode, 
     CAST(T1.Description AS nvarchar(MAX)) + ' - ' + 
     c.TopicDescription AS TopicDescription,
     c.Debit AS Debit,c.Credit  AS Credit
     FROM cte AS c 
     INNER JOIN Accounting.Topics AS T1 
     ON T1.TopicID = c.NewParentID
     )

     select 
     isnull(sum(Debit),0)+
     isnull(
       (select sum(debit) from accounting.documentdetail 
        where (Accounting.DocumentDetail.date between @FromDate and @ToDate) 
       and topicfk=@TopicID)
     ,0) as Debit,
     isnull(Sum(Credit),0)+
     isnull(
        (select sum(credit) from accounting.documentdetail 
         where (Accounting.DocumentDetail.date between @FromDate and @ToDate) 
         and topicfk=@TopicID)
     ,0) as Credit 
     from cte as c
     WHERE     (NewParentID = @TopicID)
)

Actually , my topics and documents table are like following image , and I just need to calculate the balance sheet of my topics from documentdetail table and show it like hierarchy of my topics (parent-childs) !!!!

Answer 1

I see dbo.subtopics_getsum(Accounting.Topics.TopicID,@FromDate,@ToDate) over and over in your columns. As a nasty workaround you could also dump that into a temp table like #t1, although it might be that it's already cached sufficiently.

Also I don't see the need for TOP (100) PERCENT although it probably makes no difference. Was this copied out of a view at some stage?

You really need to refactor this code properly.