Interactive Grid Performance Tuning: Handling Large Data Sets

When the Grid Gets Slow

Interactive Grids with thousands of rows can become sluggish due to initial row count queries, excessive DOM rendering, and heavy client-side processing. These optimization techniques keep grids responsive even with large data sets.

Lazy Loading and Scroll Paging

By default, IG fetches and renders one page of rows at a time. As the user scrolls, it fetches the next page from the server. This “scroll paging” is already built in, but you can tune it:

function(config) {
    // Fetch 50 rows per batch (default is usually 25)
    config.defaultGridViewOptions = config.defaultGridViewOptions || {};
    config.defaultGridViewOptions.rowsPerPage = 50;
    return config;
}

Eliminating the Row Count Query

The biggest performance drain on large tables is the COUNT(*) query that IG runs to display “Rows 1-50 of 1,234,567.” Disable this by setting the Maximum Row Count to a reasonable limit or disabling it entirely:

function(config) {
    // Don't count total rows
    config.defaultGridViewOptions = config.defaultGridViewOptions || {};
    config.defaultGridViewOptions.footer = false;
    return config;
}

-- Or in IG Attributes:
-- Maximum Row Count: 1000
-- When No Data Found: "No data found"

Server-Side Query Optimization

The IG source query runs on every page fetch. Ensure it is optimized:

-- Include indexes on columns commonly used as IG filters
CREATE INDEX idx_orders_status ON orders(status);
CREATE INDEX idx_orders_date ON orders(order_date);

-- Avoid calling PL/SQL functions in the SELECT list
-- BAD: runs the function for every row
SELECT order_id, get_customer_name(customer_id) AS cust_name FROM orders;

-- BETTER: join to get the value
SELECT o.order_id, c.customer_name
  FROM orders o JOIN customers c ON o.customer_id = c.customer_id;

Reduce Column Count

Every column adds to the DOM weight and rendering time. Only include columns that users actually need. Move rarely-used columns to a drill-down detail page rather than displaying them in the grid. For computed columns, calculate them in SQL rather than adding JavaScript computations that run for every visible row.

Pre-Filter With Page Items

Require users to set filter criteria (date range, department, status) in page items before the grid loads. Use a “Search” button pattern where the grid only queries the database after filters are applied, rather than loading all data on page entry:

-- IG source query with required filter
SELECT order_id, customer_name, order_date, status, total
  FROM orders
 WHERE order_date BETWEEN :P10_DATE_FROM AND :P10_DATE_TO
   AND (:P10_STATUS IS NULL OR status = :P10_STATUS);

Set the region’s “Lazy Loading” to Yes so it does not execute until the user clicks Search and the page items are populated.

Monitor With Browser DevTools

Use the browser’s Network tab to see how long each IG AJAX fetch takes. If fetches take more than 500ms, optimize the source query. Use the Performance tab to identify JavaScript bottlenecks in rendering. If the grid feels sluggish during scrolling, reduce the number of visible columns or simplify HTML expressions.