SuiteQL Improve Budget Query With CTE

I recently ran a budget SuiteQL query that took a whopping 527,138ms to complete (that’s nearly 9 minutes!).

I had to take a look and see why it was taking so long, and after a few tweaks found the issue had to do with trying to find the very budget category I needed.

So to assist in this discovery I modified my query to search for the budget category first before using it in the rest of my statement.

The way I’ve designed my SuiteQL queries lately is to use a CTE expression to capture any inputs first followed by any other operative things needed next, before then moving on to the bulk of what I want returned.

Here’s an example of what I initially had:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
-- SuiteQL query to fetch budget values for specific GL accounts
-- @param `budget_type` is a string to search for the budget values needed, eg. FINAL, DRAFT (etc)
-- @param `account_id` is the account id to fetch
-- @param `run_date` (optional) fetching values for a specific date, needed in ISO format 'YYYY-MM-DD'
WITH inputs AS (
    SELECT
        ? AS budget_type,
        ? AS account_id,
        TO_DATE(?, 'YYYY-MM-DD') AS run_date
)
SELECT
    B.account AS credit_account,
    A.id AS debit_account,
    B.subsidiary AS sub_id,
    BUILTIN.DF(B.category) AS budget_category,
    TO_CHAR(AP.startdate, 'YYYY-MM') AS period,
    BM.amount AS monthly_amount,
FROM
    BudgetsMachine BM
    LEFT JOIN Budgets B
        ON B.id = BM.budget
    LEFT JOIN AccountingPeriod AP
        ON BM.period = AP.id
    LEFT JOIN Account A
        ON B.account = A.id
    CROSS JOIN inputs
WHERE
    ((
        inputs.run_date IS NOT NULL AND
        EXTRACT(YEAR FROM AP.startdate) = EXTRACT(YEAR FROM inputs.run_date)
        AND EXTRACT(MONTH FROM AP.startdate) = EXTRACT(MONTH FROM inputs.run_date)
    ) OR (
        inputs.run_date IS NULL AND
        EXTRACT(YEAR FROM AP.startdate) = EXTRACT(YEAR FROM CURRENT_DATE)
        AND EXTRACT(MONTH FROM AP.startdate) = EXTRACT(MONTH FROM CURRENT_DATE)
    ))
    AND BUILTIN.DF(B.category) LIKE '%' || inputs.budget_type || '%'
    AND A.id = inputs.account_id
    AND (
        inputs.sub_id IS NULL OR
        B.subsidiary = inputs.sub_id
    )

What I didn’t realise at the time was that the filter line AND BUILTIN.DF(B.category) LIKE '%' || inputs.budget_type || '%' was expensive. I needed to find a way to filter the budget I wanted.

Thankfully there was a better way, and I added another CTE to help filter the result I wanted. The problem I had was that I only wanted the latest budget and the only way to fetch the top row from a CTE expression was to use a window function.

Here how the much improved result looked:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
-- SuiteQL query to fetch budget values for specific GL accounts
-- @param `budget_type` is a string to search for the budget values needed, eg. FINAL, DRAFT (etc)
-- @param `account_id` is the account id to fetch
-- @param `run_date` (optional) fetching values for a specific date, needed in ISO format 'YYYY-MM-DD'
WITH inputs AS (
    SELECT
        ? AS budget_type,
        ? AS account_id,
        TO_DATE(?, 'YYYY-MM-DD') AS run_date
),
-- This CTE is to get the budget needed. It will find the latest version if you are using 'FINAL' or 'DRAFT'
-- that can be found in the name of the Budget Category. It will also exclude any Budget Category's that have
-- been made inactive. Refer to Accounting Lists > Budget Category for details.
-- As the Budget Category's are named as YYYY (FINAL|DRAFT) - eg. 2025 FINAL or 2023 DRAFT it's easy to find
-- the budget I want by placing the names of these budgets in descending order.
valid_budget_category AS (
    SELECT
        B.category AS category_id,
        BUILTIN.DF(B.category) AS category_name,
        ROW_NUMBER() OVER (ORDER BY BUILTIN.DF(B.category) DESC) AS rn
    FROM
        Budgets B
        LEFT JOIN BudgetCategory BC
            ON B.category = BC.id
        CROSS JOIN inputs
    WHERE
        BC.isinactive = 'F'
        AND BUILTIN.DF(B.category) LIKE CONCAT('%', CONCAT(inputs.budget_type, '%'))
)
SELECT
    B.account AS credit_account,
    A.id AS debit_account,
    B.subsidiary AS sub_id,
    VC.category_name AS budget_category,
    TO_CHAR(AP.startdate, 'YYYY-MM') AS period,
    BM.amount AS monthly_amount,
FROM
    BudgetsMachine BM
    LEFT JOIN Budgets B
        ON B.id = BM.budget
    LEFT JOIN AccountingPeriod AP
        ON BM.period = AP.id
    LEFT JOIN Account A
        ON B.account = A.id
    LEFT JOIN valid_budget_categories VC
        ON B.category = VC.category_id
    CROSS JOIN inputs
WHERE
    VC.rn = 1
    AND A.id = inputs.account_id
    AND ((
        inputs.run_date IS NOT NULL AND
        TO_CHAR(AP.startdate, 'YYYY-MM') = TO_CHAR(inputs.run_date, 'YYYY-MM')
    ) OR (
        inputs.run_date IS NULL AND
        TO_CHAR(AP.startdate, 'YYYY-MM') = TO_CHAR(CURRENT_DATE, 'YYYY-MM')
    ))

Massive Saving!

Upon running this new SuiteQL query the runtime went down to 5029ms!! 😳

From this I learned that I needed to watch for queries using the LIKE with % wildcard characters and to better filter what specific budget I wanted early.

Anyway, I hope that might help you to optimise your queries!

Massive Saving!#

Massive Saving!