none
How to deal with large amount of XML in Sqlserver Database. RRS feed

  • Question

  • In a table we have 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. 

    TABLE - [dbo].[CampaignConfiguration](
    [CampaignId] [uniqueidentifier] NOT NULL,
    [ConfigurationXml] [xml] NOT NULL,
    [ConfigurationRules] [nvarchar](max) NOT NULL,
    [RulesVersion] [nvarchar](50) NULL,
    [Created] [datetime] NOT NULL,
    [CampaignCalculationParameters] [nvarchar](max) NULL 

    So I need a suggestion how to overcome this problem means to improve SELECT query performance.



    Thursday, August 1, 2019 10:27 AM

All replies

  • And how do you select the row, by filtering on the XML data or ..?

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Thursday, August 1, 2019 10:31 AM
  • Yes. I have used filter.

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

    One campaign can have multiple records(tags). It slows down the select statement.

    Thursday, August 1, 2019 11:30 AM
  • Hi Manas,

    Please share a full DDL on that table, including indexes.

    Usually it is a good idea to have indexes on the column(s) in the WHERE clause.

    Also, columns with XML data type could have indexes of few types.

    Thursday, August 1, 2019 2:33 PM
  • It is not implausible that it is a matter of network speed and processing in the client.

    But there could also be fragmentation in the LOB data. You could try running
    ALTER INDEX ALL ON tbl REORGANIZE WITH (LOB_COMPACTION = ON)

    For blobs of that size, you may want to consider using FILESTREAM.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Thursday, August 1, 2019 9:21 PM