OPENJSON poor performance with CROSS/OUTER APPLY and millions of rows

Question

I have a table with millions of rows and three relevant columns:

CREATE TABLE [EventSourcing].[EventsTableCompressed](
    [DocumentKey] [nvarchar](200) NOT NULL,
    ...
    [JsonCompressed] [varbinary](max) NOT NULL,
    [JsonDecompressed]  AS (CONVERT([nvarchar](max),Decompress([JsonCompressed]))),
 CONSTRAINT [PK_EventsTableCompressed] PRIMARY KEY CLUSTERED 
(
    [DocumentKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

When I perform a search using the cluster index, it goes fast as expected, example:

SELECT 
DocumentKey,
JSON_VALUE(JsonDecompressed, '$.MyJsonProperty') as MyJsonProperty
FROM  [EventSourcing].[EventsTableCompressed] C
where DocumentKey> 'RangeInit' and DocumentKey< 'RangeEnd'

The rows returned are more or less 100 over billions and the IO is minimun (as expected)

However, If I try to complete the query with a CROSS APPLY OPENJSON or OUTER APPLY OPENJSON the performance is not good at all (1 minute to extract 100 rows)

SELECT DocumentKey,  JSON_VALUE(JsonDecompressed, '$.CreditUid') as CreditUid
FROM  [EventSourcing].[EventsTableCompressed] C  
OUTER APPLY OPENJSON (C.JsonDecompressed, '$.MyArrayInsideTheJson') as J
where DocumentKey> 'RangeInit' and DocumentKey< 'RangeEnd'

Inspecting the query plan, I have the same in both cases, with the cluster index seek using most of the time. However, in the Profiler tool, I can see a very big number in read and, surprise, writes in the second query.

I would like to know the reason of this behaviour. It seems that the APPLY is beeing executed over all the rows, not just the rows filtered by the where clausure. Is it that right?

Answer 1

Disabling the stadistic in the database prevents the error. It seems that, the first time, the stats must be updated or something like that (thats the reason of high volume or writes).