# HealthProbe Import Optimization Log

**Canonical path:** `HealthProbe/Doc/04-project/Import-Optimization-Log.md`  
**Created:** 2026-06-02  
**Purpose:** Track import performance work, measured results, regressions, and next experiments.

This is a living project log. Update it after each import optimization commit and after each real-device import report.

## Scope

The current optimization target is the initial / full-history HealthKit import into the SQLite archive.

Primary goals:
- complete large first-run imports without app freeze or watchdog-like stalls;
- keep memory bounded for low-end devices;
- reduce wall-clock duration enough to make future background / scheduled collection realistic;
- keep archive writes idempotent and differential;
- preserve SQLite as the source of truth.

Non-goals for this log:
- redesigning HealthKit background collection strategy;
- changing archive semantics;
- optimizing UI rendering after import, except when post-import work blocks the app.

## Measurement Fields

Use the HealthProbe diagnostic report fields below for comparisons:

| Field | Meaning |
|-------|---------|
| `WallClockDuration` / `Duration` | User-visible total operation time. |
| `SummedFetchElapsed` | Time spent fetching HealthKit samples. Per-metric sums may overlap. |
| `SummedProcessingElapsed` | Time spent converting HealthKit samples into archive rows. |
| `SummedInsertElapsed` | Time spent writing rows to SQLite. Current main bottleneck. |
| `SummedFinalizeElapsed` | Type verification, aggregate rebuild, and finalization cost. |
| Per-type `insertElapsed` | Most useful field for high-volume types such as Heart Rate and Active Energy. |

Important interpretation:
- per-metric timing sums can exceed wall-clock time because type fetches overlap;
- progress rates shown during import may overestimate throughput if overhead is not included;
- compare first snapshots only against first snapshots after a database reset.

## Real-Device Results

### 2026-06-02 Baseline Before Latest Batch/Chunk Work

Source: user-provided diagnostic report.

| Metric | Value |
|--------|-------|
| Wall clock | 20m 25s |
| Summed fetch | 1m 03s |
| Summed processing | 1m 31s |
| Summed insert | 17m 02s |
| Summed finalize | 9.2s |
| Heart Rate count | 923,466 |
| Heart Rate insert | 10m 45s |
| Active Energy insert | 4m 29s |
| Steps insert | 27.1s |
| Walking + Running Distance insert | 21.8s |

Conclusion: SQLite insert dominated the run. HealthKit fetch was not the limiting factor.

### 2026-06-02 After Batched Initial Archive Writes

Source: user-provided diagnostic report after commit `a026566`.

| Metric | Value |
|--------|-------|
| Wall clock | 18m 21s |
| Summed insert | 15m 44s |
| Heart Rate insert | 10m 03s |
| Active Energy insert | 3m 51s |
| Steps insert | 28.1s |
| Walking + Running Distance insert | 25.4s |

Conclusion: batching produced a useful improvement, but insert remained dominant.

### 2026-06-02 After Larger Initial Write Chunks

Source: user-provided diagnostic report after commit `c138b7b`.

| Metric | Value |
|--------|-------|
| Wall clock | 18m 30s |
| Summed metric total | 18m 02s |
| Summed fetch | 46.8s |
| Summed processing | 1m 37s |
| Summed insert | 15m 24s |
| Summed finalize | 10.5s |
| Heart Rate count | 922,404 |
| Heart Rate total | 11m 23s |
| Heart Rate fetch | 21.2s |
| Heart Rate processing | 56.1s |
| Heart Rate insert | 9m 58s |
| Active Energy count | 348,635 |
| Active Energy insert | 3m 48s |
| Steps insert | 24.2s |
| Walking + Running Distance insert | 20.0s |

Conclusion: larger chunks gave only marginal gains. Further optimization should reduce per-sample SQLite work rather than only increasing page/chunk size.

### 2026-06-02 After Direct Inserts For New Archive Samples

Commit: `44d9ebd` (`Use direct inserts for new archive samples`)  
Source: user-provided first-import diagnostic report after database reset.

Note: an earlier user-provided report looked significantly faster, but it was a
reimport rather than a fresh first snapshot and is not used as a direct
comparison against first-import runs.

| Metric | Value |
|--------|-------|
| Wall clock | 17m 13s |
| Summed metric total | 17m 13s |
| Summed fetch | 43.0s |
| Summed processing | 1m 40s |
| Summed insert | 14m 38s |
| Summed finalize | 9.5s |
| Heart Rate count | 922,431 |
| Heart Rate total | 10m 25s |
| Heart Rate fetch | 20.8s |
| Heart Rate processing | 57.0s |
| Heart Rate insert | 8m 59s |
| Active Energy count | 348,669 |
| Active Energy insert | 3m 54s |
| Steps insert | 24.2s |
| Walking + Running Distance insert | 20.7s |

Comparison against the previous comparable first-import run (`c138b7b`):

