Making the collateral list 89× faster

The list shows 20 rows at a time out of about 3,500 assets, yet every page took 18–20 seconds, deep pages were just as slow, and Export took 35 seconds — users waited on almost every search or page change.

• Row numbers and totals came from ROW_NUMBER() / COUNT() OVER(), which forced the database to process every filtered row (3,500+) before cutting the result down to 20.
• So the "latest technician status" lookup (OUTER APPLY … TOP 1) ran 3,500 times instead of 20 — against a table with no usable index.
• The paging step joined all 14 tables even though the filters used only a few. One was a cross-database join that needs a collation conversion, so it can't use an index; past page 1 the optimizer switched to a nested loop that re-read that table for every row.

• Page first on narrow columns (id + row number + total) with OFFSET / FETCH, then join back for the full columns and technician status — only for that page's 20 rows.
• In the paging step, join only the tables that change the row count plus the ones the filters actually reference.
• Force a hash join on the cross-database data so it is read once instead of once per row.
• Turn the latest-status lookup into a derived table with ROW_NUMBER, so the table is read once — for the page and for Export.

• Checked before changing anything that the joins don't multiply rows: COUNT(*) = COUNT(DISTINCT id) = 3,503.
• Compared the new query with the old one row by row across 9 filter combinations: identical, zero differences.
• Edge cases (an invalid page range) still return what they did before — 0 rows, not every row.
• Wrote down why the collation and the hash hint matter in a GOTCHAS doc, so the next person doesn't remove them by accident.

The "technician updated status" column showed "-" even after technicians had updated it, because the mobile app and the web app wrote the status to different places (measured: 15 assets had a status, only 3 had history). Both paths now write both places, with a guard against duplicate rows when a technician submits the form more than once.

The eviction case page was slow for a different reason: parallel query steps waited on each other when the database server was busy. Limiting those steps to a single thread (MAXDOP 1) took the first tab from 33.8 s to 1.9–2.4 s and its Excel export from 13.5 s to 2–3 s, with identical results.

Every commit in this work was co-authored with Claude Code. The part I own: timing both versions on the same data, checking row by row that the results didn't change, and only then shipping it with a rollback.