PersonalDevice.DeviceRegistrationId is nvarchar(max) with no index, so every device lookup scans the whole table #7146

Description

Description

PersonalDevice.DeviceRegistrationId is created as nvarchar(max) and has no index. The model property has no [MaxLength] (checked in 17.6.1 and develop: Rock/Model/CRM/PersonalDevice/PersonalDevice.cs), so the column can't be used as an index key.

Rock looks up devices by this column frequently, which looks like the mobile app registration and refresh path. Every lookup scans the clustered index. On our database (about 140,000 PersonalDevice rows) this is our second-largest query by pages read.

The generated SQL (column list trimmed):

SELECT TOP (@p__linq__2) ...
FROM [dbo].[PersonalDevice] AS [Extent1]
WHERE (([Extent1].[DeviceRegistrationId] = @p__linq__0)
       OR (([Extent1].[DeviceRegistrationId] IS NULL) AND (@p__linq__0 IS NULL)))
  AND ([Extent1].[PersonalDeviceTypeValueId] = @p__linq__1)
ORDER BY [Extent1].[Id] ASC

Workaround we're considering

Narrowing the column to nvarchar(450) in our database and adding the index above ourselves. We'd rather not diverge from Rock's schema, because a future migration that alters this column would fail while our index exists. So, a core fix would be preferable.

Actual Behavior

From Query Store on our production database (Azure SQL Hyperscale, 8 vCores), averaged over 11 days:

The execution plan is a Clustered Index Scan on PK_dbo.PersonalDevice, with PersonalDeviceTypeValueId as a residual predicate and a Filter on DeviceRegistrationId. Some compilations run it as a parallel plan, which is costly for a single-row lookup. The plan shows no implicit conversion; the missing index is the only cause.

Expected Behavior

The lookup should be an index seek that reads a handful of pages. For example:

1. Give DeviceRegistrationId a maximum length, such as [MaxLength(450)] or whatever fits the longest push token Rock needs to support. On our data the longest stored value is 163 characters.
2. Add an index:

CREATE NONCLUSTERED INDEX IX_DeviceRegistrationId_PersonalDeviceTypeValueId
    ON dbo.PersonalDevice (DeviceRegistrationId, PersonalDeviceTypeValueId);

Because both filter columns are equality matches and the index carries Id, the ORDER BY Id / TOP is satisfied by the seek as well.

If a length limit isn't acceptable, a persisted hash column with an index would also work, but the query would need to filter on the hash.

Steps to Reproduce

1. On any Rock install, run:

   SELECT TYPE_NAME(system_type_id) AS type_name, max_length
   FROM sys.columns
   WHERE object_id = OBJECT_ID('dbo.PersonalDevice') AND name = 'DeviceRegistrationId';
   -- Returns nvarchar, -1 (nvarchar(max))
   
   EXEC sp_helpindex 'dbo.PersonalDevice';
   -- No index includes DeviceRegistrationId

2. Run the query above with SET STATISTICS IO ON and a real registration ID and device type.
3. See a Clustered Index Scan, with logical reads equal to roughly the size of the table.
4. On a database with many devices and active mobile app use, Query Store shows this query near the top by logical reads.

Issue Confirmation

• Perform a search on the Github Issues to see if your bug is already reported.
• Reproduced the problem on a fresh install or on the demo site.

Rock Version

17.6.1

Client Culture Setting

en-US