Project resource forecast page takes over 30 seconds to load

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

We’re experiencing severe performance issues with the resource forecast page in our Project Management module. When project managers try to load the forecast view for projects with 200+ resources and multiple work breakdown structures, the page consistently takes 30-45 seconds to render. This is causing significant delays in project planning activities.

Our environment is running D365 F&O 10.0.41 with SQL Server backend. We have some customizations around resource allocation logic that were implemented about six months ago. The performance was acceptable initially, but as our project portfolio grew, the load times became unacceptable. I’m wondering if our customizations are creating complex queries that aren’t properly indexed, or if there’s something else we should investigate with the database performance.

system 2

Let me address all three performance factors you’re dealing with systematically.

Database Indexing Strategy: First, create a composite index on your custom resource allocation table covering (ProjectId, ResourceId, StartDate, EndDate). This will eliminate the table scan you identified. Run this in a maintenance window:

CREATE NONCLUSTERED INDEX IX_ResourceAlloc_Forecast

ON CustomResourceAllocation (ProjectId, ResourceId, StartDate, EndDate)

INCLUDE (AllocationPercentage, Status);

Update statistics immediately after. For date range queries, add a filtered index for active allocations: WHERE Status = ‘Active’ AND EndDate >= GETDATE().

Query Complexity from Customizations: Your nested loop issue is critical. Refactor the resource calculation logic to use set-based operations. Instead of looping through each resource, build a single query using CTEs or temp tables to aggregate allocation data. Move calculations that don’t change frequently to a cached table that refreshes on a schedule rather than on-demand. Implement query result caching in your X++ code using the Map class for repeated lookups within the same session.

Code Pattern for Optimization: Replace the nested loop pattern with batch retrieval. Use QueryRun with proper ranges to fetch all required resources in one database round trip, then process them in memory. Implement lazy loading for detailed resource data - only fetch full details when users expand specific resources in the forecast view.

Additional Recommendations: Enable query plan caching for your custom queries. Add hints like OPTION (OPTIMIZE FOR UNKNOWN) if you’re seeing parameter sniffing issues. Consider implementing a materialized view pattern where forecast aggregates are pre-calculated during off-peak hours and stored in a summary table. The forecast page then reads from this optimized table rather than calculating everything real-time.

Monitor the improvement with SQL Server DMVs - specifically sys.dm_exec_query_stats to track query duration changes. You should see load times drop to under 5 seconds for most project sizes after implementing these changes. Test with your largest projects first to validate the improvements scale properly.

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

Have you checked the SQL query execution plans when the forecast page loads? I’d start by enabling SQL profiling to capture the actual queries being generated. In my experience with large resource datasets, missing indexes on custom fields can cause table scans that kill performance. Also check if your customizations are using proper query hints and if they’re leveraging the existing indexes on standard resource tables.

4

Good point about the execution plans. I ran SQL Profiler during a page load and found multiple queries with high logical reads. One query against the custom resource allocation table is doing a full table scan. The table has about 150K records now. I see warnings about missing statistics too. Should I be looking at creating filtered indexes for the date range queries?

sureshdev 5

Tested this on D365 Project Operations with a 50k-row CustomResourceAllocation table—the composite index on ProjectId, ResourceId, StartDate, EndDate dropped forecast page load from 34 seconds to under 3.

6

Filtered indexes could definitely help if you’re consistently querying specific date ranges. But first, make sure your statistics are up to date - outdated stats can cause the optimizer to choose poor execution plans. Run UPDATE STATISTICS on your custom tables. Also, I’ve seen cases where custom X++ code in resource allocation retrieves more data than necessary. Check if you can add filtering at the query level rather than in code loops. This is especially important when dealing with hierarchical project structures.

7

I worked on a similar issue last year. Beyond indexing, review your customization’s use of temporary tables. If you’re building result sets in memory with large datasets, you might be hitting memory pressure. Consider implementing pagination for the forecast view or using query ranges to limit initial data retrieval. Also check if your custom logic is triggering additional queries in loops - that N+1 query pattern is a common culprit in forecast performance issues.

8

I reviewed the custom code and found we are doing some nested loops that could be optimized. There’s also a calculation running for every resource that could be cached. I’m going to work with our dev team on refactoring this. In the meantime, should we consider increasing SQL Server memory allocation or is that just masking the problem?

9

Adding more memory might help temporarily but won’t solve the underlying issues. Focus on the code optimization first. Here’s what I recommend tackling systematically based on your situation.