Why your SyteLine report is slow: T-SQL tuning for AR aging and other heavy reports

Free script: A read-only script that ranks your slowest cached report procedures is in my free SyteLine SQL diagnostic scripts.

Step 1: Find out where the time goes

A report that "takes 6 minutes" is three timers added together:

If the stored procedure finishes in 5 seconds in SSMS, tuning SQL won't help. Look at the queue or the layout instead. If it takes 5 minutes in SSMS too, keep reading.

To find the procedure, open the report form, find the background task name, and look it up on the Background Task Definitions form. That shows what the task executes.

Step 2: Measure the stored procedure properly

Work in a test database restored from recent production data. Performance on a test database with a tenth of the data tells you nothing.

SET STATISTICS TIME ON;
SET STATISTICS IO ON;

-- run the report procedure with the user's real parameters here
-- (turn on "Include Actual Execution Plan" in SSMS first: Ctrl+M)

Write down three numbers before you change anything: elapsed time, the logical reads on the biggest tables, and the row count returned. These numbers are your baseline and, later, your proof.

Step 3: Read the plan for the usual suspects

In the actual execution plan, start with the most expensive operators and the thickest arrows. In SyteLine reports, the same few patterns cause most of the trouble.

1. Row-by-row cursors

Many report procedures loop through records one at a time, running several queries per row. On 500 customers that's fine. On 50,000 open AR transactions it's minutes. Fix: rewrite the loop as one set-based query. See the aging example below.

2. Functions wrapped around columns

-- slow: SQL Server can't use an index on inv_date
WHERE CONVERT(date, ar.inv_date) <= @AsOfDate

-- fast: compare the raw column to a computed boundary
WHERE ar.inv_date < DATEADD(day, 1, @AsOfDate)

The same goes for ISNULL(column, ...), UPPER(column), and user-defined scalar functions in WHERE or JOIN clauses. A scalar function in the SELECT list runs once per row and quietly multiplies the cost.

3. Joins without site_ref

In SyteLine, keys like cust_num or item are only unique within a site. A join that leaves out site_ref can match far more rows than intended. In a multi-site database it also produces wrong totals. Always join on the key and site_ref.

4. Scans on the big transaction tables

Tables like artran_mst, matltran_mst, jobtran_mst and ledger_mst grow forever. A scan that took a second at go-live takes a minute five years later. If the plan shows a full scan of one of these tables to fetch a small date range or one customer, an index is probably missing. You can sometimes also see a "missing index" hint in green in the plan.

5. Stale statistics and parameter sniffing

If the report is fast for one user and slow for another, or fast after a restart and slow by Friday, suspect outdated statistics or a cached plan built for very different parameters. Check when statistics were last updated with your DBA before rewriting anything.

Step 4: Fix it safely

From least to most invasive:

1. Narrow what the report asks for. Sensible default date ranges and required filters often cut run time more than any tuning.
2. Update statistics and maintain indexes. This is routine DBA work, low risk, and often overdue.
3. Add a targeted index. Build it in test, measure it, and document it. Re-check it after every upgrade, because a SyteLine upgrade can rebuild tables.
4. Rewrite the logic in a custom copy of the report. Don't edit Infor's standard stored procedure. Upgrades and service packs can overwrite it, and you lose your fix or, worse, half of it. Copy the report, point the copy at your own procedure, and give users the new report.

Example: aging buckets without a cursor

This is the set-based pattern behind most fast aging reports. It's an illustration written for this article, not Infor's code. Check how your own data signs payments and credits before using it, and reconcile the totals against the standard report.

DECLARE @Site    nvarchar(8) = N'MAIN',
        @AsOfDate date       = '2026-10-31';

WITH open_items AS (
    SELECT  ar.cust_num,
            ar.inv_num,
            MIN(ar.due_date) AS due_date,
            SUM(CASE WHEN ar.type IN ('P', 'C') THEN -ar.amount
                     ELSE ar.amount END) AS balance   -- verify sign rules in your data
    FROM    artran_mst AS ar
    WHERE   ar.site_ref = @Site
      AND   ar.inv_date < DATEADD(day, 1, @AsOfDate)
    GROUP BY ar.cust_num, ar.inv_num
    HAVING  SUM(CASE WHEN ar.type IN ('P', 'C') THEN -ar.amount
                     ELSE ar.amount END) <> 0
)
SELECT  oi.cust_num,
        SUM(CASE WHEN DATEDIFF(day, oi.due_date, @AsOfDate) <= 0              THEN oi.balance ELSE 0 END) AS [current],
        SUM(CASE WHEN DATEDIFF(day, oi.due_date, @AsOfDate) BETWEEN 1  AND 30 THEN oi.balance ELSE 0 END) AS [1_30],
        SUM(CASE WHEN DATEDIFF(day, oi.due_date, @AsOfDate) BETWEEN 31 AND 60 THEN oi.balance ELSE 0 END) AS [31_60],
        SUM(CASE WHEN DATEDIFF(day, oi.due_date, @AsOfDate) BETWEEN 61 AND 90 THEN oi.balance ELSE 0 END) AS [61_90],
        SUM(CASE WHEN DATEDIFF(day, oi.due_date, @AsOfDate) > 90              THEN oi.balance ELSE 0 END) AS [over_90],
        SUM(oi.balance) AS total
FROM    open_items AS oi
GROUP BY oi.cust_num
ORDER BY total DESC;

The data is read once, grouped once, and bucketed in a single pass. There's no loop and no per-customer query. Because the WHERE clause compares raw columns, an index on artran_mst (site_ref, inv_date) can be used if one exists.

Step 5: Prove it

Before the new version goes live:

• Totals match. Grand total, and a handful of individual customers, against the old report for the same date.
• Timing is recorded. Old vs new elapsed time and logical reads from Step 2.
• The requester signs off after running it themselves in test.

Write the before/after numbers into the ticket. They justify the change and help the next person who touches the report.

On multi-tenant cloud

In multi-tenant CloudSuite Industrial you can't run SQL against the database or add indexes. The principles still hold: narrow the parameters, avoid heavy row-by-row logic in custom IDO methods, and use filtered IDO loads instead of loading everything and filtering on the form. For standard reports that are slow in cloud, open a case with Infor and include the parameters and timings.

FAQ

Why is my SyteLine AR aging report so slow?

Usually because it processes transactions row by row and scans a large artran_mst table. A set-based rewrite in a custom copy of the report, plus an index that fits its filters, typically brings it down to seconds.

How do I find the stored procedure behind a SyteLine report?

Find the report's background task name, then look it up on the Background Task Definitions form to see what the task runs.

Should I modify Infor's standard report stored procedures?

No. Upgrades can overwrite them. Copy the report and point the copy at your own procedure.

Is adding an index to a SyteLine table safe?

Usually, if you test it, measure it, document it, and re-check it after upgrades. Don't add indexes to production without testing on a copy first.