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

Choosing Between Merge Join and Hash Join

Erik Darling gives us a Sophie’s Choice: It could have chosen a Hash Join, but then the order of the Id column from the Posts table wouldn’t have been preserved on the other side. Merge Joins are order preserving, Hash Joins aren’t. If we use a Hash Join, we’re looking at ordering the results of […]

Read More

Creating Temp Staging Tables to Avoid Spooling

Bert Wagner shows how you can create your own tables in tempdb to avoid eager or lazy spools: SQL Server Spool operators are a mixed bag. On one hand, they can negatively impact performance when writing data to disk in tempdb. On the other hand, they allow filtered and transformed result sets to be temporarily […]

Read More

Categories