| Metric | Previous | Current | Change |
|--------|----------|---------|--------|
| Wall clock | 18m 30s | 17m 13s | -1m 17s / -7% |
| Summed insert | 15m 24s | 14m 38s | -46s / -5% |
| Heart Rate insert | 9m 58s | 8m 59s | -59s / -10% |
| Active Energy insert | 3m 48s | 3m 54s | +6s / +3% |
| Steps insert | 24.2s | 24.2s | flat |
| Walking + Running Distance insert | 20.0s | 20.7s | +0.7s / +4% |

Conclusion: direct inserts for brand-new dependent rows produced a valid but
modest first-import gain. The large reimport improvement was not representative
of a clean first snapshot. SQLite insert remains the dominant bottleneck.

### 2026-06-02 Non-Chain-Start Full Scan After Index Removal

Commit context: after `ff59257` (`Drop unused sample import indexes`)
Source: user-provided diagnostic report with `previousSnapshotID` present and
`isChainStart: false`.

This is not a comparable first-import benchmark for the unused-index removal,
but it is important because it shows that non-initial captures can be slower
than first imports when the app performs a full-history scan.

| Metric | Value |
|--------|-------|
| Wall clock | 22m 33s |
| Summed metric total | 22m 14s |
| Summed fetch | 52.0s |
| Summed processing | 2m 23s |
| Summed insert | 18m 44s |
| Summed finalize | 11.5s |
| Heart Rate count | 922,440 |
| Heart Rate total | 13m 30s |
| Heart Rate fetch | 24.3s |
| Heart Rate processing | 1m 29s |
| Heart Rate insert | 11m 25s |
| Active Energy count | 348,698 |
| Active Energy insert | 4m 44s |
| Steps insert | 40.4s |
| Walking + Running Distance insert | 36.0s |

Conclusion: this run should not be used to judge first-import index removal.
However, it indicates a separate bottleneck: subsequent full scans still spend
most of their time in SQLite writes, likely because unchanged samples are still
touching the archive write path. The next implementation target should reduce
per-sample work for unchanged existing samples during verification/full-scan
captures.

### 2026-06-02 First Import After Index Removal And Reset Fortification

Commit context: after `3dd5f48` (`Fortify scheduled test database reset`), with
the unused index removal from `ff59257` included. Source: user-provided
diagnostic report with `previousSnapshotID: none` and `isChainStart: true`.

This is a comparable first-import benchmark. The `a281c51` verified-event change
is included in the build, but it should not materially affect this run because a
clean first import creates brand-new samples rather than unchanged existing
samples.

| Metric | Value |
|--------|-------|
| Wall clock | 12m 43s |
| Summed metric total | 12m 42s |
| Summed fetch | 40.4s |
| Summed processing | 1m 37s |
| Summed insert | 10m 11s |
| Summed finalize | 10.8s |
| Total records | 1,579,168 |
| Heart Rate count | 922,450 |
| Heart Rate total | 8m 06s |
| Heart Rate fetch | 18.6s |
| Heart Rate processing | 56.1s |
| Heart Rate insert | 6m 41s |
| Active Energy count | 348,701 |
| Active Energy insert | 2m 09s |
| Steps insert | 21.6s |
| Walking + Running Distance insert | 19.2s |

Comparison against the previous comparable first-import run (`44d9ebd`):

| Metric | Previous | Current | Change |
|--------|----------|---------|--------|
| Wall clock | 17m 13s | 12m 43s | -4m 30s / -26% |
| Summed insert | 14m 38s | 10m 11s | -4m 27s / -30% |
| Heart Rate insert | 8m 59s | 6m 41s | -2m 18s / -26% |
| Active Energy insert | 3m 54s | 2m 09s | -1m 45s / -45% |
| Steps insert | 24.2s | 21.6s | -2.6s / -11% |
| Walking + Running Distance insert | 20.7s | 19.2s | -1.5s / -7% |

Conclusion: first-import reset is now clean and the unused-index removal produced
a large measurable gain. SQLite insert remains dominant, but the main bottleneck
has moved from about 14m38s to 10m11s.

## Optimization Iterations

