Translation of enums comparison in filters kills index performance

[alex-kulakov]

Queries like

session.Query.All<SomeType>().Where(e => e.State == States.New)

have following filter in SQL if States has base type smaller than integer, e.g. byte

SELECT [a].[Id], 100 AS [TypeId], [a].[State] FROM [dbo].[SomeType] WHERE (CAST([a].[State]  AS integer) = 1)

Such SQL queries have poor performance even if State is indexed. This happens because C# compiler elevates left and right expressions up to int.

We could improve this case by applying cast to actual underlying type of enum.

Note that the problem is also appears in filtered indexes.

[ondrejtucny]

The same issue happens when a filtered index is created and such field of type enum is used in the filter condition. SQL Server can't process the WHERE clause and the whole data model provisioning fails:

SQL error occured. SQL error details 'Type: Unknown;' Query 'CREATE UNIQUE INDEX [IX_AcsWorkerQueue] ON [CardSec].[CardServiceLevelSetting] ([ValidFrom] ASC, [Id] ASC) INCLUDE ([AsFuture.Id], [TypeId]) WHERE (([AsFuture.Id] IS NOT NULL) AND (CAST([State] AS integer) = 2) AND (CAST([Channel] AS integer) = 0)); CREATE UNIQUE INDEX [IX_AcsCardQueue] ON [CardSec].[CardServiceLevelSetting] ([AsFuture.Id] ASC, [ValidFrom] ASC, [Id] ASC) INCLUDE ([Channel], [TypeId]) WHERE ([AsFuture.Id] IS NOT NULL);' Original message 'Incorrect WHERE clause for filtered index 'IX_AcsWorkerQueue' on table 'CardSec.CardServiceLevelSetting'.'

When the WHERE (([AsFuture.Id] IS NOT NULL) AND (CAST([State] AS integer) = 2) AND (CAST([Channel] AS integer) = 0)) is simplified to WHERE (([AsFuture.Id] IS NOT NULL) AND ([State] = 2) AND ([Channel] = 0)), it works.

In combination with the fact that any custom indexes are deleted by DataObjects, this is a serious problem when the index is truly necessary for the sake of performance.

See also issue #192.

[alex-kulakov]

@ondrejtucny, I made a note that filtered indexes have the same issue, thank you for example of indexes anyway.

I did some changes in a separate branch (6.0-enum-improvements) but they are not ready for PR, some tests became failed.

[alex-kulakov]

@ondrejtucny, I have just merged changes to developing version.