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?

Asked by dariol
(99 rep)
Jul 14, 2025, 12:17 PM