Forum Discussion

amrragab23's avatar
amrragab23
Copper Contributor
Feb 10, 2025

How can I optimize this query for better performance

Hi ,
I have this query and it is taking a long time. If there are more than 10k rows, it takes more than 5 minutes. Is there another way to speed up the process?

 

SELECT ROW_NUMBER() OVER (ORDER BY CreationDate DESC) AS RowId,
       Id [Id],
       transactionsId [TransactionsId],
       amount [Amount],
       AccountId [AccountId],
       dbo.Account_FirstBalance(CreationDate, AccountId, 161, CompanyId) [FirstBalance]
FROM p_Ledger
WHERE CreationDate >= '2024-11-01'
      AND CreationDate <= '2025-02-11'
      AND CompanyId = 117
      AND branchId = 161
ALTER FUNCTION [dbo].[Account_FirstBalance](
    @TransactionsDate DATETIME, 
    @AccountId BIGINT, 
    @BranchId INT, 
    @CompanyId BIGINT
) 
RETURNS FLOAT 
AS    
BEGIN  
    DECLARE @credit FLOAT;

    SELECT @credit = SUM(CASE WHEN T.transactionStatusId = 1 THEN T.amount ELSE -T.amount END)
    FROM dbo.Transactions T
    WHERE 
        T.Approval = 1 
        AND T.CompanyId = @CompanyId
        AND T.AccountsId = @AccountId
        AND T.IsDeleted = 0
        AND T.transactionsDate < @TransactionsDate
        AND (@BranchId = 0 OR T.branchId = @BranchId);

    RETURN ROUND(COALESCE(@credit, 0), 2);
END;

 

Analysis Services
analytics
Big Data
Data Warehouse
  • rodgerkong's avatar
    rodgerkong
    Iron Contributor
    Feb 11, 2025

    As olafhelper said, the UDF is the source of your issue. There are 2 ways to modify your query

    1. Use JOIN and aggregated function in a subquery/CTE, then JOIN with p_Ledger to get result.
    2. Use CROSS APPLY replace the UDF, so the query might run in parallel.

     

    Code 1

    WITH CTE
AS
(
SELECT 
	T.CompanyId, T.AccountsId, T.branchId, L.CreationDate,
    SUM(IIF(T.transactionStatusId = 1, T.amount, -T.amount)) [FirstBalance]
FROM p_Ledger L
INNER JOIN dbo.Transactions T 
ON
    T.CompanyId = L.CompanyId
    AND T.AccountsId = L.AccountId
    AND T.transactionsDate < L.CreationDate
    AND T.branchId = L.BranchId
WHERE 
	L.CreationDate >= '2024-11-01'
    AND L.CreationDate <= '2025-02-11'
    AND L.CompanyId = 117
    AND L.branchId = 161
	AND T.Approval = 1 
    AND T.IsDeleted = 0
GROUP BY
	T.CompanyId, T.AccountsId, T.branchId, L.CreationDate
)
SELECT
	ROW_NUMBER() OVER (ORDER BY L.CreationDate DESC) AS RowId,
    L.Id [Id],
    L.transactionsId [TransactionsId],
    L.amount [Amount],
    L.AccountId [AccountId],
	ROUND(ISNULL(CTE.FirstBalance, 0), 2) [FirstBalance]
FROM p_Ledger L LEFT OUTER JOIN CTE
ON L.AccountId = CTE.AccountsId
AND L.CompanyId = CTE.CompanyId
AND L.branchId = CTE.branchId
AND L.CreationDate = CTE.CreationDate
WHERE L.CreationDate BETWEEN '2024-11-01' AND '2025-02-11'

     

    Code 2

    SELECT 
    ROW_NUMBER() OVER (ORDER BY CreationDate DESC) AS RowId,
    Id AS [Id],
    transactionsId AS [TransactionsId],
    amount AS [Amount],
    AccountId AS [AccountId],
    ROUND(COALESCE(calc.FirstBalance, 0), 2) AS [FirstBalance]
FROM p_Ledger L
CROSS APPLY (
    SELECT 
        SUM(CASE WHEN T.transactionStatusId = 1 THEN T.amount ELSE -T.amount END) AS FirstBalance
    FROM dbo.Transactions T
    WHERE 
        T.Approval = 1
        AND T.CompanyId = L.CompanyId
        AND T.AccountsId = L.AccountId
        AND T.IsDeleted = 0
        AND T.transactionsDate < L.CreationDate
        AND T.branchId = L.branchId
) calc
WHERE 
    L.CreationDate >= '2024-11-01'
    AND L.CreationDate <= '2025-02-11'
    AND L.CompanyId = 117
    AND L.branchId = 161

     

    NOTE

    You should do some test to confirm which way is the best, and if they will get result correctly.

  • olafhelper's avatar
    olafhelper
    Bronze Contributor
    Feb 10, 2025

    Is there another way to speed up the process?

    A function is executed per source row and if the function runs queries, then of course that will be very slow.

    Rewrite your query as one set-based query instead of using a function.

Resources