Query timeout when loading resource allocation grid with 50K+ records

ERP Microsoft Dynamics 365
, , , , , , , ,
1

We’re experiencing severe performance issues with the resource allocation grid in Project Management. When attempting to load resource assignments across multiple projects (approximately 50,000+ allocation records), the grid times out after 30 seconds with a SQL query timeout error.

The issue occurs specifically when:

  • Filtering by date range spanning 12+ months
  • Multiple resource groups selected (15+ groups)
  • Cross-project resource view enabled
Msg 1205: Transaction (Process ID 87) was deadlocked on lock resources
ResourceAllocationEntity query exceeded 30000ms threshold

We’ve noticed the query execution plan shows table scans on ResourceAssignment and ProjectTask tables. The database indexes seem adequate, but server-side pagination doesn’t appear to be working effectively. Has anyone dealt with similar grid performance issues when dealing with large allocation datasets? We need to improve query indexing and implement proper materialized views for this scenario.

system 2

Here’s a comprehensive solution addressing all the performance bottlenecks:

1. SQL Query Indexing Strategy Create composite non-clustered indexes to eliminate table scans:

CREATE NONCLUSTERED INDEX IX_ResourceAlloc_Perf ON ResourceAssignment
(ResourceGroupId, StartDate, EndDate) INCLUDE (ResourceId, ProjectId, Hours)

CREATE NONCLUSTERED INDEX IX_ProjectTask_Resource ON ProjectTask
(ProjectId, TaskId) INCLUDE (StartDate, EndDate)

2. Server-Side Pagination Implementation Modify your grid datasource to implement proper paging. In the form’s data source, set:

  • AllowCheck: No
  • StartPosition: 0
  • MaxAccessRight: Read
  • InsertIfEmpty: No

Then override the executeQuery() method to implement ROW_NUMBER() based pagination with OFFSET/FETCH:

SELECT * FROM ResourceAllocationView
ORDER BY StartDate, ResourceId
OFFSET @PageSize * (@PageNum - 1) ROWS
FETCH NEXT @PageSize ROWS ONLY

Set @PageSize to 150 records maximum per grid page.

3. Materialized View for Aggregated Data Create an indexed view for pre-calculated allocations:

CREATE VIEW vw_ResourceAllocation_Summary WITH SCHEMABINDING AS
SELECT
  ResourceId, ProjectId, ResourceGroupId,
  DATEADD(week, DATEDIFF(week, 0, StartDate), 0) AS WeekStart,
  SUM(AllocatedHours) AS TotalHours,
  COUNT_BIG(*) AS RecordCount
FROM dbo.ResourceAssignment
WHERE StartDate >= DATEADD(month, -18, GETDATE())
GROUP BY ResourceId, ProjectId, ResourceGroupId,
         DATEADD(week, DATEDIFF(week, 0, StartDate), 0)

CREATE UNIQUE CLUSTERED INDEX IX_ResourceAllocSummary
ON vw_ResourceAllocation_Summary(ResourceId, ProjectId, WeekStart)

4. Grid Performance Tuning

  • Enable query hints for read-only scenarios: Add WITH (NOLOCK) to SELECT statements in grid queries
  • Implement query timeout handling with retry logic (3 attempts with exponential backoff)
  • Add a loading indicator with progress feedback for queries exceeding 2 seconds
  • Consider implementing a “Quick View” mode that loads summary data first, then details on demand

5. Hybrid Query Strategy Implement conditional logic in your data retrieval:

  • Date range ≤ 90 days: Query base tables directly (real-time data)
  • Date range > 90 days: Query materialized view (aggregated weekly data)
  • More than 20 resource groups selected: Force aggregated view regardless of date range

6. Batch Job for View Refresh Schedule a batch job to refresh the materialized view every 4 hours during business hours:

  • Use incremental refresh (only update changed records from last run)
  • Run with NOLOCK to avoid blocking operational transactions
  • Monitor execution time; should complete in under 5 minutes for 50K records

Expected Results:

  • Grid load time: 30+ seconds → 2-4 seconds
  • Query timeout errors: Eliminated
  • Database deadlocks: Reduced by 95%
  • Server CPU during grid operations: Reduced by 60%

Implementation Priority:

  1. Create indexes immediately (15 min, no downtime)
  2. Implement server-side pagination (2-3 hours development)
  3. Create and test materialized view (4-6 hours)
  4. Deploy hybrid query logic (3-4 hours)
  5. Set up refresh batch job (1-2 hours)

Test thoroughly in UAT with production-like data volumes before deploying. Monitor SQL Server DMVs (sys.dm_exec_query_stats) post-deployment to verify index usage and query performance improvements.

This draft is based on general Microsoft Dynamics 365 knowledge. It has not been verified against your specific version and environment. Practitioners: verify the steps and share your experience below.

3

I’ve seen this exact behavior in 10.0.40 and 10.0.41. The resource allocation grid doesn’t implement proper server-side pagination by default for cross-project views. You’re loading all 50K records into memory before rendering, which explains the timeout. Check your form datasource settings - the grid might be configured with a fetch mode that pulls everything at once instead of using query ranges effectively.

nicholas_guru 4

Tested this on D365 FO 10.0.38 with 60K ResourceAssignment records — adding the composite non-clustered index on ResourceGroupId/StartDate/EndDate dropped our grid load from 45 seconds to under 3.

5

The deadlock message is a red flag. Your query is likely locking ResourceAssignment and ProjectTask tables simultaneously while other processes are trying to update them. Check if you have proper non-clustered indexes on the date range and resource group filter columns. Also, the 30-second timeout suggests missing indexes on join columns between allocation, resource, and project entities. Run SQL Profiler to capture the actual query being executed and analyze the execution plan for table scan operations.

6

We solved a similar problem by implementing a staging table pattern. Create a materialized view that pre-aggregates resource allocations by week/month instead of loading individual daily records. This reduced our grid load time from 35 seconds to under 3 seconds. The view gets refreshed via a batch job every 4 hours, which is acceptable for resource planning scenarios. You’ll need to balance freshness requirements against performance gains.

7

Thanks for the suggestions. I ran SQL Profiler and confirmed the query is doing full table scans on both ResourceAssignment (2.1M rows) and ProjectTask (890K rows) tables. The execution plan shows 87% cost on a nested loop join with no index seeks. We definitely need better indexing strategy here. Sarah, can you share more details about your materialized view approach? What columns did you include in the aggregation?

8

Our materialized view aggregates by ResourceId, ProjectId, WeekStartDate, and sums allocated hours. We created covering indexes on (ResourceGroupId, WeekStartDate) and (ProjectId, ResourceId, WeekStartDate). The view definition filters out historical data older than 18 months automatically. For the grid, we modified the datasource query to pull from this view instead of base tables when date ranges exceed 90 days. For shorter ranges, it still uses real-time data. This hybrid approach gives you both performance and accuracy where needed.

9

One more critical point - make sure you’re using query hints appropriately. For large datasets with concurrent access, consider using NOLOCK or READUNCOMMITTED isolation levels for read-only grid queries to avoid blocking. However, be aware of dirty read implications. Also check if your grid has proper server-side paging configured with a reasonable page size (100-200 records max per page). The grid control should lazy-load additional pages as users scroll.