Data filters cause database index scan instead of index seek

[dnimekaf]

ABP Framework version: 4.11.0
User Interface: Angular
Steps to reproduce: Run IRepository <TEntity:IMustHaveTenant >.GetAllList() method

Data filters use sql data parameters to show if the filter is enabled or not. These parameters sometimes cause performance issues on MSSQL Server, forcing it to use index scan insted of index seek. This is a huge problem for the tables with large number of records.

For example, here is a sample query with enabled data filter and its execution plan (the query is generated automatically):

declare @__ef_filter__IsMustHaveTenantFilterEnabled_0 bit = 1, @__ef_filter__CurrentTenantId_1 int = 1
SELECT top 100 * FROM [Prospects] AS [e] WHERE (@__ef_filter__IsMustHaveTenantFilterEnabled_0 = 0) OR ([e].[TenantId] = @__ef_filter__CurrentTenantId_1)

As you can see, it performs index scan for a table. For our database this query takes 1.5 minutes to complete.

And this is the same query except removing of the filter parameter (which is redundant here anyway)
declare @__ef_filter__CurrentTenantId_1 int = 1
SELECT top 100 * FROM [Prospects] AS [e] WHERE ([e].[TenantId] = @__ef_filter__CurrentTenantId_1)

And now it uses index seek. This query completes in < 1s on the same database.

The main issue for the MS SQL server is this one: https://stackoverflow.com/questions/27564852/why-is-sql-server-using-index-scan-instead-of-index-seek-when-where-clause-conta

There is no way to prevent data filter to modify the SQL query. Disabling the filter just sets its parameter value to 0.

Suggestion: to avoid using bit parameters in data filters. Such filter should be applied or not before goes down to SQL.

[maliming]

What is your sql server version?
This is more like its problem.

[dnimekaf]

What is your sql server version?
This is more like its problem.

This problem is present on various versions of mssql, I personally checked on version 15 and on Microsoft Azure (the most recent one I guess). It's probably not a database bug, that's how its optimizer works. Perhaps it is necessary to take into account this feature.

[maliming]

We want to dynamically enable/disable a filter at runtime, I can't find other ways to achieve it.

[maliming]

You can try to use IgnoreQueryFilters to disable EF Core's global filters. https://docs.microsoft.com/en-us/ef/core/querying/filters#disabling-filters

[xyfy]

Can we change the CreateFilterExpression method in AbpDbContext from

        protected virtual Expression<Func<TEntity, bool>> CreateFilterExpression<TEntity>()
            where TEntity : class
        {
            Expression<Func<TEntity, bool>> expression = null;

            if (typeof(ISoftDelete).IsAssignableFrom(typeof(TEntity)))
            {
                expression = e => !IsSoftDeleteFilterEnabled || !EF.Property<bool>(e, "IsDeleted");
            }

            if (typeof(IMultiTenant).IsAssignableFrom(typeof(TEntity)))
            {
                Expression<Func<TEntity, bool>> multiTenantFilter = e => !IsMultiTenantFilterEnabled || EF.Property<Guid>(e, "TenantId") == CurrentTenantId;
                expression = expression == null ? multiTenantFilter : CombineExpressions(expression, multiTenantFilter);
            }

            return expression;
        }

to

        protected virtual Expression<Func<TEntity, bool>> CreateFilterExpression<TEntity>()
            where TEntity : class
        {
            Expression<Func<TEntity, bool>> expression = null;

            if (typeof(ISoftDelete).IsAssignableFrom(typeof(TEntity)))
            {
                if (IsSoftDeleteFilterEnabled)
                {
                    expression = e => !EF.Property<bool>(e, "IsDeleted");
                }
            }

            if (typeof(IMultiTenant).IsAssignableFrom(typeof(TEntity)))
            {
                if (IsMultiTenantFilterEnabled)
                {
                    Expression<Func<TEntity, bool>> multiTenantFilter = e => EF.Property<Guid>(e, "TenantId") == CurrentTenantId;
                    expression = expression == null ? multiTenantFilter : CombineExpressions(expression, multiTenantFilter);
                }
            }

            return expression;
        }

avoid add the IsMultiTenantFilterEnabled filter into sql directly

[xyfy]

I cannot understand, why need to add the condition into expression and generate it to sql ,such as IsSoftDeleteFilterEnabled、IsMultiTenantFilterEnabled，what is the exact reason?

can we only use it in the logic code?

@maliming

[olicooper]

@xyfy This update won't work because it is applied in OnModelCreating which is called once per context creation to configure the entities (not on a per query basis); so you can't use ABP's DataFilter multiple times per request.

The context lifetime could be cached across requests so the results are going to be inconsistent too: https://docs.microsoft.com/en-us/ef/core/dbcontext-configuration/#the-dbcontext-lifetime

[stale]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.