sql server - XML सर्वर XML प्रदर्शन अनुकूलन



sql-server performance (1)

मैं आपको एक उत्तर और एक अनुमान दे सकता हूं:

पहले मैं आपके परिदृश्य का मजाक उड़ाने के लिए एक घोषित तालिका चर का उपयोग करता हूं:

DECLARE @tbl TABLE(s NVARCHAR(MAX));
INSERT INTO @tbl VALUES
(N'<root>
    <SomeElement>This is first text of element1
        <InnerElement>This is text of inner element1</InnerElement>
        This is second text of element1
    </SomeElement>
    <SomeElement>This is first text of element2
        <InnerElement>This is text of inner element2</InnerElement>
        This is second text of element2
    </SomeElement>
</root>')
,(N'<root>
    <SomeElement>This is first text of elementA
        <InnerElement>This is text of inner elementA</InnerElement>
        This is second text of elementA
    </SomeElement>
    <SomeElement>This is first text of elementB
        <InnerElement>This is text of inner elementB</InnerElement>
        This is second text of elementB
    </SomeElement>
</root>');

- यह क्वेरी XML को उप-चयन से बाहर डाली के साथ पढ़ेगी। आप इसके बजाय एक CTE उपयोग कर सकते हैं, लेकिन यह केवल सिंथेटिक चीनी होना चाहिए ...

SELECT se.value(N'(.)[1]','nvarchar(max)') SomeElementsContent
      ,se.value(N'(InnerElement)[1]','nvarchar(max)') InnerElementsContent
      ,se.value(N'(./text())[1]','nvarchar(max)') ElementsFirstText
      ,se.value(N'(./text())[2]','nvarchar(max)') ElementsSecondText
FROM (SELECT CAST(s AS XML) FROM @tbl) AS tbl(TheXml)
CROSS APPLY TheXml.nodes(N'/root/SomeElement') AS A(se);

- दूसरा भाग टाइप किए गए XML में लिखने और वहाँ से पढ़ने के लिए एक तालिका का उपयोग करता है:

DECLARE @tbl2 TABLE(x XML)
INSERT INTO @tbl2
SELECT CAST(s AS XML) FROM @tbl;

SELECT se.value(N'(.)[1]','nvarchar(max)') SomeElementsContent
      ,se.value(N'(InnerElement)[1]','nvarchar(max)') InnerElementsContent
      ,se.value(N'(./text())[1]','nvarchar(max)') ElementsFirstText
      ,se.value(N'(./text())[2]','nvarchar(max)') ElementsSecondText
FROM @tbl2 t2
CROSS APPLY t2.x.nodes(N'/root/SomeElement') AS A(se);

क्यों /text() बिना /text() तुलना में तेज है?

यदि आप मेरे उदाहरण को देखते हैं, तो एक तत्व की सामग्री शुरुआती नोड से लेकर समापन नोड तक सब कुछ है । एक तत्व का text() केवल दो तत्वों के बीच का पाठ है। आप इसे ऊपर के चयन के परिणामों में देख सकते हैं। text() एक पेड़ संरचना में वास्तव में एक अलग संग्रहीत भाग है (अगला भाग पढ़ें)। लाने के लिए यह एक कदम-कदम है । अन्यथा एक जटिल संरचना का उद्घाटन टैग और उसके संबंधित समापन टैग के बीच सब कुछ खोजने के लिए विश्लेषण किया जाना है - भले ही text() अलावा और कुछ नहीं हो।

मुझे XML को उपयुक्त प्रकार में क्यों संग्रहीत करना चाहिए?

XML सिर्फ कुछ मूर्खतापूर्ण अतिरिक्त पात्रों के साथ पाठ नहीं है! यह एक जटिल संरचना वाला एक दस्तावेज है। XML को आपके द्वारा देखे गए पाठ के रूप में संग्रहीत नहीं किया जाता है । XML एक ट्री संरचना में संग्रहीत है। जब भी आप एक स्ट्रिंग डालते हैं, जो एक XML का प्रतिनिधित्व करता है, एक वास्तविक XML में, यह बहुत महंगा काम होना चाहिए। जब एक्सएमएल आपके (या किसी अन्य आउटपुट) के लिए प्रस्तुत किया जाता है तो स्क्रैच से निर्मित स्ट्रिंग (री) का प्रतिनिधित्व होता है।

प्री-कास्ट अप्रोच क्यों तेज है

यह अनुमान लगा रहा है ...
मेरे उदाहरण में दोनों दृष्टिकोण काफी समान हैं और एक ही निष्पादन योजना के लिए (लगभग) नेतृत्व करते हैं।
SQL सर्वर सब कुछ नीचे काम नहीं करेगा जिस तरह से आप यह उम्मीद कर सकते हैं। यह एक प्रक्रियात्मक प्रणाली नहीं है, जहाँ आप ऐसा करते हैं, ऐसा करने के बाद और ऐसा करने के बाद, आप ऐसा करते हैं! । आप इंजन को बताएं कि आप क्या चाहते हैं, और इंजन यह तय करता है कि यह कैसे करना है। और इंजन इस के साथ बहुत अच्छा है!
निष्पादन शुरू होने से पहले, इंजन दृष्टिकोण की लागत का अनुमान लगाने की कोशिश करता है। CONVERT (या CAST ) एक सस्ता ऑपरेशन है। यह हो सकता है, कि इंजन आपकी कॉल की सूची को कम करने और हर एक की जरूरत के लिए कास्ट करने का फैसला करता है, क्योंकि यह सोचता है, कि यह एक व्युत्पन्न तालिका की महंगी रचना से सस्ता है ...

मुझे एक डेटाबेस में 34 पंक्तियाँ मिली हैं, प्रत्येक पंक्ति में एक कॉलम है जिसमें xml है - xml वास्तव में एक NVARCHAR (MAX) कॉलम में है जो XML स्तंभ नहीं है।

प्रत्येक पंक्ति के लिए मैं एक परिणाम के रूप में xml तत्वों में मानों का चयन कर रहा हूं। प्रदर्शन बहुत खराब है। मैंने दो अलग-अलग प्रश्नों की कोशिश की है। पहले को निष्पादित करने में लगभग 22 सेकंड लगते हैं और दूसरे को 7 लगते हैं।

7 सेकंड में भी, यह इष्टतम की तुलना में बहुत धीमी है, मैं 1-2 सेकंड के लिए सबसे अधिक उम्मीद कर रहा हूं।

तो फिर मैंने एक अफवाह ऑनलाइन पढ़ी कि यदि आप NVARCHAR डेटा को एक अस्थायी तालिका या टेबल चर का उपयोग करके XML में परिवर्तित करते हैं, तो आप एक प्रदर्शन लाभ प्राप्त करेंगे, जो कम से कम मेरे मामले में सच था ... अब यह एक दूसरे के तहत निष्पादित होता है । अब मैं जो खोज रहा हूं वह एक स्पष्टीकरण है जो मुझे बता सकता है कि ये 2 दृष्टिकोण वास्तव में प्रदर्शन को क्यों प्रभावित करते हैं।

22 सेकंड:

SELECT
    c.ID,
    c.ChannelName,
    [Name] = d.c.value('name[1]','varchar(100)'),
    [Type] = d.c.value('transportName[1]','varchar(100)'),
    [Enabled] = d.c.value('enabled[1]','BIT'),
    [Queued] = d.c.value('properties[1]/destinationConnectorProperties[1]/queueEnabled[1]','varchar(100)'),
    [RetryInterval] = d.c.value('properties[1]/destinationConnectorProperties[1]/retryIntervalMillis[1]','INT'),
    [MaxRetries] = d.c.value('properties[1]/destinationConnectorProperties[1]/retryCount[1]','INT'),
    [RotateQueue] = d.c.value('properties[1]/destinationConnectorProperties[1]/rotate[1]','BIT'),
    [ThreadCount] = d.c.value('properties[1]/destinationConnectorProperties[1]/threadCount[1]','INT'),
    [WaitForPrevious] = d.c.value('waitForPrevious[1]','BIT'),
    [Destination] = COALESCE(
        d.c.value('properties[1]/channelId[1]','varchar(100)'),
        d.c.value('properties[1]/remoteAddress[1]','varchar(100)'),
        d.c.value('properties[1]/wsdlUrl[1]','varchar(1024)')),

    [DestinationPort] = COALESCE(
        d.c.value('properties[1]/remotePort[1]','varchar(100)'),
        d.c.value('properties[1]/port[1]','varchar(1024)')),
    [Service] = d.c.value('properties[1]/service[1]','varchar(1024)'),
    [Operation] = d.c.value('properties[1]/operation[1]','varchar(1024)')
FROM
(
    SELECT
            [ID],
            [ChannelName] = [Name],
            [CFG] = Convert(XML, Channel)
    FROM
            dbo.CHANNEL
) c
CROSS APPLY c.CFG.nodes('/channel/destinationConnectors/connector') d(c)

पाठ के उपयोग के कारण 7 सेकंड ()। मुझे पता नहीं है कि पाठ चीजों को गति क्यों देता है।

SELECT
    c.ID,
    c.ChannelName,
    [Name] = d.c.value('(name/text())[1]','varchar(100)'),
    [Type] = d.c.value('(transportName/text())[1]','varchar(100)'),
    [Enabled] = d.c.value('(enabled/text())[1]','BIT'),
    [Queued] = d.c.value('(properties/destinationConnectorProperties/queueEnabled/text())[1]','varchar(100)'),
    [RetryInterval] = d.c.value('(properties/destinationConnectorProperties/retryIntervalMillis/text())[1]','INT'),
    [MaxRetries] = d.c.value('(properties/destinationConnectorProperties/retryCount/text())[1]','INT'),
    [RotateQueue] = d.c.value('(properties/destinationConnectorProperties/rotate/text())[1]','BIT'),
    [ThreadCount] = d.c.value('(properties/destinationConnectorProperties/threadCount/text())[1]','INT'),
    [WaitForPrevious] = d.c.value('(waitForPrevious/text())[1]','BIT'),
    [Destination] = COALESCE(
        d.c.value('(properties/channelId/text())[1]','varchar(100)'),
        d.c.value('(properties/remoteAddress/text())[1]','varchar(100)'),
        d.c.value('(properties/wsdlUrl/text())[1]','varchar(1024)')),

    [DestinationPort] = COALESCE(
        d.c.value('(properties/remotePort/text())[1]','varchar(100)'),
        d.c.value('(properties/port/text())[1]','varchar(1024)')),
    [Service] = d.c.value('(properties/service/text())[1]','varchar(1024)'),
    [Operation] = d.c.value('(properties/operation/text())[1]','varchar(1024)')
FROM
(
    SELECT
            [ID],
            [ChannelName] = [Name],
            [CFG] = Convert(XML, Channel)
    FROM
            dbo.CHANNEL
) c
CROSS APPLY c.CFG.nodes('/channel/destinationConnectors/connector') d(c)

यह क्वेरी पाठ () दृष्टिकोण का उपयोग करता है, लेकिन पहले एक तालिका चर में NVARCHAR कॉलम को xml कॉलम में कनवर्ट करता है। एक सेकंड से भी कम समय में निष्पादित ...

DECLARE @Xml AS TABLE (
    [ID] NVARCHAR(36) NOT NULL Primary Key,
    [Name] NVARCHAR(100) NOT NULL,
    [CFG] XML NOT NULL
);

INSERT INTO @Xml (ID, Name, CFG)
SELECT
    c.ID,
    c.Name,
    Convert(XML, c.Channel)
FROM
    [dbo].[CHANNEL] c;

SELECT
    c.ID,
    c.ChannelName,
    [Name] = d.c.value('(name/text())[1]','varchar(100)'),
    [Type] = d.c.value('(transportName/text())[1]','varchar(100)'),
    [Enabled] = d.c.value('(enabled/text())[1]','BIT'),
    [Queued] = d.c.value('(properties/destinationConnectorProperties/queueEnabled/text())[1]','varchar(100)'),
    [RetryInterval] = d.c.value('(properties/destinationConnectorProperties/retryIntervalMillis/text())[1]','INT'),
    [MaxRetries] = d.c.value('(properties/destinationConnectorProperties/retryCount/text())[1]','INT'),
    [RotateQueue] = d.c.value('(properties/destinationConnectorProperties/rotate/text())[1]','BIT'),
    [ThreadCount] = d.c.value('(properties/destinationConnectorProperties/threadCount/text())[1]','INT'),
    [WaitForPrevious] = d.c.value('(waitForPrevious/text())[1]','BIT'),
    [Destination] = COALESCE(
        d.c.value('(properties/channelId/text())[1]','varchar(100)'),
        d.c.value('(properties/remoteAddress/text())[1]','varchar(100)'),
        d.c.value('(properties/wsdlUrl/text())[1]','varchar(1024)')),

    [DestinationPort] = COALESCE(
        d.c.value('(properties/remotePort/text())[1]','varchar(100)'),
        d.c.value('(properties/port/text())[1]','varchar(1024)')),
    [Service] = d.c.value('(properties/service/text())[1]','varchar(1024)'),
    [Operation] = d.c.value('(properties/operation/text())[1]','varchar(1024)')
FROM
(
    SELECT
            [ID],
            [ChannelName] = [Name],
            [CFG]
    FROM
            @Xml
) c
CROSS APPLY c.CFG.nodes('/channel/destinationConnectors/connector') d(c)




sql-server-2012