Sample Header Ad - 728x90

OPENJSON is evaluated as many times as filtered row count

-1 votes
0 answers
79 views
I have some query generated by EF Core 9. EF uses OPENJSON function on MS SQL SERVER where you use Contains in EF Core query. With generated query where OPENJSON is used in SUBQUERY The SQL Server evaluates it as many times as joined/filtered rows. That is very bad. A query from the example finishes in almost **4 seconds**. When I replace it with WHERE IN then it finishes in **~50 ms**. Unfortunately sometimes that OPENJSON contains a lot of items so I can not replace that permanently. Is there any solution to tell SQL Server to evaluate this OPENJSON only once? -- or -- Is it a BUG in MS SQL Server 2019? enter image description here
Asked by dariol (99 rep)
Jul 14, 2025, 12:17 PM