| Date | Commit | Change | Result / Status |
|------|--------|--------|-----------------|
| 2026-06-02 | `fd08ded` | Added explicit fetch / processing / insert / finalize timings to reports. | Made phase comparisons possible without inferring from UI progress. |
| 2026-06-02 | `87f1a85` | Cached repeated SQLite write-path lookups within grouped imports. | Reduced repeated id lookup pressure in hot path. |
| 2026-06-02 | `7294a01` | Fast-pathed visibility writes for new archive samples. | Removed redundant visibility close/existence work for brand-new samples. |
| 2026-06-02 | `585d77f` | Tightened archive verification aggregate queries. | Reduced finalization / verification rescans. |
| 2026-06-02 | `2dd279c` | Used rowid fast path for new archive sample rows. | Avoided follow-up id lookup queries when SQLite confirmed new inserts. |
| 2026-06-02 | `f569b6c` | Fixed scheduled test database reset. | Restored ability to compare fresh first-snapshot imports. |
| 2026-06-02 | `986f343` | Increased Heart Rate import write chunks. | Early attempt to reduce paging/write overhead for the largest metric. |
| 2026-06-02 | `c1ebd37` | Sped up archive verification finalization. | Reduced finalize pressure; insert remained dominant. |
| 2026-06-02 | `bcbf9a5` | Cleaned up import diagnostic timings. | Corrected date-fetch wall-clock measurement and report text. |
| 2026-06-02 | `a026566` | Batched initial import archive writes across several fetched pages. | Wall clock improved from about 20m25s to 18m21s on the measured first import. |
| 2026-06-02 | `c138b7b` | Increased initial import write chunk sizes. | Marginal improvement: summed insert from 15m44s to 15m24s on the next comparable run. |
| 2026-06-02 | `44d9ebd` | Used direct inserts for dependent rows when `samples` creates a new sample. | Confirmed modest first-import gain: wall clock 18m30s -> 17m13s, summed insert 15m24s -> 14m38s, Heart Rate insert 9m58s -> 8m59s. |
| 2026-06-02 | `ff59257` | Removed unused `samples` indexes on global UUID hash and semantic fingerprint. | Confirmed large first-import gain after clean reset: wall clock 17m13s -> 12m43s, summed insert 14m38s -> 10m11s, Heart Rate insert 8m59s -> 6m41s. Deleted-object lookup remains covered by `(sample_type_id, sample_uuid_hash)`. |
| 2026-06-02 | pending | Captured non-chain-start full-scan report after index removal. | Not comparable for first-import performance; reveals a separate full-scan/unchanged-sample write bottleneck. |
| 2026-06-02 | `a281c51` | Stopped writing `verified` observation events for unchanged existing samples. | Awaiting comparable non-chain-start/full-scan report. Expected signal is lower `SummedInsertElapsed` and especially lower Heart Rate insert time when most rows are unchanged. |
| 2026-06-02 | `3dd5f48` | Fortified scheduled test database reset with a disk marker and extra SQLite sidecar cleanup. | Real-device report confirmed reset produced `previousSnapshotID: none`, `isChainStart: true`, and a clean first-snapshot timeline. |
| 2026-06-02 | pending | Removed unused `sample_versions(start_date, end_date)` and redundant `sample_visibility_ranges(sample_id, last_observation_id)` indexes. | Awaiting comparable first-import report. Expected signal is lower insert time because first import writes one sample version and one visibility range per record. |

## Current Diagnosis

The import is no longer primarily a HealthKit fetch problem. On the latest comparable first-import measured run:

- total wall clock was 12m43s after the unused-index removal and clean reset;
- summed fetch was only 40.4s;
- summed insert was 10m11s;
- Heart Rate alone spent 6m41s inserting.

The likely bottleneck is per-row SQLite work:
- uniqueness checks on hot tables;
- index maintenance while importing high-volume rows;
- multiple dependent writes per sample;
- commit / transaction shape;
- Core Data or UI refresh work after SQLite completes, if the app remains unresponsive after import.

## Open Issues / Observations

- Very small pages reduced freeze risk but introduced visible overhead.
- Some progress timing displayed in the UI did not include overhead, so elapsed time and rates looked better than the real operation.
- A previous Heart Rate import appeared to stall for long periods around roughly 900k records, but later progress resumed; avoid classifying this as a hard timeout without report evidence.
- After a completed import, the app may remain unresponsive for more than one minute. This needs separate timing around post-import cache rebuild, UI refresh, report generation, and main-thread work.
- Partial / old imported observations can pollute comparisons. Fresh first-snapshot performance comparisons should use a confirmed reset database.
- Non-chain-start full scans can be slower than first imports if unchanged existing samples still write per-sample archive evidence.

## Next Experiments

Prioritize experiments in this order:

1. Add explicit post-import timings if the app is still unresponsive after the operation reports success.
2. Run a non-chain-start/full-scan benchmark after skipping unchanged `verified` events and compare `SummedInsertElapsed`, `Heart Rate insertElapsed`, `Steps insertElapsed`, and `Walking + Running Distance insertElapsed`.
3. Reduce remaining per-sample SQLite writes for unchanged existing samples during non-chain-start full scans, especially open visibility-range existence checks.
4. Profile whether index maintenance dominates first-import insert cost.
5. Consider a guarded bulk-import mode for first observations:
   - keep archive semantics unchanged;
   - only relax work that can be safely reconstructed or validated;
   - re-enable normal idempotent paths for incremental observations.
6. Run a fresh first-import benchmark after the unused-index removal and compare `SummedInsertElapsed`, `Heart Rate insertElapsed`, and `Active Energy insertElapsed`.
7. Investigate whether first-import-only deferred index creation or temporary staging tables can reduce `samples` / `sample_versions` / `sample_observation_events` write cost without weakening final archive integrity.
8. Revisit adaptive page sizes only after SQLite write-path costs are reduced.
9. Revisit background / scheduled collection once initial import can finish reliably and post-import UI recovery is bounded.

## Verification Checklist For Each Optimization

- [ ] `git diff --check` passes.
- [ ] SQLite archive store tests pass.
- [ ] Import configuration tests pass if capture strategy changed.
- [ ] Repeated-page/idempotency behavior remains covered.
- [ ] A real-device report is attached or summarized in this log.
- [ ] The next experiment is recorded before moving on.
