Infor SyteLineFree assessmentPDF, Excel, Word exportInfor SyteLineAll Infor SyteLine (CloudSuite Industrial) tools

SyteLine IDO and SQL Server Performance Risk Assessment

This assessment scores the performance risk of an Infor SyteLine (CloudSuite Industrial) 9 or 10 on-prem installation across 12 areas: data growth, SQL Server maintenance and configuration, custom triggers and EXTGEN overrides, IDO and form patterns, integrations, TaskMan and reporting. Each question shows the SyteLine-specific fix for a weak answer, and the result is a 0-100% risk score with next steps.

0 of 12 answered0%

1. How large are your biggest transaction tables (matltran_mst, ledger_mst, jobtran_mst), and do you purge or archive them?

Check row counts with sp_spaceused or sys.dm_db_partition_stats. Fix: agree a retention policy with finance and audit, then run the SyteLine purge utilities for history you no longer need online, testing on a copy first.

2. How often are index maintenance and statistics updates run on the application database?

Fix: schedule index reorganise or rebuild based on fragmentation from sys.dm_db_index_physical_stats, plus UPDATE STATISTICS on the large _mst tables, in a window outside TaskMan peaks.

3. How many custom (non-Infor) triggers exist on _mst tables?

List them from sys.triggers joined to sys.tables and compare with a clean install. Fix: move logic out of triggers into IDO events or scheduled processes where possible, and keep any remaining trigger set-based, never row by row with cursors.

4. How many EXTGEN_ stored procedure overrides are in place?

An EXTGEN_ procedure replaces the standard procedure of the same base name, so every override is code that runs instead of Infor's on every call and is not upgraded with it. Fix: review the EXTGEN Listing, retire overrides that newer releases made unnecessary, and profile the ones on hot paths (order entry, posting, MRP/APS).

5. Do IDO extension classes or scripts call LoadCollection with RecordCap 0 (no cap) or without an explicit property list?

In Mongoose, RecordCap 0 retrieves all records and -1 uses the system default cap. Fix: always pass a PropertyList with only the properties you need, a tight Filter, a real RecordCap, and a ReadMode suited to the read; in REST v2 calls use props and readonly=true for loads that will not be updated.

6. Do form personalisations add unbound properties filled by a per-row method call (post-query commands)?

A PostQueryCmd (pqc in REST) runs a method once for every row returned, so a grid of 500 rows makes 500 extra calls. Fix: replace per-row methods with a derived property or a joined subcollection computed in one set-based query, or cap the grid.

7. How do integrations (web store, MES, EDI, BI) read SyteLine data?

Fix: replace one-minute polling of full collections with change-based filters (for example RecordDate greater than the last run), longer intervals, or event-driven outbound messages, and give each integration its own user so its load is visible.

8. What is your TaskMan background task backlog at peak?

Look at queued versus running tasks and durations in Background Task History (BGTaskHistory). Fix: stagger scheduled reports and utilities, move heavy jobs out of business hours, and find the longest-running tasks before adding more TaskMan capacity.

9. Is SQL Server max server memory set deliberately on the database server?

Check with sp_configure 'max server memory (MB)' (enable 'show advanced options' first) or in the Memory page of server properties in SSMS. Fix: set it to leave headroom for the OS and anything else on the box (often the case when the utility or web server shares the machine), and confirm the buffer pool is not being trimmed.

10. How is tempdb configured?

