sql-server - SQL：来自CTE的xml.nodes非常慢(SQL: xml.nodes from cte are very slowly)

Question

I have table with a column that contains xml like this:

(我有一个带有包含xml的列的表，如下所示：)

<block>
    <blockIn>
        <G>1</G>            
    </blockIn>
    .....
    <blockIn>
        <G>12</G>
    </blockIn>
    ......
</block>
.....
<block>
......
</block>

I need find MAX between <blockIn><G> in each , and then summarize all this MAX

(我需要在每个<blockIn><G>之间找到MAX，然后总结所有这些MAX)

(sum (Max (<block> …<blockIn> ...<G></G>); Max (<block> …<blockIn> ...<G></G>) ...))

So, I did this:

(因此，我这样做：)

WITH ds AS 
(
    SELECT 
        fieldXML
    FROM 
        table
    WHERE 
        ID = 1
)
SELECT 
    (SELECT SUM(node_a.value('max(blockIn/G)' , 'int' )) 
     FROM ds.fieldXML.nodes('/Block')  AS node_refs(node_a)) AS [ArticulNum]
FROM
    ds

But it works very slowly.

(但是它工作非常缓慢。)

If I use a variable, it works very fast:

(如果我使用一个变量，它将非常快速地工作：)

DECLARE @xml AS [XML];

SELECT 
    @xml = fieldXML
FROM 
    table
WHERE 
    ID = 1;

SELECT SUM(node_a.value('max(blockIn/G)' , 'INT' )) 
FROM @xml.fieldXML.nodes('/Block') AS node_refs(node_a)

What do I need to do so that the first solution works fast, too?

(我需要怎么做才能使第一个解决方案也能快速运行？)

  ask by dva translate from so

Answer 1

等待大神答复