Performance and Optimization

Levers

  • Incremental scope (changed versions only).

  • Batch sizing (row chunks for large loads).

  • Scheduling frequency segmentation by class volatility.

  • Index tuning on high‑filter columns in analytic views (not on raw class tables unless necessary).

Class Table Design

Class tables are deliberately semi‑denormalised; extra joins avoided to speed report authoring. Avoid over‑indexing—monitor actual query workload first.

Index Strategy (Guideline)

Add nonclustered indexes only when ALL are true: 1. Repeated predicate / join on column. 2. Selectivity meaningfully reduces row scans. 3. Measured workload shows material IO/latency.

Example Target Columns - ObjID (if heavy point lookups outside PK context) - LastModified (temporal slicing) - Key business property frequently filtered (e.g., Status)

Sample Index .. code:: sql

CREATE NONCLUSTERED INDEX IX_MFInvoice_Status ON MFInvoice(Status);

Monitoring Update Runtime

Track ProcessBatch durations for trends. .. code:: sql

SELECT TOP 100 ProcessBatch_ID, CreatedOnUTC, DATEDIFF(second, CreatedOnUTC, CompletedOnUTC) AS DurationSec, Status FROM dbo.MFProcessBatch WHERE ProcessType = ‘ClassTableUpdate’ ORDER BY ProcessBatch_ID DESC;

Outlier Detection

WITH d AS (
  SELECT ProcessBatch_ID,
         DATEDIFF(second, CreatedOnUTC, CompletedOnUTC) AS DurationSec
  FROM dbo.MFProcessBatch
  WHERE ProcessType = 'ClassTableUpdate'
)
SELECT *
FROM d
WHERE DurationSec > (SELECT AVG(DurationSec) + 2*STDEV(DurationSec) FROM d);

Large Table Techniques

  • Partition staging (create temp table segments then merge) if native procedures insufficient.

  • Off‑peak full validations.

  • Avoid concurrent full + incremental on same class.

Multi‑Lookup Explosion Cost

Use CROSS APPLY only in analytic views (not in routine update procedures). Pre‑aggregate exploded datasets if repeatedly consumed.

Event Log Volume Control

Purge or archive raw XML older than retention period if MFEventLog_OpenXML growth impacts storage.

Troubleshooting Hotspots

Symptom: Slow incremental - Check change volume spike (unusual high edit burst?). - Validate network/API latency. - Inspect ProcessBatchDetail for repeating error/rescan patterns.

Symptom: Report timeout - Review executed SQL (extended events or query store) for missing index hints. - Consider pre‑aggregation view or materialized table (ETL pattern) for complex multi‑join sets.

Next

Troubleshooting queries & diagnostics: Troubleshooting and Sample Queries