Using WITH With OPENJSON

Jovan Popovic points out the performance difference in using the WITH clause in an OPENJSON query:

 

Here are results of the queries:

SQL Server Execution Times: CPU time = 656 ms, elapsed time = 651 ms.
SQL Server Execution Times: CPU time = 204 ms, elapsed time = 197 ms.

As you can see, WITH clause specify that OPENJSON should immediately return properties from the JSON array without second parsing. Performance of the queries might be increased 3 times if you avoid double parsing.

That’s a pretty big difference when you specify the relevant data model elements.

Related Posts

Logical Equivalence and Query Tuning

Erik Darling didn’t warn us that there would be math: Often when query tuning, I’ll try a change that I think makes sense, only to have it backfire. It’s not that the query got slower, it’s that the results that came back were wrong different. Now, this can totally happen because of a bug in previously used […]

Read More

SQL Server Execution Plan Operators

Bert Wagner takes us through some of the more common execution plan operators: Spools come in a variety of types, but most of them can be summarized as operators that store an intermediary result table in tempdb. SQL Server often uses spools to process complex queries, transforming the data into a tempdb worktable to allow […]

Read More

Categories