Developer ToolsFree calculatorPDF, Excel, Word exportInfor SyteLineAll Infor SyteLine (CloudSuite Industrial) tools

SyteLine Large Table Scan Cost Calculator: Scan vs Index Seek in SQL Server

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.

Your numbers

Table rows

rows

Use sp_spaceused on the table, for example matltran_mst.

Average row size

bytes

Data size from sp_spaceused divided by rows.

Rows the query returns

rows

Index depth

levels

index_depth from sys.dm_db_index_physical_stats; 3 to 4 is typical for large tables.

Index covers the query

Storage read speed for cold reads

MB/s

Share of table in buffer cache

30 %

Memory read speed

MB/s

Your results

Full scan time

21.0094

Seconds: uncached share at disk speed plus cached share at memory speed.

Index seek time (all cold)

0.0131

Seconds, assuming nothing is cached.

Table pages (scan logical reads)

1,116,626

Roughly what SET STATISTICS IO reports as logical reads for a full scan.

Table size

8,723.64

MB, at 8 KB per page.

Index seek page reads

504

Covering: traverse the index then read the matching leaf pages. Not covering: one key lookup page per row, worst case.

Index seek data read

3.9375

MB.

Scan is slower by

1,600.72

Times.

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.

Save results:

Scan vs seek time as the table grows

Sweeps Table rows from 1000000 to 100000000, holding your other inputs fixed.

Send us your slowest SyteLine query

Share the query text and its STATISTICS IO output and we will suggest a sargable rewrite or index change, checked against SyteLine posting load.

Optional. The tool and every export work without this. No spam.

Was this tool useful?

The basis: pages, not rows

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

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.

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

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

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.

Frequently Asked Questions

How do you calculate query cost in SQL Server?

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?

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?

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?

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.

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.

Talk to an Expert Explore More Free Tools