Skip to main content

Technical Analysis: OMGT Search & Export Performance

1. Problem Statement​

The OMGT Advanced Search and Data Export functionalities suffer from severe performance degradation, frequently leading to HTTP 500 errors (timeouts) and high server resource consumption. This prevents users from effectively filtering large order volumes and generating requisite reports.

Related Documents:

2. Identified Anti-Patterns​

Location: OrderAdvancedSearchQueryBuilder.FinaliseQuery Pattern:

foreach (EITInterval zipRange in this.listPostCodeRange) {
    ordersQueryTemp.AddRange(ordersQuery.Where(c => ...));
}
ordersQuery = ordersQueryTemp.AsQueryable().Distinct();
  • Issue: Instead of generating a single SQL IN or BETWEEN sequence, the code executes a separate database query for each postcode range.
  • Impact: Multiplies database round-trips and forces the final result set into memory (ordersQueryTemp), breaking database-side paging and sorting.

Location: OrderAdvancedSearchQueryBuilder.BuildQuery Pattern: Manual .Join() calls for tables already present in the source view (OrderTravelProductSearchViewList).

  • Issue: The query builder re-implements joins that are already defined in the SQL View level.
  • Impact: Increases SQL complexity (nested joins), leading to sub-optimal execution plans by the SQL Server optimizer.

C. Row-Based Template Resolution (Export)​

Location: MassActionHandler.ExportExcel calling HandleResolverTemplateExport Pattern:

foreach (OrderTravelProductOMGT otp in selectedOrderTravelProduct) {
   // Re-initializes EITTextTemplate and all resolvers for EVERY row
   List<EITEntityTTResolver> resolvers = this.OnRaiseWepMassActionExportEvent(...);
   rows = this.HandleResolverTemplateExport(rows, ...);
}
  • Issue: The system treats every export row as a unique "document" requiring its own template resolution engine instance.
  • Impact: CPU-bound bottleneck. For 1,000 rows, it performs 1,000+ reflection-heavy resolutions, even if the patterns are static or simple property access.

D. "God Class" Complexity​

Location: OrderAdvancedSearchQueryBuilder.cs (5,763 LOC)

  • Metrics:
    • ~30 distinct filter categories (Global, Accounting, School, Travel, etc.).
    • ~1,227 LOC in the input DTO (SearchOrderOMGTDto).
    • Combinatorial Explosion: Users can combine any subset of these 30 categories, leading to an effectively infinite number of possible query plans.
  • Issue: Excessive chaining of conditional LINQ filters in a single class.
  • Impact: Difficult to maintain, test, or optimize. The SQL logic for one category (e.g., "Flights") often implicitly depends on joins added by another category (e.g., "Invoice"), creating fragile dependencies.

3. Mitigation Expectations​

Problem AreaMitigation StrategyExpected Result
Search (Postcodes)Convert foreach loop into a combined LINQ predicate (e.g., .Where(c => ranges.Any(...))).Reduction from N queries to 1 single query. Restoration of DB-level paging.
Search (Joins)Audit and remove Join calls for fields already in the View.Cleaner SQL, reduced join depth, faster execution plan generation.
Export (Latency)Data Prefetching: Parse template to identify and batch-load relationships (eliminated N+1).Elimination of thousands of DB roundtrips. CPU latency becomes negligible once I/O is fixed.
Export (Legacy)Maintain EITTextTemplate engine but feed it pre-hydrated in-memory entities.100% backward compatibility with custom patterns while achieving performance gains.

4. Risks & Constraints​

  • Functional Parity: Any change to Distinct() logic must be verified to ensure no duplicated rows appear in the search results.
  • Template Compatibility: Legacy custom export patterns rely on the existing resolver chain. A "Fast Path" must safely fallback to the template engine for unrecognized patterns.