Fix: use multiple equally sized tempdb data files (Microsoft's guidance: one per logical processor up to eight, then add files in multiples of four only if allocation contention continues), pre-size them to avoid autogrowth during month-end, and place them on fast storage.

11. How much blocking do you see at month-end close?

Capture it with sys.dm_exec_requests (blocking_session_id) or the blocking script generator linked below. Fix: identify the head blocker (often a long posting, report or custom trigger), shorten its transaction, and add the supporting index.

12. Where do heavy reports and BI queries run during business hours?

Fix: move heavy reporting to a reporting copy (for example an Always On readable secondary, log shipping or a nightly restore) and schedule the rest off-peak through TaskMan.

Was this tool useful?

How to use it

Answer with your DBA and the developer who owns customisations in the room; half of the questions need a quick look at the system rather than a guess. Row counts come from sys.dm_db_partition_stats, triggers from sys.triggers, overrides from the EXTGEN Listing, and background task timing from Background Task History. Each answer scores 0 (no risk) to 3 (high risk). The score is your total divided by the maximum of 36, shown as a percentage. Under 36% is low risk, 36-65% moderate and 66% or more high. The scoring and thresholds are practitioner checks drawn from tuning work on SyteLine installs, not an Infor benchmark or official sizing guidance.

Score what is true today, not what is planned.
If you do not know an answer, pick the highest-risk option: unknown is itself a risk.
Export the result and attach it to your tuning or upgrade ticket.

Why IDO patterns matter as much as SQL

Most SyteLine load reaches SQL Server through the IDO layer, so the way forms and extension classes ask for data decides how much work the database does. Three patterns cause most of it. First, LoadCollection with RecordCap 0, which in Mongoose means no cap, so a filter mistake returns a whole transaction table. Second, loads without an explicit PropertyList, which pull every property including derived ones. Third, PostQueryCmd methods (pqc in the REST API), which run once per returned row and turn one query into hundreds of calls. The fixes are cheap: explicit property lists, tight filters, a realistic RecordCap, readonly loads in REST v2 when no update follows, and set-based derived properties instead of per-row methods.

Reading the cost of a query in SQL Server

When people search for the cost of a query in SQL Server they usually mean the estimated subtree cost shown in an execution plan. It is a relative unit the optimiser uses to compare plans, not seconds or money, and it also drives parallelism through the cost threshold for parallelism setting (default 5). For tuning, measured figures are more useful: logical reads, CPU time and duration from SET STATISTICS IO and TIME, Query Store or sys.dm_exec_query_stats. On SyteLine, sort the plan cache by total logical reads and you will usually find a handful of IDO-generated queries against matltran_mst, jobtran_mst or ledger_mst near the top, often missing a supporting index or carrying a wide filter.

Common mistakes

The usual mistake is adding hardware or TaskMan capacity before finding the expensive work. More memory hides a missing index for a while; more task capacity runs more heavy reports at the same time and increases blocking. Another is turning on every SQL Server option a blog recommends without a baseline. Change one thing, measure through a full business day or a close, and keep the change only if the numbers move. Finally, do not edit Infor objects or drop vendor indexes to speed things up: use EXTGEN overrides, IDO extension classes and added indexes that are documented and reapplied after upgrades.

Frequently Asked Questions

Is SyteLine 10 slower than SyteLine 9 on the same hardware?

Not inherently. Most slowdowns after a move to SyteLine 10 come from carried-over customisations: EXTGEN overrides, triggers and extension classes written for the old release, plus statistics that were not rebuilt after the upgrade. Rebuild statistics, review overrides against the new standard procedures, and capture top queries for a week before blaming the release.

What does an error in SQL Server look like inside SyteLine?

SyteLine usually surfaces the SQL Server message in its error dialog or in the background task history. Common ones in performance cases are Msg 1205 (the session was chosen as deadlock victim), Msg 1222 (lock request time out period exceeded) and command timeouts. Each points to blocking or long transactions, so capture the blocking chain rather than just retrying.

What is a SQL error number and where do I look it up?

Every SQL Server error has a number, severity and state. You can read the message text for any number from sys.messages, and Microsoft documents the engine errors in its Database Engine events and errors reference. For SyteLine, also check whether the error came from a standard procedure or an EXTGEN_ override, since overrides are a frequent source.

How do I check whether reporting is slowing down users?

Compare the time windows of slow-form complaints with Background Task History and with sessions from report or BI users in sys.dm_exec_requests. If the long reads line up with report runs, move those reports to a reporting copy or schedule them off-peak through TaskMan, then compare the next week.

If you want your team to ask SyteLine questions in plain English without adding uncapped loads to the live database, we can set up a read-only private AI layer on your own servers that respects these limits.

Talk to an Expert Explore More Free Tools