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.
Answer by JohnM ·
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.