Territory management data partitioning causing slow performance

CRM SAP Customer Experience (SAP CX)
, , , , , , , ,
1

We’re experiencing significant performance degradation in our territory management module since migrating to SAP CX 2105. Our account hierarchies span multiple regions (EMEA, APAC, Americas) with over 250K accounts distributed across 180+ territories.

The main issues:

  • Territory assignment queries timing out after 45+ seconds
  • Multi-region data access creating cross-partition joins
  • Query execution plans showing full table scans despite proper indexing

Current partition strategy uses territory_id as the partition key, but queries filtering by region or account hierarchy are bypassing partition pruning. We’ve analyzed the execution plans and see consistent performance issues when sales reps access accounts across their assigned territories.

Has anyone successfully implemented territory table partitioning for large-scale deployments? Specifically looking for guidance on partition key selection and optimizing multi-region access patterns.

2

I’ve seen this exact scenario. The territory_id partition key is likely your bottleneck. Most territory queries filter by region or account ownership first, then drill into specific territories. Your current setup forces the optimizer to scan all partitions for region-based queries. Consider a composite partition strategy using region_code as the primary partition key, then sub-partition by territory_id. This aligns with your actual query patterns and enables partition pruning for regional access.

3

For online repartitioning, create a new partitioned table with your target structure, then use SAP CX’s built-in data migration utilities to copy records in batches during off-peak hours. The territory management module supports dual-table operation during migration. You’ll need to:

  1. Create shadow table with region-based partitioning
  2. Set up bidirectional sync triggers
  3. Migrate historical data in chunks (we did 50K records per batch)
  4. Switch read traffic gradually using connection pool routing
  5. Final cutover during maintenance window

We completed this for 400K territories with only 15 minutes of read-only mode required.

4

We had similar timeout issues with our territory hierarchies. Beyond partitioning, check if you’re using the territory hierarchy cache properly. SAP CX 2105 introduced changes to how territory trees are cached in memory. If your queries are rebuilding hierarchy paths on every access, that explains the 45-second delays. The cache configuration is in the territory management admin console under Performance Settings. We reduced our query times from 40s to under 3s just by enabling aggressive hierarchy caching.

5

After reviewing your symptoms and the discussion, here’s a comprehensive solution addressing all three core issues:

Territory Table Partitioning Strategy: Your current territory_id partition key doesn’t align with access patterns. Implement composite partitioning:

  • Primary partition: region_code (EMEA/APAC/AMERICAS) - enables immediate partition pruning for 80% of queries
  • Sub-partition: territory_id ranges (groups of 100 territories) - maintains granular access control
  • Hash sub-sub-partition on account_id for account-specific lookups

Partition configuration:

CREATE TABLE territory_assignments (
  region_code VARCHAR(10),
  territory_id NUMBER,
  account_id NUMBER
) PARTITION BY LIST (region_code)
  SUBPARTITION BY RANGE (territory_id)

Multi-Region Data Access Optimization: The cross-partition joins are killing performance. Implement materialized views for common multi-region queries:

  • Pre-compute territory overlaps for accounts with multi-region presence
  • Create region-specific summary tables refreshed every 4 hours
  • Use SAP CX’s territory assignment cache (increase from default 5min to 60min TTL)
  • Enable query result caching for territory hierarchy traversals

For sales reps accessing multiple territories, implement application-level query splitting - break single multi-region queries into parallel region-specific queries, then merge results. This leverages partition pruning fully.

Query Plan Analysis and Remediation: Your execution plans show full table scans despite indexes because:

  1. Cost-based optimizer chooses scans when accessing >15% of table (common with territory queries)
  2. Multi-column predicates on region + hierarchy prevent index usage
  3. Parallel query degree set too low (default=4) for your data volume

Implement these query optimizer hints:

  • Force partition-wise joins: /*+ PQ_DISTRIBUTE(t HASH HASH) */
  • Set parallel degree to 16 for territory queries
  • Create function-based indexes on frequently filtered hierarchy paths
  • Update table statistics weekly (currently detecting stale stats from 2 months ago)

Migration Path: For zero-downtime repartitioning:

  1. Create new partitioned structure during business hours
  2. Use Oracle GoldenGate or SAP’s replication tools for continuous sync
  3. Migrate 50K territory records per hour during off-peak (2-6 AM)
  4. Run parallel validation queries comparing old vs new table results
  5. Switch connection pool routing 10% at a time over 3 days
  6. Final cutover in 15-minute maintenance window

Validation: After implementation, your metrics should show:

  • Territory assignment queries: <2 seconds (from 45s)
  • Partition pruning rate: >85% of queries
  • Cross-partition joins: reduced by 70% via materialized views
  • Concurrent user capacity: 200+ simultaneous territory queries

This approach has worked for three deployments I’ve managed with 200K-500K territory assignments across multiple regions.

6

One more critical point about query plan analysis - make sure you’re looking at the actual execution plans under load, not just EXPLAIN output. SAP CX 2105 has dynamic query optimization that behaves differently under concurrent load. We discovered our partition pruning was working fine in isolation but failing when 50+ users hit the system simultaneously. The optimizer was switching to nested loops instead of hash joins, causing the full table scans you’re seeing. Check your parallel query settings and consider disabling dynamic plan switching for territory queries.

7

Thanks for the suggestions. I checked our hierarchy cache settings and they were at default (5-minute TTL). I’ve increased it to 30 minutes which helped slightly, but the core partition pruning issue remains. The composite partition approach sounds promising - are there any specific considerations for maintaining data consistency when repartitioning an active territory table? We can’t afford extended downtime for this change.