Reading from XML data from SQL Server reduces performance

Question

I am having a SQL view of the below script. As the table stores the data as XML (dbo.TEST.configuration), I have to select each column by reading the xml data. But when the record increases, it is getting too slow to load. Is there any method to improve its performance, please let me know?

WITH XMLNAMESPACES( DEFAULT 'http://tempuri.org/xmlConfiguration.xsd')

SELECT      dbo.TEST.uid, dbo.TEST.name, dbo.TEST.ClassUid, dbo.TEST.xmlData, 
                  CAST(REPLACE(CAST(dbo.TEST.configuration AS VARCHAR(MAX)), 'encoding="utf-8"', '') 
                  AS XML ).value('(/xmlConfiguration/batchUid)[1]', 'NVARCHAR(200)') AS batchUid, 
                  CAST(REPLACE(CAST(dbo.TEST.xmlData AS VARCHAR(MAX)), 'encoding="utf-8"', '') 
                  AS XML ).value('(/xmlConfiguration/statusUid)[1]', 'NVARCHAR(200)') AS statusUid, 
                  CAST(REPLACE(CAST(dbo.TEST.xmlData AS VARCHAR(MAX)), 'encoding="utf-8"', '') 
                  AS XML ).value('(/xmlConfiguration/subjectUid)[1]', 'NVARCHAR(200)') AS subjectUid, 
                  CAST(REPLACE(CAST(dbo.TEST.xmlData AS VARCHAR(MAX)), 'encoding="utf-8"', '') 
                  AS XML ).value('(/xmlConfiguration/cultureUid)[1]', 'NVARCHAR(200)') AS cultureUid, 
                  CAST(REPLACE(CAST(dbo.TEST.xmlData AS VARCHAR(MAX)), 'encoding="utf-8"', '') 
                  AS XML ).value('(/xmlConfiguration/format)[1]', 'NVARCHAR(200)') AS format, 
                  CAST(REPLACE(CAST(dbo.TEST.xmlData AS VARCHAR(MAX)), 'encoding="utf-8"', '') 
                  AS XML ).value('(/xmlConfiguration/secondFormat)[1]', 'NVARCHAR(200)') AS secondFormat, 
                  CAST(REPLACE(CAST(dbo.TEST.xmlData AS VARCHAR(MAX)), 'encoding="utf-8"', '') 
                  AS XML ).value('(/xmlConfiguration/standardUid)[1]', 'NVARCHAR(200)') AS standardUid
FROM         dbo.TEST INNER JOIN
                  dbo.batch ON CAST(REPLACE(CAST(dbo.TEST.xmlData AS VARCHAR(MAX)), 'encoding="utf-8"', '') 
                  AS XML ).value('(/xmlConfiguration/batchUid)[1]', 'NVARCHAR(200)') = dbo.batch.uid INNER JOIN
                  dbo.status ON CAST(REPLACE(CAST(dbo.TEST.xmlData AS VARCHAR(MAX)), 'encoding="utf-8"', '') 
                  AS XML ).value('(/xmlConfiguration/statusUid)[1]', 'NVARCHAR(200)') = dbo.status.uid INNER JOIN
                  dbo.vFormat ON CAST(REPLACE(CAST(dbo.TEST.xmlData AS VARCHAR(MAX)), 'encoding="utf-8"', '') 
                  AS XML ).value('(/xmlConfiguration/format)[1]', 'NVARCHAR(200)') = CONVERT(NVARCHAR(200), dbo.vFormat.uid) OR
                  CAST(REPLACE(CAST(dbo.TEST.xmlData AS VARCHAR(MAX)), 'encoding="utf-8"', '') 
                  AS XML ).value('(/xmlConfiguration/secondFormat)[1]', 'NVARCHAR(200)') = CONVERT(NVARCHAR(200), dbo.vFormat.uid) INNER JOIN
                  dbo.standard ON CAST(REPLACE(CAST(dbo.TEST.xmlData AS VARCHAR(MAX)), 'encoding="utf-8"', '') 
                  AS XML ).value('(/xmlConfiguration/standardUid)[1]', 'NVARCHAR(200)') = dbo.standard.uid INNER JOIN
                  mainDb.dbo.Class ON dbo.TEST.ClassUid = mainDb.dbo.Class.uid INNER JOIN
                  dbo.subject ON CAST(REPLACE(CAST(dbo.TEST.xmlData AS VARCHAR(MAX)), 'encoding="utf-8"', '') 
                  AS XML ).value('(/xmlConfiguration/subjectUid)[1]', 'NVARCHAR(200)') = dbo.subject.uid INNER JOIN
                  mainDb.dbo.culture ON CAST(REPLACE(CAST(dbo.TEST.xmlData AS VARCHAR(MAX)), 'encoding="utf-8"', '') 
                  AS XML ).value('(/xmlConfiguration/cultureUid)[1]', 'NVARCHAR(200)') = mainDb.dbo.culture.uid

