Simple count timing out

[Menighin]

I have made kind of an API for my app.
Basically when the user access some page I run two basic querys:

• getAll that returns the data paginated and filtered
• count that returns the total of records with the given filter.

In the backend I simply have the following structure:

public IQueryable<DbIntegrationLog> Filter(IntegrationLogFilter filter)
{
    var queryable = _context.DbIntegrationLog
    .AsNoTracking()
    .AsQueryable();

    if (filter.Timestamp != null)
        queryable= queryable.Where(o => o.Timestamp >=  filter.Timestamp)

    if (filter.Xml != null)
        queryable = queryable.Where(o => o.Xml != null && EF.Functions.Like(o.Xml, $"%{filter.Xml}%"));

    // Apply another filters accordingly with Where clauses...

    return queryable;
}

public IEnumerable<DbIntegrationLog> GetAll(int page, IntegrationLogFilter filter)
{
    var query = Filter(filter);
    // Page it
    query = query.Skip((page - 1) * 30).Take(30);
    return query.ToList();
}

public int Count(ILuwakPageFilter filter)
{
    var queryable = Filter(filter);
    return queryable.Count();
}

My logs table is a huge table with a lot of records (1kk+ and growing).
The GetAll method executes fine but the Count gives me a timeout.
Analyzing the query EF generates, it is simple:

SELECT COUNT(*)
FROM [DbIntegrationLog] AS [o]
WHERE ([o].[Timestamp] >= '2019-08-26 13:15:00.4620960') AND ([o].[Xml] IS NOT NULL AND [o].[Xml] LIKE '%log%')

When I execute it on database directly, it runs fast enough. Like 1 or 2 secods.
When EF executes it, it takes like 20 seconds and throws a timeout exception.

Playing around, I can see the LIKE part of the query seems to be what drags down the performance. But the only query I see being generated in the output is the one above, which runs fast in SQL Server Management Studio. So, is EF maybe executing a select under the hood or something like that? Any ideas how to make this work?

Im using EFCore 2.2.

Thanks

[joakimriedel]

Just wanted to add in that I'm seeing the same kind of slowdowns, also in 3.0. Analyzing the queries involved it seems for my case that the SQL Server was doing a pretty bad guesswork, perhaps due to queries from EF Core running through sp_executesql. Updating statistics using sp_updatestats helped the query planner a lot. That said, a like %x% query will always be hard to guess for the server.

[divega]

@jcemoller what query exactly did you execute directly against the database? LIKE with %... cannot take advantage of any index, but LIKE x... can.

[joakimriedel]

@divega yeah, I don't use like % in any of my queries I just wrote that as a general advice. I'm still experiencing similar issues with severe performance issues migrating to 3.0. Complex queries that used to take <1 sec is now over 1 minute. Investigating and opening up a separate issue, did not mean to take over @Menighin

[divega]

Ah, I guess my intent was to ask @Menighin: what query exactly did you execute directly against the database? LIKE with a pattern that starts with a wildcard cannot take advantage of any index, but LIKE with a pattern that starts with a character can.

[Menighin]

@divega I executed exactly the same SELECT COUNT(*) ... (it's up there) EF Core has generated for me...
When I run it on SQL Server Management Studio it runs fast. When EF Core runs it, it times out.
Any ideas?

[AndriySvyryd]

@Menighin Did you try the workaround @jcemoller suggested?

sp_updatestats

[joakimriedel]

@Menighin jumping in here again since I had a similar issue.

What you probably are experiencing is problems with parameter sniffing (google this). When you run the query directly in SQL Server Management Studio it will recompile the execution plan. When you run through EF Core, the SQL Server will try to pick a previously cached plan based on the parameter values. If it chooses a bad plan (using wrong estimations for row count etc) it can cause bad performance. Running sp_updatestats might help as @AndriySvyryd pointed out. If not, you could try WITH RECOMPILE to ensure a new plan is always generated.

[joakimriedel]

@AndriySvyryd when #6717 gets implemented I guess it would be quite easy to add option(recompile) to queries 😉

[smitpatel]

@Menighin - I am closing this issue because there is nothing specific EF Core does under the hood (apart from sending sql as sp_executesql). Let us know if updating statistics works for you.

[Menighin]

Guys sorry for the delay repling it.
I tried using sp_updates and WITH RECOMPILE but unfortunelly my database user does not have permission to execute such commands.

I have then tried running the query with OPTION (RECOMPILE) and I'm still facing the same issue.
I have tried doing like:

           var sqlCount = queryable.Select(o => o.Id).ToCountSql();
            sqlCount += " OPTION (RECOMPILE)";

            int res;
            using (var connection = _context.Database.GetDbConnection())
            {
                connection.Open();

                using (var command = connection.CreateCommand())
                {
                    command.CommandText = sqlCount;
                    res = Convert.ToInt32(command.ExecuteScalar().ToString());
                }
            }

Any other ides or should I try get the permission to run those guys?