CODEWITHSUNDEEP
sql.netsql-serverentity-frameworkentity-framework-6
FrankDrebin893
FrankDrebin893

Reputation: 115

Entity Framework 6 - Query with specific parameters gets extremely slow when running in one application, but not in another

We have the following EntityFramework 6 query, which receives a few parameters, two of them being pagination parameters(page size and page number). On our website the first page loads with the default page number as 1, and the page size as 30. It seems that somehow this particular query is remembered by the SQL server, because on subsequent loads of the query, the page starts timing out just for this query with these specific parameters.

If we change the page number to 2 or the page size to 29 or 500, it runs in milliseconds. We also have lots of other queries that are run using this DbContext, but none of them start running slowly with specific parameters(as far as we're aware).

The query below is migrated from another application that accesses the same DB(trying to have just one application access the DB). That application has never run into that the query for page 1 starts running slowly, so it's only after migrating the query that this particular problem has started occurring. The application without the problem is running EF version 6.0.0, while our failing application is running 6.1.3).

I'm also pretty confident this is something SQL Server side, because our application is load balanced, and it starts happening with the query on all servers - even if we restart the servers they keep having the issue with the query timing out on those specific parameters.

The connection strings have been the same(except one has an initial catalog, the other doesn't):

Data Source=MyServerName;User ID=...;Password=...;Initial Catalog=...;MultipleActiveResultSets=True;App=EntityFramework

Here follows the query

var result = MyEntities.MyTable
        .Join(MyEntities.MyOtherTable, id = id...)
        .Where(f => (request.SearchString == "" || request.SearchString == null || (f.Property.Contains(request.SearchString))))
        .OrderBy(f => f...)
        .Select(f => new MyObject
        {
            ....
        });
        MyResponseObject response = new()
        {
            Data = result.Skip((request.Page - 1) * request.PageSize).Take(request.PageSize).ToList(),
            Count = result.Count()
        };
        return response;

Upvotes: 0

Views: 570

Answers (1)

Steve Py
Steve Py

Reputation: 35063

The culprit will likely be a bad execution plan on the server. You can use an interceptor to prepend an "OPTION(RECOMPILE)" to the query to ensure SQL Server is running a fresh query. An example can be found here: How to add OPTION RECOMPILE into Entity Framework

However, first I would also look at cleaning up that query a little to boost performance. Ideally you should have navigation properties linking MyTable with MyOtherTable to remove the need to explicitly join the two. Join should only be needed in very rare cases where you need to relate two loosely related entities.

Next, extract the Where conditions so the conditional logic is done in code rather than in the LINQ:

var query = MyEntities.MyTable.AsQueryable();
if (!string.IsNullOrEmpty(request.SearchString))
    query = query.Where(f => f.Property.Contains(request.SearchString));

Provided you have navigation properties, leverage them within your Linq. You do not need to explicitly join tables, nor eager load relationships with Include. If you don't have navigation properties available I would highly recommend the investment to add them, worst case you will need to use the explicit Joins if that cannot be factored into the development.

I would highly recommend discussing options with your client/team to standardize on a preference for StartsWith type string matching and providing an option for Contains type string matching. Typically the majority of string searches would be satisfied with a Term% type search rather than %Term%. It's important to give them that option, but %Term% searches are considerably more expensive so it's advisable to default to the faster search if it satisfies a significant majority of searches then give the option for the more thorough search. For instance allowing users to do a %Term% search by prefixing the term with a * or %:

var query = MyEntities.MyTable.AsQueryable();

if (!string.IsNullOrEmpty(request.SearchString))
{
    bool isContainsSearch = request.SearchString.StartsWith("%") || request.SearchString.StartsWith("*");
    string scrubbedSearchString isContainsSearch ? request.SearchString.Substring(1) : request.SearchString;

    query = isContainsSearch 
        ? query.Where(f => f.Property.Contains(request.SearchString.Substring(1)))
        : query.Where(f => f.Property.StartsWith(request.SearchString));
}

That is just an example, alternatively give the user a checkbox or other selection to explicitly opt for a contains-like search to avoid possible issues with search terms containing question marks or stars.

Next I would leave the OrderBy and Select until after the count:

var rowCount = query.Count();

MyResponseObject response = new()
{
    Data = result
        .Select(f => new MyResponseObject
        {
            // ...
        }).Skip((request.Page - 1) * Request.PageSize)
        .Take(request.PageSize).ToList(),
    Count = rowCount
};
return response;

This lets EF execute the most efficient queries for the count and projection which may also help it prevent getting hung up on execution plans.

Upvotes: 1

Related Questions