Missing Key index on table UFRecordDataString

[dvandevliet]

Affected versions <= 8.10.1

In 8.7.0 migrations indexes on Key column were added on most RecordData tables except UFRecordDataString.

As of what is currently latest release Umbraco Forms v8.10.1 this index has not been added yet.

Reproduction

• Install Umbraco Forms 8.10.1

Bug summary

• View indexes on UFRecordDataString and you will not see an index on Key column

Specifics

n.a.

Steps to reproduce

n.a.

Expected result

• Non-clustered Index on UFRecordDataString .Key
• IRecordStorage.GetRecordByUniqueId (...) uses index retrieving data by Key from UFRecordDataString

Actual result

• Missing non-clustered Index on UFRecordDataString .Key
• Incremental CPU load on calling IRecordStorage.GetRecordByUniqueId(...) the more records this table contains.
• Deleting a record using the Umbraco Forms CMS interface takes more time, the more records this table contains.

[dvandevliet]

This will also fix the long delete duration reported by #575
(database describes that has issues has a table UFRecordDataString that contains many records)

[AndyButland]

Interesting... these weren't added in 8.7.0 only as it seemed the index for this table was already there. It was only the other UFRecordData* tables that didn't have them and it needed adding in a separate migration.

Could I ask if you could share the same screenshot as you have above but for the UFRecordDataString table please?

And also let me know if you've found this on a clean install, or on a version you've upgraded from an earlier version? Thanks.

[dvandevliet]

I found this issue on a version that was upgraded from an earlier version.
I have already added the index manually so I can share a screenshot, but that will not help I think.

This case - upgrade from an earlier version

• Database was upgraded on 20/04/2020 from Umbraco v7+UF to Umbraco v8.6.0 with Umbraco Forms 8.4.0
• UF upgrades over time
• now running UF 8.7.6

Other cases - clean install
Based on what you said I compared this agains a few other installs that were based on a clean UF install and I noticed that they indeed all have the index that uses a different naming convention:

They all have in common that they started of as a clean Umbraco 8, so that the table was created as defined by Dto.

The different naming convention caused me to wrongfully assume that we added the index ourselves and I apologize for this.

[AndyButland]

Thanks for getting back on this. OK, so it seems that we may have an issue with the missing index for projects that have been upgraded from V7. The index was likely introduced for 8.0.0, but will only be applied for new installs. The new indexes added in 8.7 will be applied to the tables that previously had never had indexes, but this doesn't apply to the UFRecordDataString as I'd previously expected this to always have one.

I'll see about adding a health-check and a further migration to add this one if it's missing (under either naming convention).

[AndyButland]

Potential missing index now added in a migration, plus an additional health to warn if it's missing. Due in 8.11.0 and 9.3.0.