Performance Testing JSON in SQL Server vs C#

A case study in why JSON performance in SQL Server is much better than I originally thought.

Performance testing in SQL Server is hard enough. When you start trying to compare SQL Server functions to code in .NET, lots of of other factors come in to play.

This video examines five JSON performance tests and describes what to consider when performance testing in SQL Server vs other languages.

Elsewhere on the internet:

[yonihbc]

I got to this video with a question in my mind, is it faster to return datatable from SQL to C# and make the JSON in C# in order to save it to file (I need to export if and send it to FTP) or to do FOR JSON PATH/AUTO in SQL and use that string with C#

Do you have an answer for that?

[kadenzxc]

Thanks for putting the time into this. This is valuable information. I don't have much experience doing bench tests on other applications, but I do run tests optimize my own code regularly and I have some questions about things I'd want to check if I was working on something like this.

Do you think you should be running tests that take +10s so that the SQL Server database overhead becomes less significant in the final results? The server has to read your query, calculate a hash on the query string, look up the hash code in a table of cached execution plans, and then execute the plan. I'd think that could take a decent portion of those 71ms. Increasing the number of entries in the de/serialization should make the results more accurate since it increases the ratio of deserialization time to overhead time in the final result.

Also, the results are kinda contaminated with aggregation time on the SQL side no? Adding entries doesn't fix that problem. The query plan shows that aggregation takes up 38% of the time for the whole recording.

Is it possible that SSMS notices that we are not pulling column values, and just parses the JSON string for the number of entries? That'd mean we could be skipping a decent chunk serialization time in the aggregated version of the query. It all depends on the writing inside of OPENJSON, which doesn't display in the query plan results. I understand that it looks better than the one where display results are dumped, but as far as I know, it could be that SSMS already factors that out with a stopwatch (it would make sense to me as a coder), and the only time advantage from the unaggregated query to the aggregated query is because of SQL Server skipping the deserialization work to parse unwanted columns. Aggregation time still gets added to the latter too. Have you tried looking at the client statistics pane or the properties pane for total query execution time?

Have you tried checking the time when reading results into an in-memory table? That would guarantee that all the values are being de/serialized, it would skip the screen update, and it wouldn't trigger an aggregation function.

[lnk]

Please explain: How can compare BANANA with COWS ? SQL is write in c++, C# is interpreted, WTF do you explain here ?