Last post Nov 05, 2008 10:41 AM by nbethmann
Oct 14, 2008 06:51 PM|nbethmann|LINK
I've been working on getting JSON as easily as possible from SQL Server without having to mess around with OR mappings. I don't need OR mappings on the server so why bother with them. I decided that getting XML straight from the database would be the easiest
way to do it. I wanted to see what the network/bandwidth costs would be for XML vs a straight query over TCP/IP. The results were rather interesting.
My sample query selected 180 rows using 3 different query methods. I used Network Monitor 3.2 to gather the results.
I fully intend to have nested results if I use xml so I'll have to do a comparison of that next and also look at performance on the server, but I thought I would post my first set of results as the results were interesting.
BTW, I have written a simple XML to JSON converter that works with the Linq XElement class and I happy enough with that to continue with the experiment.
Oct 20, 2008 10:32 AM|nbethmann|LINK
I've completed my nested results. This query has 600 subrows spread out among the 180 rows for about 780 total rows.
Here are my test results:
It looks like XML can indeed work. Size is excellent and format is excellent.
Nov 05, 2008 10:41 AM|nbethmann|LINK
Further tests are showing that compressed xml works quite well and provides decreased network bandwidth and decreased CPU load versus a LINQ query when the target is nested data. If you are storing the data in a cache, then having compressed data is even
more beneficial. Furthermore, the savings in coding time is huge. If you have no need for objects on the server and can simple pass the results straight from the database server to the client with just a conversion to JSON required then this is the way to
go. For my purposes, I just modify a view and then immediately see the changes on the client page with no code changes required. This is great for rapid development of web pages with client side rendering.
It would be great if Sql Server 11 would implement "For JSON" and compression natively, but until they do, I'll use "For XML" and the CLR compression routines.