I’ll address all three optimization areas comprehensively since they work together for optimal WIP reporting performance.
SQL Query Optimization:
Your current query has several inefficiencies. First, it’s missing temporal filtering which causes full table scans of historical data. Second, the GROUP BY on both WorkOrderID and Status is redundant since WorkOrderID is unique. Here’s an optimized version:
SELECT wo.WorkOrderID, wo.Status,
COUNT(mm.MaterialID) as MaterialCount
FROM WorkOrders wo
JOIN MaterialMovements mm ON wo.WorkOrderID = mm.WorkOrderID
WHERE wo.Status IN ('RELEASED', 'IN_PROGRESS')
AND mm.MovementDate >= DATEADD(day, -7, GETDATE())
AND mm.IsActive = 1
GROUP BY wo.WorkOrderID, wo.Status
The MovementDate filter limits the scan to recent data (adjust the 7-day window to match your WIP definition). The IsActive flag ensures you’re only counting current movements, not historical corrections or cancelled transactions.
More importantly, create a filtered index specifically for this query pattern:
CREATE INDEX IX_MaterialMovements_WIP
ON MaterialMovements(WorkOrderID, MovementDate, MaterialID)
WHERE IsActive = 1 AND MovementDate >= DATEADD(day, -30, GETDATE())
This filtered index dramatically reduces index size and improves query performance by only indexing relevant WIP data.
Table Partitioning:
The MaterialMovements table should be partitioned by MovementDate using a sliding window approach. For GPSF 2023.1, implement monthly partitions with automated maintenance:
- Create partition function for monthly boundaries
- Partition MaterialMovements on MovementDate
- Keep 3 months of data in active partitions (current month + 2 prior)
- Archive older partitions to separate filegroup or table
This reduces the working dataset from 800K+ rows to typically under 100K for current WIP queries. The database optimizer will use partition elimination to skip irrelevant historical data automatically.
Critically, ensure your WIP queries include the partition key (MovementDate) in the WHERE clause. Without it, partition elimination won’t occur and you’ll still scan all partitions.
Query Caching:
Enable the GPSF query result cache for performance analysis reports. Navigate to Performance Analysis > Configuration > Cache Settings:
- Enable Query Result Caching: Yes
- Cache TTL for WIP Reports: 120 seconds (2 minutes)
- Cache Invalidation Events: Add ‘MATERIAL_MOVEMENT_CREATED’, ‘WORK_ORDER_STATUS_CHANGED’
- Maximum Cache Size: 500 MB
The 2-minute TTL balances data freshness with performance. During peak hours when 50+ users might view the WIP dashboard simultaneously, the cache serves most requests from memory instead of executing the database query.
The event-based invalidation ensures cache accuracy - when material movements occur or work order statuses change, the relevant cache entries are cleared immediately. This prevents stale data while maintaining performance benefits.
Additionally, implement a materialized view for the most common WIP aggregations:
CREATE MATERIALIZED VIEW MV_WIP_Summary
REFRESH EVERY 5 MINUTES
AS
SELECT wo.Status, wo.ProductLine,
COUNT(*) as OrderCount,
SUM(mm.Quantity) as TotalQuantity
FROM WorkOrders wo
JOIN MaterialMovements mm ON wo.WorkOrderID = mm.WorkOrderID
WHERE wo.Status IN (‘RELEASED’, ‘IN_PROGRESS’)
AND mm.MovementDate >= DATEADD(day, -7, GETDATE())
GROUP BY wo.Status, wo.ProductLine
This pre-computed view refreshes every 5 minutes in the background, providing instant results for summary-level WIP dashboards. Detailed drill-down reports can still use the optimized base query.
Finally, schedule statistics updates during your maintenance window (likely between shifts). Run UPDATE STATISTICS on WorkOrders and MaterialMovements tables nightly to ensure the query optimizer has accurate cardinality estimates. This prevents it from choosing inefficient execution plans during peak hours.
Implement these changes in sequence: query optimization first (immediate impact), then caching (moderate effort, high impact), then partitioning (higher effort, long-term benefit). Monitor query execution times after each phase to measure improvement.