And that got me thinking, what if I could reduce the number of reads? The simplest way to do so would be to reduce the size of data by changing the types. Both in terms of CPU and the logical reads.
The above were rather unexpected results. Native INT and VARCHAR columns required just over 200 logical reads while both JSON and schema-less XML took around 12,000 logical reads, while the XML with schema required over 34,000. It is also worth looking at the logical reads. the INT column was still faster at 0.03 seconds. So what happened to the 10 times faster claim? Well, the native VARCHAR column performed 10 times faster at 0.04 seconds than the JSON one. Better (or worse) still when compared to an XML with schema which took 40.47 seconds the JSON type seems to be 94 times faster. That can’t be right!? Can it? JSON query took on average of 0.43 seconds which is 36 times faster than the 16.79 seconds it took to sum the values out of the XML documents. There are no indexes or computed columns to help with the performance of the queries as the objective is to simply measure performance of parsing and document querying. To put things in perspective I will compare performance on the document types to aggregate on an int and an varchar type columns.Ĭonvert(int, json_value(, '$.ValueA')) The test consists of simply calculating a sum of a specific property from the document across all the rows in the table. The tables are populated with 100,000 random records. I have tables created where there are only two columns, an integer primary key Id and a Data column of the test type. value and json_value for XML and JSON respectively. It would be difficult to compare performance of every single usecase so I have decided to focus on comparing single value retrieval from a document using. The data is stored as plain text, something that was possible before of course, but there is for json clause and 4 methods: isjson, json_modify, json_query and json_value.Īn example of retrieving value from a JSON document can look something like this:ĭeclare nvarchar(max) = N'' That’s right, JSON support but not a JSON type. JSON support was introduced in SQL Server 2016. Let’s see an example of retrieving a value of a node from an XML variable: It came with for xml clause and three main methods: modify, query and value defined on the XML type. It is possible to define a column as an XML type with, or without schema and then query the non-relational data together with relational data. It was implemented as a new built-in data type.
XML Data Type has been supported by SQL Server since version 2005. I used to do a lot of XML in SQL Server and now I use JSON too so I decided to check how they compare when it comes to performance in SQL Server (2017). Have you heard claims that in SQL Server the new JSON data support performs 10 times better than the old XML storage? And all that despite using nvarchar type for storage instead of the specialised xml type.
0 kommentar(er)
