Hot tables — large prod tables that break naive queries¶

Some Raklet tables are small on a dev box and huge in production. A LINQ query that is instant locally can time out, or fail to translate, against prod volume. This file is the registry of those tables. Check it before writing or reviewing any EF query.

Full reasoning and the testing process: docs/testing/prod-scale-pr-testing-plan.md.

The rule¶

If a PR adds or changes an EF LINQ query that touches a table below:

Add a real-SQL [TestCategory("Integration")] test that executes the query against SQL Server — not against .AsQueryable() over an array. In-memory LINQ-to-Objects supports every operation and proves nothing about EF6's SQL translation or performance. Pattern to copy: Raklet.UnitTests/OrganisationMembershipCrudIntegrationTests.cs.
Use the DbContext the query actually uses (see table below) — the hot tables are spread across four separate databases.
Name the index the query relies on. If no single index covers all the filtered/grouped columns, add a migration. A non-covering index still key-lookups once per row — fine on a dev org, a timeout on a prod-scale one.
Be especially wary of GroupBy, Distinct, and .Count() on these tables — EF6 either mistranslates them or emits a per-group re-scan.

The four databases¶

The codebase has four EF DbContexts, each on its own database:

DbContext	Connection string	Holds
`RakletDb` (`Models/RakletDb.cs`)	`RakletV3`	orgs, memberships, contacts, events, payments-side refs
`RakletEmailContext` (`Raklet.Email/Database/`)	`RakletEmailContext`	emails, email items, email actions
`RakletMoneyContext` (`Raklet.Money/Database/`)	`RakletMoneyContext`	money-side payment data
`RakletSmsContext` (`Raklet.Sms/Database/`)	`RakletSmsContext`	SMS

Registry¶

Prod sizes measured 2026-05-14 (re-run the queries below to refresh):

Database (DbContext)	Table	Prod rows	Prod reserved	Why large	Common filter/group columns
email (`RakletEmailContext`)	`EmailItems`	17.3M	32 GB	one row per recipient per campaign	`EmailId`, `IsDeleted`
email (`RakletEmailContext`)	`EmailItemActions`	14.9M	8.3 GB	one row per recipient action per campaign	`EmailId`, `Type`, `IsDeleted`, `ClickedUrl`, `EmailItemId`
email (`RakletEmailContext`)	`EmailParts`	2.5M	15 GB	HTML bodies — very wide rows	`EmailId`
email (`RakletEmailContext`)	`Emails`	2.5M	10 GB	every campaign ever sent — wide rows	`OrganisationId`
main (`RakletDb`)	`OrganisationMemberships`	910k	6.4 GB	every contact in every org — very wide rows	`OrganisationId`, `Status`, `Type`
main (`RakletDb`)	`Organisations`	34k	3.9 GB	only 34k rows but ~120 KB/row — per-org settings/CSS/config blobs	`Id`, `Permalink`
main (`RakletDb`)	`Payments`	474k	1.1 GB	every payment ever taken	`OrganisationId`, date ranges
main (`RakletDb`)	`Debts`	1.5M	1.1 GB	per-member debts	`OrganisationId`, `OrganisationMembershipId`
main (`RakletDb`)	`OrganisationMembershipActivities`	1.8M	722 MB	activity log per member	`OrganisationMembershipId`, `OrganisationId`
main (`RakletDb`)	`ApplicationForms`	144k	688 MB	wide form payloads	`OrganisationId`

Notes: - *_2018 tables (EmailItems_2018, EmailItemActions_2018, …) are archive tables — don't query them by accident. - Row count alone hides scale risk. Organisations is only 34k rows but ~120 KB each — a query that .Include()s or wide-selects orgs across the table moves gigabytes. Sort the size query by UsedMB, not by row count. - Row counts here are platform-wide; query performance depends on per-org / per-email fan-out — the worst single org or single email, not the total.

Refreshing this registry¶

Per-table size — metadata-only, instant, safe on prod. Run once per database:

SELECT
    DB_NAME()                                              AS [Database],
    s.name                                                 AS [Schema],
    t.name                                                 AS [Table],
    SUM(CASE WHEN p.index_id IN (0,1) THEN p.row_count ELSE 0 END) AS [Rows],
    CAST(SUM(p.reserved_page_count) * 8.0 / 1024 AS DECIMAL(18,1)) AS [ReservedMB],
    CAST(SUM(p.used_page_count)     * 8.0 / 1024 AS DECIMAL(18,1)) AS [UsedMB]
FROM sys.dm_db_partition_stats p
JOIN sys.tables   t ON t.object_id = p.object_id
JOIN sys.schemas  s ON s.schema_id = t.schema_id
GROUP BY s.name, t.name
ORDER BY [UsedMB] DESC;

Worst-case fan-out per email — this does scan, so run off-peak:

SELECT TOP 20 EmailId, COUNT(*) AS Actions
FROM dbo.EmailItemActions
WHERE EmailId IS NOT NULL
GROUP BY EmailId
ORDER BY COUNT(*) DESC;