# SyteLine Large Table Scan Cost Calculator: Scan vs Index Seek in SQL Server > Source: https://netray.co/tools/syteline-large-table-scan-cost-calculator > Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems. > Section: Free Tools ## Short answer See why a non-sargable filter on matltran_mst, jobtran_mst or ledger_mst takes seconds while an index seek takes milliseconds. ## Category developer ## Erp Infor SyteLine ## Audience - SyteLine developer - DBA - Report writer ## Tool **Kind:** calculator **Inputs:** #### Table rows **Key:** rows **Type:** number **Default:** 20000000 **Min:** 1000 **Step:** 100000 **Unit:** rows **Help:** Use sp_spaceused on the table, for example matltran_mst. #### Average row size **Key:** row_bytes **Type:** number **Default:** 450 **Min:** 20 **Max:** 8060 **Step:** 10 **Unit:** bytes **Help:** Data size from sp_spaceused divided by rows. #### Rows the query returns **Key:** returned **Type:** number **Default:** 500 **Min:** 1 **Step:** 10 **Unit:** rows #### Index depth **Key:** depth **Type:** number **Default:** 4 **Min:** 1 **Max:** 8 **Step:** 1 **Unit:** levels **Help:** index_depth from sys.dm_db_index_physical_stats; 3 to 4 is typical for large tables. #### Index covers the query **Key:** covering **Type:** select **Default:** 0 **Options:** ##### No, key lookups needed **Value:** 0 ##### Yes, covering index **Value:** 1 #### Storage read speed for cold reads **Key:** disk **Type:** number **Default:** 300 **Min:** 10 **Step:** 10 **Unit:** MB/s #### Share of table in buffer cache **Key:** cache_pct **Type:** slider **Default:** 30 **Min:** 0 **Max:** 100 **Step:** 5 **Unit:** % #### Memory read speed **Key:** mem **Type:** number **Default:** 4000 **Min:** 100 **Step:** 100 **Unit:** MB/s **Outputs:** #### Table pages (scan logical reads) **Key:** table_pages **Expr:** ceil(rows * row_bytes / 8060) **Format:** integer **Description:** Roughly what SET STATISTICS IO reports as logical reads for a full scan. #### Table size **Key:** table_mb **Expr:** table_pages * 8 / 1024 **Format:** number **Description:** MB, at 8 KB per page. #### Full scan time **Key:** scan_seconds **Expr:** table_mb * (1 - cache_pct / 100) / disk + table_mb * (cache_pct / 100) / mem **Format:** decimal **Primary:** true **Description:** Seconds: uncached share at disk speed plus cached share at memory speed. #### Index seek page reads **Key:** seek_reads **Expr:** if(eq(covering, 1), depth + ceil(returned * row_bytes / 8060), depth + returned) **Format:** integer **Description:** Covering: traverse the index then read the matching leaf pages. Not covering: one key lookup page per row, worst case. #### Index seek data read **Key:** seek_mb **Expr:** seek_reads * 8 / 1024 **Format:** decimal **Description:** MB. #### Index seek time (all cold) **Key:** seek_seconds **Expr:** seek_mb / disk **Format:** decimal **Primary:** true **Description:** Seconds, assuming nothing is cached. #### Scan is slower by **Key:** ratio **Expr:** scan_seconds / seek_seconds **Format:** number **Description:** Times. **Chart:** #### Scan vs seek time as the table grows **Type:** line **X:** rows **From:** 1000000 **To:** 100000000 **Steps:** 40 **Series:** - scan_seconds - seek_seconds **Disclaimer:** A simplified IO model. Random reads for key lookups are slower per MB than sequential scans on spinning disk, and the optimizer may choose a scan on purpose when many rows match. Confirm with the actual execution plan and SET STATISTICS IO. ## Content **Intro:** This calculator compares the IO cost of a full scan with an index seek on large SyteLine tables such as matltran_mst, jobtran_mst and ledger_mst on SQL Server. It shows why one non-sargable filter in a report or IDO can turn milliseconds into tens of seconds as history grows. **Sections:** #### The basis: pages, not rows **Body:** SQL Server stores data in 8 KB pages, and a row can hold up to 8,060 bytes of in-row data. The calculator uses 8,060 bytes as the usable space per page, which is close to the real figure (a page has a 96-byte header and a row offset array) and ignores fill factor, so it slightly underestimates page count. A scan reads every page of the table: pages = rows x row bytes / 8,060. A seek walks the B-tree from root to leaf (index depth pages) and then reads only the matching rows. If the index does not include every column the query needs, each row costs a key lookup into the clustered index; worst case that is one page per row. Time is MB read divided by read speed, split between cached and uncached pages. #### What makes SyteLine queries scan **Body:** The common culprits in SyteLine reports, IDO filters and custom procedures are predicates the optimizer cannot match to an index. Wrapping the column in a function, such as CONVERT(date, trans_date) = '2026-10-01' on matltran_mst, forces evaluation on every row; write a range on the raw column instead. A leading wildcard, item LIKE '%-100', cannot seek. Implicit conversions between varchar and nvarchar parameters do the same. Long OR chains across different columns often scan too, while IN on one indexed column usually seeks. Filters built in a form's filter-in-place with wildcards end up in the same IDO query, so users can trigger scans without any custom code. **Bullets:** - Use trans_date >= @from AND trans_date < @to, not functions on the column - Avoid leading % in LIKE on large tables - Match parameter data types to column types - Prefer IN on one indexed column over OR across columns #### Worked example **Body:** A 20 million row matltran_mst at 450 bytes per row is about 1.1 million pages, roughly 8.5 GB. With 30% of it cached, a full scan reads about 6 GB from storage at 300 MB/s plus 2.6 GB from memory, about 21 seconds, and it also pushes useful pages out of the buffer pool for everyone else. A seek returning 500 rows through a 4-level index with key lookups reads about 504 pages, under 4 MB, roughly 13 milliseconds even fully cold. The ratio is in the thousands. Grow the table to 60 million rows and the scan triples while the seek stays flat, which is why reports that were fine at go-live degrade every year. #### Check the real cost in SQL Server **Body:** Run the query in SQL Server Management Studio with SET STATISTICS IO ON and SET STATISTICS TIME ON, then read logical reads per table in the Messages tab: compare that figure with the table pages output here. Include the actual execution plan and look for Clustered Index Scan or Table Scan on the large table, and for Key Lookup operators with high row counts. The estimated subtree cost in the plan is the optimizer's internal unit, not seconds, so use it only to compare plans. For problems happening right now, use the blocking and long-running query script tool to see which session is scanning. **Cta:** If you want people to ask SyteLine history questions in plain English without writing scans against production, we can run a private AI layer on your servers that uses indexed, reviewed queries on a reporting copy. **Faq:** #### How do you calculate query cost in SQL Server? **Answer:** SQL Server shows an estimated subtree cost in the execution plan, a unitless optimizer figure for comparing plans. For real cost, use SET STATISTICS IO ON to get logical and physical reads per table and SET STATISTICS TIME ON for CPU and elapsed time. Logical reads times 8 KB is the data volume touched. #### Is IN faster than OR in SQL Server? **Answer:** For several values on the same column, IN and OR usually produce the same plan, because the optimizer rewrites one into the other. The difference appears with OR across different columns, which often prevents a single index seek and leads to a scan. Check the plan rather than relying on the syntax. #### Why is my query on matltran_mst slow? **Answer:** matltran_mst holds every material transaction and grows for years. A filter that cannot use an index, such as a function on trans_date or a leading wildcard on item, scans the whole table. Rewrite the filter as a range on the raw column and check that an index supports the filter columns. #### Does adding an index always help? **Answer:** No. Each index adds write cost to every insert and update on the table, and SyteLine posts transactions constantly. Add an index only when the plan shows a scan or heavy key lookups for a frequent query, prefer adding included columns to an existing index, and test on a copy before production. ## Search terms cost of query in sql, sql server cost of query, how to calculate query cost in sql, in vs or sql performance, cost of the query, matltran_mst slow query, sql server table scan vs index seek, syteline query performance --- Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json