Do Ignition Perspective Table Pagers Reduce Database Load?

Why don't the usual fixes make a large Perspective table faster?

A slow table showing a large SQL result set on Ignition 8.1.45 Perspective usually gets one of three fixes. None of them works until you know which stage is slow.

• Turning on the built-in Pager to cut database load. The pager splits rows that are already in the table's data property into pages. The query still returns every row, the gateway still holds all of them, and the session still receives all of them. Database cost does not change.
• Turning on Virtualized to "fetch more rows as you scroll." Virtualization does not request rows from the database on scroll. It limits how many rows the browser builds as DOM elements at one time. The dataset is already fully loaded.
• Writing a custom OFFSET pager right away. This moves load to the database, but it costs you the table's built-in sorting and filtering across the full set. It also adds a count query and a re-query on every page change. For a few thousand rows it usually solves a problem you don't have.

Each of these acts on a different stage of the data chain. Look at the trend first: find the slow stage, then pick the control that acts on that stage.

Where does the load actually land between SQL and the browser?

Follow the rows from source to screen:

1. The query binding or named query runs the SELECT on the database.
2. The gateway receives the result set and writes it to the table's data property.
3. The data property syncs to the browser session.
4. The browser renders rows. This includes cell formatting and any embedded views used as cell renderers.

The built-in pager and Virtualized both act only at stage 4. The pager works with the data already loaded, so to use it you load everything. Virtualization stops the browser from building the image of every row, its formatting, and any nested views, and draws only the rows near the viewport. With virtualization on, the built-in pager performs well with a few thousand rows.

Tuning a pager does not fix a slow query, and rewriting a query does not fix a render stall.

Do Virtualized and Pager work together or cancel each other?

They stack. The pager decides which slice of loaded rows belongs to the current page. Virtualization decides which rows of that slice get built in the DOM.

At 10 rows per page with no scrollbar, every row on the page is visible, so virtualization has almost nothing to skip. It also costs almost nothing, so leave it on. At large page sizes, or with the pager off, virtualization carries the render load. With embedded views as cell renderers, virtualization matters most, because each rendered row instantiates those views.

How do I measure which stage is slow before changing anything?

1. Time the query alone. Run the exact SELECT in the database client or the Ignition query browser against production-sized data. Record execution time and row count. Check the execution plan for full scans on your filter and ORDER BY columns.
2. Check the binding polling rate. A query binding that polls re-runs the full SELECT on every interval, for every open session. Multiply rows by sessions by polls per minute to get the real database load.
3. Time binding completion to render. Open the browser developer tools on the Perspective session. Measure the gap between the data arriving and the table becoming responsive. A fast query followed by a long freeze points at stage 4.
4. Toggle Virtualized in the Designer or a test view with the same data. If scroll and load times change sharply, the bottleneck is rendering, and you are done with the database side.
5. Count columns and renderers. Many columns, per-cell style scripts, or embedded views multiply render cost per row.

When is custom OFFSET paging justified, and how do I build it?

Build a server-side pager when the measurements show the database or payload stage is the problem. That means the result set is too large to move per session, the query is slow even when indexed, or many sessions hit the same view. Keep the built-in pager until you know you need this.

1. Disable the table's built-in pager. Its page count comes from the rows in the data property. With only one page of rows loaded, it would show a single page. Build the paging controls as separate components outside the table: previous and next buttons, a page number entry, and a page-size dropdown.
2. Add custom properties on the view. Add a page index, a page size, and a total row count.
3. Write a count query with the same WHERE clause as the data query. Bind it to the total-row property. Compute total pages as ceil(totalRows / pageSize) in an expression on the paging controls.
4. Parameterize the data query with offset and page size. Calculate offset as pageIndex * pageSize for a zero-based index. Bind the table data to this query so a change to the page index re-executes it.
5. Clamp the page index to the range 0 to totalPages - 1. This handles a count change when rows are inserted or deleted.
6. Turn polling off on the data query unless the page must refresh live. Page changes already trigger re-execution.

-- Count query (same filter as data query)
SELECT COUNT(*) AS total_rows
FROM history_table
WHERE event_time >= :startTime;

-- Page query, SQL Server / ANSI style
SELECT id, event_time, tag_name, value
FROM history_table
WHERE event_time >= :startTime
ORDER BY event_time DESC, id DESC
OFFSET :offset ROWS FETCH NEXT :pageSize ROWS ONLY;

-- MySQL / PostgreSQL / SQLite style
-- ... ORDER BY event_time DESC, id DESC LIMIT :pageSize OFFSET :offset;

The table and column names above are placeholders. Match the paging syntax to your database dialect.

How do I verify the fix, and what breaks after switching to server-side paging?

Repeat the baseline measurements from the diagnostic section. The database should now show a bounded row count per execution. The session should populate the table without a render stall. Scrolling within a page should be smooth with Virtualized on. Open the view in several sessions at once and confirm database load scales with page changes, not with a polling interval.

These pitfalls recur with this pattern:

• Non-deterministic ORDER BY. Without a unique tiebreaker, such as the primary key, rows repeat or vanish between pages. Always end the ORDER BY with a unique column.
• Deep OFFSET cost. The database still reads and discards all skipped rows, so late pages get slower on very large tables. Keyset paging (WHERE id < :lastId) avoids this, but it only supports next and previous navigation, not jumping to a page number.
• Sorting and filtering scope. The table's built-in sort and filter now act only on the current page. Move sort column and filter text into query parameters if users expect them to apply to the whole set.
• Count drift. On tables that change quickly, the count and the page query can disagree for a moment. Clamp the page index and refresh the count on page changes.
• Selected columns. SELECT * inflates the payload at every stage. Return only the columns the table displays.

FAQ