Optimizing DevExpress GridView: Preventing SQL Reloads on Grouping

INTRODUCTION

During a recent engagement with a client in the logistics and supply chain sector, our team was tasked with stabilizing a mission-critical legacy dashboard. The system was a high-traffic ASP.NET WebForms application used by dispatchers to monitor thousands of active shipments. The core of this dashboard was a data-heavy grid that grouped shipments by status, region, and carrier.

The problem surfaced during peak operational hours. Dispatchers reported that simply clicking to expand a group (e.g., opening the “In Transit” group) caused the interface to freeze for several seconds. We realized that what should have been a lightweight client-side interaction was triggering a full server-side lifecycle event.

Instead of just displaying pre-loaded data, the application was re-executing heavy SQL queries against the production database for every single UI interaction. This article details how we diagnosed this lifecycle conflict and implemented a caching layer to prevent unnecessary data reloads.

PROBLEM CONTEXT

The application relied heavily on the DevExpress ASPxGridView control to present tabular data. The business requirement allowed users to group records dynamically to view aggregated data. Under the hood, the architecture was relatively standard for its time:

• Frontend: ASP.NET WebForms with DevExpress UI controls.
• Backend: SQL Server.
• Data Access: ADO.NET with raw SQL queries constructed in the code-behind.

The grid was configured to load data inside the Page_Init event. The developers’ original intent was to ensure data was always available for the grid to render. However, this implementation failed to account for the way DevExpress handles callbacks. When a user expanded a group row, the grid performed a callback to the server to render the new rows. Because the data loading logic was unconditional in the initialization phase, the server re-fetched the entire dataset from SQL Server before determining which specific rows to render.

WHAT WENT WRONG

The performance bottleneck was caused by a misunderstanding of the ASP.NET page lifecycle in conjunction with DevExpress callbacks.

The original implementation looked similar to this:

protected void Page_Init(object sender, EventArgs e) {
    // This executes on every PostBack AND Callback
    LoadShipmentData(); 
}

private void LoadShipmentData() {
    // Expensive SQL operation running 50+ times per session
    DataTable dt = ExecuteHeavyQuery(); 
    gvShipments.DataSource = dt;
    gvShipments.DataBind();
}

Even though the grid had EnableRowsCache="True", it was rendered ineffective. By explicitly calling DataBind() inside Page_Init on every request, we were forcing the grid to discard its cached structure and accept “fresh” data, which triggered the expensive SQL query every time a user clicked a plus sign.

From a database perspective, this generated an I/O storm. A simple UI navigation action by 100 concurrent users resulted in thousands of redundant execution plans being generated per minute.

HOW WE APPROACHED THE SOLUTION

To resolve this, we needed to separate the Data Retrieval (getting data from SQL) from the Data Binding (linking data to the Grid).

1. Caching Strategy

We needed a temporary storage mechanism to hold the data set across callbacks. Since this was a WebForms application with moderate concurrency, we opted to use the server-side Session object (or Cache object depending on scope) to store the DataTable. This ensures the SQL query runs only once per user session or until the data is explicitly refreshed.

2. Conditional Binding

We had to refactor the Page_Init logic. Instead of blindly loading data, we shifted the responsibility to the Grid’s own DataBinding event. This ensures that data is only fetched when the Grid explicitly requests it, rather than forcing it during page initialization.

3. Handling Callbacks

We utilized the IsCallback and IsPostBack properties to determine if a full reload was actually necessary.

FINAL IMPLEMENTATION

The solution involved modifying the code-behind to prioritize cached data. We implemented a pattern where the application first checks for existing data in the Session before attempting to contact the database.

Here is the sanitized, generic version of the corrected implementation:

protected void Page_Init(object sender, EventArgs e) {
    // Only bind if we are not causing a full reload, or let the grid handle it internally
    // We attach the event handler here, but we do NOT call DataBind() explicitly
    gvGenericList.DataBinding += GvGenericList_DataBinding;
}

protected void Page_Load(object sender, EventArgs e) {
    if (!IsPostBack && !IsCallback) {
        // Initial load only
        gvGenericList.DataBind();
    }
}

protected void GvGenericList_DataBinding(object sender, EventArgs e) {
    // Assign the data source from our caching method
    gvGenericList.DataSource = GetCachedData();
}

private DataTable GetCachedData() {
    // Check if data exists in Session to avoid SQL trip
    if (Session["SessionDataKey"] == null) {
        // Data not in cache, execute SQL
        DataTable dt = FetchDataFromDatabase();
        Session["SessionDataKey"] = dt;
    }
    
    // Return cached data
    return (DataTable)Session["SessionDataKey"];
}

private DataTable FetchDataFromDatabase() {
    // Standard ADO.NET fetching logic
    // This now runs ONLY once per session or on explicit refresh
    DataTable dt = new DataTable();
    using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["AppDb"].ConnectionString)) {
        using (SqlCommand cmd = new SqlCommand("SELECT * FROM Shipments WHERE Status = @Status", conn)) {
            conn.Open();
            using (SqlDataAdapter da = new SqlDataAdapter(cmd)) {
                da.Fill(dt);
            }
        }
    }
    return dt;
}

Key Architectural Changes:

• Event-Driven Binding: Moving logic to OnDataBinding allows the Grid to control when it needs data.
• Session Caching: The SQL query executes once. Subsequent grouping, sorting, and paging operations retrieve the DataTable from memory, which is effectively instant.
• Reduced Latency: Group expansion time dropped from ~4 seconds to <200ms.

LESSONS FOR ENGINEERING TEAMS

This optimization provided several key takeaways for engineering teams handling legacy modernization or data-intensive web applications:

• Understand the Lifecycle: In ASP.NET (and even modern frameworks like React), understanding exactly when data is requested is as important as how it is requested.
• Don’t Fight the Framework: DevExpress and Telerik controls have built-in caching mechanisms. Manually forcing data binds often overrides these optimizations.
• Cache Near the Consumption: For read-heavy views where real-time data isn’t critical (second-by-second), caching the result set in memory drastically reduces database costs.
• Instrument Your Queries: We only found this because we monitored SQL profilers during UI interactions. Always verify that a UI click doesn’t trigger a backend avalanche.
• Legacy Code Requires Specialized Care: Modernizing WebForms requires specific expertise. It is often more cost-effective to optimize the existing logic than to rewrite the entire platform.

WRAP UP

By shifting from unconditional data loading to a cached, event-driven model, we eliminated 95% of the database load generated by the dashboard. This ensured that dispatchers could group and filter data instantly, restoring faith in the system’s reliability. Whether you are maintaining legacy systems or building new cloud-native architectures, understanding data flow efficiency is critical.

If you need assistance optimizing high-performance dashboards or modernizing legacy .NET applications, contact us to discuss your engineering needs.