Thanks in advance.

Sample xml data is below;

 <?xml version="1.0" encoding="utf-8"?> 
 <xmlConfiguration xmlns="http://tempuri.org/xmlConfiguration.xsd">              
 <secondFormat>3bd3d9cc-ad0a-e611-b086-00e04c6804ad</secondFormat>               
 <batchUid>c7b4743b-4493-df11-981e-00221933d118</batchUid>               
 <statusUid>f0b159ec-4193-df11-981e-00221933d118</statusUid>               
 <subjectUid>d07b5d66-3b5b-de11-b569-001143e78e41</subjectUid>               
 <cultureUid>c6644752-93d7-df11-981e-00221933d118</cultureUid>               
 <name>test</name>               
 <standardUid>dc19869b-3ea9-df11-981e-00221933d118</standardUid>               
 <format></format>            
</ConnectorConfiguration>

This is strange... Is the XML stored in a column of datatype XML? Is this casting and replacing over and over of any value? Please give more information on your table's structure and maybe some sample rows... — Shnugo, Apr 25 '16 at 11:02
@jarlh If the data is XML already there should be no need to cast at all... — Shnugo, Apr 25 '16 at 11:07
What datatypes are your "Uid" values? Is this `UNIQUEIDENTIFIER`? Are your joins always joining from one `UNIQUEIDENTIFIER` to another column of type `UNIQUEIDENTIFIER`? — Shnugo, Apr 25 '16 at 11:08
The column is not of datatype XML, it is nvarchar(MAX). Uid columns are of the type UNIQUEIDENTIFIER. — Justin, Apr 25 '16 at 11:10
So this is stored as `NVARCHAR(MAX)` but the XML content starts with `... encoding="utf-8"...` - This is something you should change as quickly as possible! This replacing, casting and XPath-querying over and over and over **must** be slow... — Shnugo, Apr 25 '16 at 11:12
Main issue in `CAST`. Just add `.query('.')` and reduce using of `XQuery` methods — Devart, Apr 25 '16 at 11:14
@user3107071 I strongly advise to look at examples from my presentation about `XML & XQuery` (#26-28) - https://www.devart.com/pub/24_hop_russian_2016.zip — Devart, Apr 25 '16 at 11:30

score 1 · Accepted Answer · answered Apr 25 '16 at 11:13

;WITH XMLNAMESPACES( DEFAULT 'http://tempuri.org/xmlConfiguration.xsd')
SELECT
    x.uid,
    x.name,
    x.ClassUid,
    x.xmlData, 
    x.batchUid, 
    x.statusUid, 
    x.subjectUid, 
    x.cultureUid, 
    x.format, 
    x.secondFormat, 
    x.standardUid
FROM (
    SELECT *,
        x.value('(/xmlConfiguration/batchUid)[1]', 'NVARCHAR(200)') AS batchUid, 
        x.value('(/xmlConfiguration/statusUid)[1]', 'NVARCHAR(200)') AS statusUid, 
        x.value('(/xmlConfiguration/subjectUid)[1]', 'NVARCHAR(200)') AS subjectUid, 
        x.value('(/xmlConfiguration/cultureUid)[1]', 'NVARCHAR(200)') AS cultureUid, 
        x.value('(/xmlConfiguration/format)[1]', 'NVARCHAR(200)') AS format, 
        x.value('(/xmlConfiguration/secondFormat)[1]', 'NVARCHAR(200)') AS secondFormat, 
        x.value('(/xmlConfiguration/standardUid)[1]', 'NVARCHAR(200)') AS standardUid
    FROM (
        SELECT *, x = CAST(
                CAST(REPLACE(CAST(xmlData AS VARCHAR(MAX)), 'encoding="utf-8"', '') AS XML
            ).query('.') ---- .query('.')
        FROM dbo.TEST
    ) T
) x
JOIN dbo.batch ON x.batchUid = dbo.batch.uid
JOIN dbo.status ON x.statusUid = dbo.status.uid
JOIN dbo.vFormat ON CONVERT(NVARCHAR(200), dbo.vFormat.uid) IN (x.format, x.secondFormat)
JOIN dbo.standard ON x.standardUid = dbo.standard.uid
JOIN mainDb.dbo.Class ON dbo.TEST.ClassUid = mainDb.dbo.Class.uid
JOIN dbo.subject ON x.subjectUid = dbo.subject.uid
JOIN mainDb.dbo.culture ON x.cultureUid = mainDb.dbo.culture.uid

I was working on something similar, you've made the race :-) upvote from my side... — Shnugo, Apr 25 '16 at 11:19

Reading from XML data from SQL Server reduces performance

1 Answers1