Forum Discussion
amrragab23
Feb 10, 2025Copper Contributor
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...
rodgerkong
Feb 11, 2025Iron Contributor
As olafhelper said, the UDF is the source of your issue. There are 2 ways to modify your query
- Use JOIN and aggregated function in a subquery/CTE, then JOIN with p_Ledger to get result.
- 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.