# SyteLine IDO and SQL Server Performance Risk Assessment > Source: https://netray.co/tools/syteline-ido-and-sql-performance-assessment > Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems. > Section: Free Tools ## Short answer Score the performance risk of a SyteLine 9 or 10 on-prem install across data growth, IDO and extension code, background tasks and SQL Server configuration, with the specific fix for each weak answer. ## Category syteline ## Erp Infor SyteLine ## Audience - ERP administrator - SQL Server DBA - SyteLine developer - IT manager ## Tool **Kind:** assessment **Questions:** #### How large are your biggest transaction tables (matltran_mst, ledger_mst, jobtran_mst), and do you purge or archive them? **Help:** 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. **Options:** ##### Under a few million rows each, with a scheduled purge or archive routine **Score:** 0 ##### Tens of millions of rows, purged occasionally **Score:** 1 ##### Tens of millions of rows, never purged **Score:** 2 ##### Hundreds of millions of rows, never purged, or we do not know the sizes **Score:** 3 #### How often are index maintenance and statistics updates run on the application database? **Help:** 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. **Options:** ##### Weekly or better, via a maintained job, with statistics updated in between **Score:** 0 ##### Monthly **Score:** 1 ##### Only after a complaint, or only auto update statistics **Score:** 2 ##### Never, or we do not know **Score:** 3 #### How many custom (non-Infor) triggers exist on _mst tables? **Help:** 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. **Options:** ##### None **Score:** 0 ##### 1 to 5, all set-based and documented **Score:** 1 ##### 6 to 20, or some use cursors **Score:** 2 ##### More than 20, or nobody knows what they do **Score:** 3 #### How many EXTGEN_ stored procedure overrides are in place? **Help:** 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). **Options:** ##### None **Score:** 0 ##### 1 to 5, reviewed at the last upgrade **Score:** 1 ##### 6 to 20, or not reviewed since they were written **Score:** 2 ##### More than 20, or no inventory exists **Score:** 3 #### Do IDO extension classes or scripts call LoadCollection with RecordCap 0 (no cap) or without an explicit property list? **Help:** 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. **Options:** ##### No, loads are filtered, capped and use explicit property lists **Score:** 0 ##### A few uncapped loads on small tables **Score:** 1 ##### Several uncapped loads, some on transaction tables **Score:** 2 ##### Common practice, or we have never reviewed extension code **Score:** 3 #### Do form personalisations add unbound properties filled by a per-row method call (post-query commands)? **Help:** 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. **Options:** ##### None on high-traffic forms **Score:** 0 ##### A few, on forms with small result sets **Score:** 1 ##### Several, including forms users open with wide filters **Score:** 2 ##### Many, on order, job or item grids used all day **Score:** 3 #### How do integrations (web store, MES, EDI, BI) read SyteLine data? **Help:** 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. **Options:** ##### Event driven or filtered on changed records only **Score:** 0 ##### Polling every 15 minutes or more, filtered **Score:** 1 ##### Polling IDOs every few minutes with broad filters **Score:** 2 ##### Polling IDOs or tables every minute, full reads **Score:** 3 #### What is your TaskMan background task backlog at peak? **Help:** 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. **Options:** ##### Tasks start within a minute or two even at peak **Score:** 0 ##### Some queueing at month-end only **Score:** 1 ##### Regular waits of 10 minutes or more during the day **Score:** 2 ##### Tasks queue for an hour or more, or users rerun them **Score:** 3 #### Is SQL Server max server memory set deliberately on the database server? **Help:** 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. **Options:** ##### Yes, sized for a dedicated SQL Server, reviewed after hardware changes **Score:** 0 ##### Yes, but not reviewed since install **Score:** 1 ##### Left at the default **Score:** 2 ##### Left at the default and app or utility servers share the machine **Score:** 3 #### How is tempdb configured? **Help:** 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. **Options:** ##### Multiple equal, pre-sized files on fast storage **Score:** 0 ##### Multiple files, but unequal or growing often **Score:** 1 ##### A single data file **Score:** 2 ##### A single small file on the same disk as the data and log files **Score:** 3 #### How much blocking do you see at month-end close? **Help:** 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. **Options:** ##### Rarely, and only for seconds **Score:** 0 ##### Some waits during posting runs **Score:** 1 ##### Users see frozen forms or lock timeout errors **Score:** 2 ##### Deadlock errors (Msg 1205) or processes killed to finish close **Score:** 3 #### Where do heavy reports and BI queries run during business hours? **Help:** 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. **Options:** ##### Against a reporting copy or replica **Score:** 0 ##### Against live, but scheduled off-peak **Score:** 1 ##### Against live, some at peak **Score:** 2 ##### Against live at peak, including ad-hoc BI and Excel connections **Score:** 3 **Bands:** #### Low performance risk **Min:** 0 **Max:** 35 **Verdict:** Your install has the basics in place. Slowdowns you see are more likely to come from a single query or task than from the platform as a whole. **Recommendations:** - Keep index and statistics jobs running and alert when they fail. - Re-run this assessment before and after each upgrade or large customisation. - Use Query Store or the plan cache to watch the top queries by total duration so regressions show up early. #### Moderate performance risk **Min:** 36 **Max:** 65 **Verdict:** Several habits are adding load that users will feel at month-end and as tables grow. Fix the three highest-scoring areas first. **Recommendations:** - Take the three questions where you scored 2 or 3 and apply the fix shown under each one. - Audit extension classes and personalisations for uncapped LoadCollection calls and per-row post-query commands. - Stagger TaskMan schedules and move heavy reports off the live database at peak. - Measure before and after: capture top queries and blocking for one close, then compare. #### High performance risk **Min:** 66 **Max:** 100 **Verdict:** The install is carrying enough data growth, custom code and configuration gaps that problems are likely to compound. A structured tuning review is warranted. **Recommendations:** - Engage a tuning review that covers SQL Server configuration, indexes, EXTGEN overrides, triggers and IDO extension code together. - Fix server memory and tempdb configuration first: they are low-risk changes with broad effect. - Agree a data retention and purge plan for the largest transaction tables. - Run a blocking and wait capture through a full month-end close to find the head blockers. ## Content **Intro:** 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. **Sections:** #### How to use it **Body:** 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. **Bullets:** - 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 **Body:** 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 **Body:** 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 **Body:** 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. **Cta:** 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. **Faq:** #### Is SyteLine 10 slower than SyteLine 9 on the same hardware? **Answer:** 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? **Answer:** 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? **Answer:** 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? **Answer:** 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. ## Search terms syteline 10 performance, syteline slow performance, syteline sql server tuning, error in sql server syteline, sql server cost of query, what is sql error, syteline ido loadcollection performance, syteline taskman backlog --- Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json