OPENJSON is slow to parse 3M rows from a table

Question

I have a table with 3M JSON rows.

I am trying to parse the JSON and insert data into another table. But OPENJSON takes like 50 seconds to Parse the 500K rows.

Query I am using

select   
    top 500000
    qse.*
from 
    dbo.QueryStore qs
    cross apply openjson(qs.Query) 
    with
        (

            Col1 nvarchar(max), Col2 nvarchar(max), Col3 nvarchar(max), Col4 nvarchar(max), Col5 nvarchar(max), Col6 nvarchar(max), Col7 nvarchar(max), Col8 nvarchar(max), Col9 nvarchar(max), Col10 nvarchar(max), Col11 nvarchar(max), Col12 nvarchar(max), Col13 nvarchar(max), Col14 nvarchar(max), Col15 nvarchar(max), Col16 nvarchar(max), Col17 nvarchar(max), Col18 nvarchar(max), Col19 nvarchar(max), Col20 nvarchar(max), Col21 nvarchar(max), Col22 nvarchar(max), Col23 nvarchar(max), Col24 nvarchar(max), Col25 nvarchar(max), Col26 nvarchar(max), Col27 nvarchar(max), Col28 nvarchar(max), Col29 nvarchar(max), Col30 nvarchar(max), Col31 nvarchar(max), Col32 nvarchar(max), Col33 nvarchar(max), Col34 nvarchar(max), Col35 nvarchar(max), Col36 nvarchar(max)
        )qse

How can I improve the speed of OPENJSON to parse 3M rows quickly?

Your help and suggestion will be appreciated.

Answer 1

Do all of your columns contain more JSON? Because the string columns which do not contain more JSON should be (at maximum length) NVARCHAR(4000). If the field contains a number then it should be give a numeric SQL data type (as appropriate) and not NVARCHAR(MAX). That should reduce the allocations and speed it up.