Problem
In ReconcileWindowsProfiles at https://github.com/fleetdm/fleet/blob/main/server/service/microsoft_mdm.go#L3046, the install loop iterates per profile:
for profUUID, target := range installTargets {
ds.MDMWindowsInsertCommandAndUpsertHostProfilesForHosts(ctx, target.hostUUIDs, command, payloads)
}With 30 profiles, this is 30 separate calls each doing: 1 command INSERT + batched queue INSERT + batched profile upsert. Each call is ~100ms = 2.7s total.
Solution
Consolidate queries.
INSERT INTO windows_mdm_commands — n, where n is number of profiles, rows in one multi-row INSERT (easy)INSERT INTO windows_mdm_command_queue — n (where n is number of profiles) × 2,000 (reconcileWindowsProfilesBatchSize ) = 54K rows across fewer batched transactions grouped by batch size, not by profileUPSERT host_mdm_windows_profiles — same 54K rows in unified batches
The number of db trips would Number of DB round-trips reduced from n, where n is number of profiles, to ~3-5 per cron tick
Found as a part of #44656
Problem
In
ReconcileWindowsProfilesat https://github.com/fleetdm/fleet/blob/main/server/service/microsoft_mdm.go#L3046, the install loop iterates per profile:With 30 profiles, this is 30 separate calls each doing: 1 command INSERT + batched queue INSERT + batched profile upsert. Each call is ~100ms = 2.7s total.
Solution
Consolidate queries.
INSERT INTO windows_mdm_commands— n, where n is number of profiles, rows in one multi-row INSERT (easy)INSERT INTO windows_mdm_command_queue— n (where n is number of profiles) × 2,000 (reconcileWindowsProfilesBatchSize ) = 54K rows across fewer batched transactions grouped by batch size, not by profileUPSERT host_mdm_windows_profiles— same 54K rows in unified batchesThe number of db trips would Number of DB round-trips reduced from n, where n is number of profiles, to ~3-5 per cron tick
Found as a part of #44656