从SQL Server中读取XML数据会降低性能

Question

我正在使用下面脚本的SQL视图。由于表格将数据存储为XML（dbo.TEST.configuration），所以我必须通过读取xml数据来选择每个列。但是，当记录增加时，它加载速度变得太慢。有没有什么方法可以改善它的性能，请让我知道？

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 

在此先感谢。

xml数据示例如下;

<?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> 

Answer 1

;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