question

manassql avatar image
manassql asked

How to deal with large amount of XML data in Sqlserver Database

In a table there are 10 columns and out of that 2 columns has huge amount data. One column(XML datatype) stores XML data another column(NVARCHAR(MAX)) stores JSON data. Each rows size around 1.4 MB. Moreover, when I am using a SELECT command it takes more time(22 seconds) to load only one record.

Select * from tags where campaignid ='EB5C2CDB-C076-5174-61D1-D9EA0E04975A';

So how to improve SELECT query performance or any better way to deal with it.

performancexmlsqlserver2012
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
JohnM avatar image
JohnM answered

Are you searching for data within those two columns? If not, don't return those two columns from the table when reading out of it.

Don't store XML in the table if it is highly select-able and if you are 2016+ (I think) you can use the JSON data type. However, in my experience, both of those two data types are just a brute to try to tune up in SQL Server. For XML data types, you can also look at using XML indexes to try to improve the performance. Note, however, your mileage may vary.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.