timeout

Fix Your EF Core Query Timeouts Using This Simple SQL Trick

by Posted on

If you ever had a problem with your query timing out due to filtering by too many ids, you’ve come to the right place. You can solve it by using openjson filtering in your EF Core query.

This article presents an alternate way for standard filtering with Where LINQ extension. The result is query has similar performance (perhaps slightly better). But the most important, it doesn’t time out due too many filter parameters.

Why the query times out with a large number of filter parameters?

T-SQL query (generated by EF) can be very fast when you use it with a small number of parameters, but then timeout when you use it with larger parameters set.

Why?

The problem is Parameter Sniffing.

When the T-SQL query runs for the first time on SQL Server, it looks at passed parameter values and creates an execution plan based on these parameters. Then it stores the plan in the query plan cache. The query plan can be used for the same query but with different parameters set (the larger one, in our case). This mechanism is used in SQL Server to save some time for compiling & recompiling queries.

The problem arises when the first time the query is executed, the set of parameters generates an acceptable plan for that set of parameters but very bad for other more common sets of parameters.

What is openjson

In T-SQL there’s a mechanism called openjson.

OpenJson is a table-valued function that parses JSON text and returns objects and properties from the JSON input as rows and columns. It provides a rowset view over a JSON document.

OpenJson returns a set of rows. Due to this fact, you can use it in FROM clause of Transact SQL statement.

Note that OpenJson function is only available for databases that have compatibility level 130 or higher.

Example of a query used to filter collection using openjson


    DECLARE @employeeIds NVARCHAR(4000) = N'[1,2,3,4]'

    SELECT * FROM employee
    INNER JOIN OPENJSON(@employeeIds) selectedEmployee
        ON employee.Id = selectedEmployee.value

This example provides a list of identifiers as a JSON array of numbers. The query converts the JSON array to a table of identifiers and filters all employees with ID specified within the list.

This query is an equivalent to

    SELECT * FROM employee
    WHERE employee.Id IN (1,2,3,4)

The difference is that in the second query the parameters are embedded within the query.

This is an important difference which we’ll use later on in EF Core

Implementation of openjson in EF Core

In our example let’s assume that the collection will be filtered by ids. In the sample codes, I used EF Core 3.1 .

First, we’ll create FilterItem class which will be used in EF Context and in the query. This is a keyless entity type in terms of EF Core. The purpose is to serve as a return type for raw SQL queries.

    public class FilterItem
    {
        public int Value { get; set; }
    }

Second, we have to create a property for openjson filtering in Context.

    public virtual IQueryable<FilterItem> OpenJsonFilter(string ids) =>
        Set<FilterItem>().FromSqlInterpolated($"select value from openjson( {ids} )");

In the model builder, we have to specify that FilterItem has no key. This helps EF Core interpreter to use this property correctly.

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);

        modelBuilder.Entity<FilterItem>().HasNoKey();
    }

That is all the setup that we need.

Usage of OpenJsonFilter in query

OpenJson filtering is slightly different than regular filtering. As shown in T-SQL example, the filtering will not be just using Where() extension. We’ll have to use Join() instead.

For the purpose of the example, let’s say we have to find all orders for selected customers.

    public class OrderQuery
    {
        public IEnumerable<int> CustomerIds { get; set; }
        // Other properties for querying against customers
    }

    public class Order
    {
        public int CustomerId { get; set; }
        public int Id { get; set; }
        public IEnumerable<int> ItemIds { get; set; }
        // other order properties
    }

    public class OrderProvider
    {
        private Context _context; // injected via ctor

        public Order GetByCustomerIds(OrderQuery query)
        {
            var ids = JsonSerializer.Serialize(query.CustomerIds);

            var result = _context.Orders
                .Join(
                    _context.OpenJsonFilter(ids),
                    order => order.CustomerId,
                    jsonFilter => jsonFilter.Value,
                    (order, jsonFilter) => order
                );

            return result;
        }
    }

So in this query, we’ll get all Orders matching given customer Ids. Customer Ids are passed in the OrderQuery as a parameter of GetByCustomerIds method. Given Ids are serialized to string, as JSON array, and passed into OpenJsonFilter.

Then, the ids are used to filter out the expected rowset by inner join.

This operation tells EF Core to use given ids as local variables in the query, instead of inserting variables directly into it. Which solves the problem of parameters sniffing and the query gets compiled with every execution, instead of being read from the cache.

Summary

This technique is great when filtering by a large array of ids is required and the query is timing out from time to time, due to this fact.

The problem that it solves is that the cached execution plan might be optimal for a small amount of parameters, but not for the same query with a larger set of parameters. This is called parameters sniffing.

This solution handles it by forcing EF to generate a query that uses local variables. This triggers SQL server to compile the query plan, instead of reading it from the cache.

Published by Lukasz