Global filters on a large database table caused SELECT query to run really slow

[DamienLaw]

Abp version: 7.4
.NET 6
EF Core 7

Global filters such as Soft Delete, May Have Tenant, Must Have Tenant are causing SELECT query in a large database table (eg: AbpAuditLogs) with more than 5 million records executes very slowly (most of the time, timed out - exceeding 30 seconds).

aspnetboilerplate/src/Abp.EntityFrameworkCore/EntityFrameworkCore/AbpDbContext.cs

Lines 162 to 186 in ae28493

	protected virtual Expression<Func<TEntity, bool>> CreateFilterExpression<TEntity>()
	where TEntity : class
	{
	Expression<Func<TEntity, bool>> expression = null;
	if (typeof(ISoftDelete).IsAssignableFrom(typeof(TEntity)))
	{
	Expression<Func<TEntity, bool>> softDeleteFilter = e => !IsSoftDeleteFilterEnabled || !((ISoftDelete) e).IsDeleted;
	expression = expression == null ? softDeleteFilter : CombineExpressions(expression, softDeleteFilter);
	}
	if (typeof(IMayHaveTenant).IsAssignableFrom(typeof(TEntity)))
	{
	Expression<Func<TEntity, bool>> mayHaveTenantFilter = e => !IsMayHaveTenantFilterEnabled || ((IMayHaveTenant)e).TenantId == CurrentTenantId;
	expression = expression == null ? mayHaveTenantFilter : CombineExpressions(expression, mayHaveTenantFilter);
	}
	if (typeof(IMustHaveTenant).IsAssignableFrom(typeof(TEntity)))
	{
	Expression<Func<TEntity, bool>> mustHaveTenantFilter = e => !IsMustHaveTenantFilterEnabled || ((IMustHaveTenant)e).TenantId == CurrentTenantId;
	expression = expression == null ? mustHaveTenantFilter : CombineExpressions(expression, mustHaveTenantFilter);
	}
	return expression;
	}

In the above piece of codes, !IsSoftDeleteFilterEnabled, !IsMayHaveTenantFilterEnabled and !IsMustHaveTenantFilterEnabled are a part of expressions and are redundant and don't really contribute to the query. The expressions are then used to generate the SQL statements.

The SQL Statement above is being generated and the underlined codes correspond to the 3 boolean variables mentioned above. As you can see, they don't really do anything. They're just constant values that are passed as parameters to the SQL query and compared thus increasing the overhead of the execution.

[DamienLaw]

The 3 boolean variables can be extracted out from the expressions and rewritten as

[ismcagdas]

@DamienLaw thanks for your suggestion. Let me check it.

[DamienLaw]

As it turns out IsMustHaveTenantFilterEnabled checks for the TenantId to determine if the Host is running the query. Currently ABP is putting this boolean in the expression and will conditionally turn on/off the filter within the SQL statement generated which we're trying to avoid. Instead of flipping the switch at the database layer, we'll need to find a way to do it at the application layer.

I'm not sure if turning an individual global filter on/off is currently possible. However there's some discussion on global filters here dotnet/efcore#21459

[ismcagdas]

@DamienLaw as far as I know, it is not possible to change the parameter value sof query filters on runtime. This is the only workaround we can found. Otherwise, CurrentTenantId will have the same value for all tenants. Maybe you can optimize your tables to make this query faster, is that possible ?

[nikschuiling]

We're also facing this issue since we upgraded aspnetboilerplate and moved from EF to EF Core. Conditional WHERE clauses such as "((@__ef_filter__p_0 = CAST(1 AS bit)) OR ([a].[IsDeleted] = CAST(0 AS bit))) AND ((@__ef_filter__p_1 = CAST(1 AS bit)) OR ([a].[TenantId] = @__ef_filter__CurrentTenantId_2)))" are causing high CPU usage on our SQL servers for even simple queries and small tables (say 10k records max).

We ended up using IgnoreQueryFilters() in several queries and added explicit filters on TenantId and IsDeleted where required. This resolved the issue, but we'd love to see a better solution.