Dataview Attribute Value Filter Causing Index Scans (Terrible Dataview Performance)

[Jon-Corey]

Please go through all the tasks below

• Check this box only after you have successfully completed both the above tasks

Please provide a brief description of the problem. Please do not forget to attach the relevant screenshots from your side.

When using an attribute value filter in a dataview, the SQL that is generated uses an index scan on the Attribute Value table. Prior to v15, the SQL that was generated used an index seek on the Attribute Value table.

This led to dataviews that were taking 4 seconds to run/persist to take 5 minutes to run/persist on a database that has ~26 million attribute values.

In case you are familiar with SQL, here are some real SQL where clauses generated by the same dataview in v14 and in v15 for comparison (with EF nicknames replaced by table names for clarity):

Example v14 Dataview SQL where clause:

WHERE ([AttributeValue].[EntityId] IS NOT NULL) AND ([AttributeValue].[AttributeId] = @p__linq__2) AND ([AttributeValue].[ValueAsDateTime] IS NOT NULL) AND ([AttributeValue].[EntityId] = [Person].[Id])

Example v15 Dataview SQL where clause (notice that Attribute Values with a null EntityId are being compared against Person.Id when there is no reason to do so):

WHERE ([AttributeValue].[AttributeId] = @p__linq__2) AND ([AttributeValue].[ValueAsDateTime] IS NOT NULL) AND ((CASE WHEN ([AttributeValue].[EntityId] IS NULL) THEN 0 ELSE [AttributeValue].[EntityId] END) = [Person].[Id])

Expected Behavior

An index seek should be used instead of an index scan.

Dataviews that were taking 4 seconds to run should not take significantly longer after updating to v15.

Actual Behavior

An index scan was used instead of an index seek.

Dataviews that were taking 4 seconds to run are now taking 5 minutes to run.

Steps to Reproduce

1. Get a v14 database with a large number of attribute values (the more there are the more pronounced the difference is)
2. Create a dataview with a filter on a an attribute value
3. Observe the short length of time the dataview takes to run
4. Get a v15 database with a large number of attribute values (the more there are the more pronounced the difference is)
5. Create a dataview with a filter on an attribute value
6. Observe the enormous length of time the dataview takes to run

Rock Version

15.1

Client Culture Setting

en-US