Блог

Главная - Вопросы по программированию - Требуется оптимизация XML SQL

Требуется оптимизация XML SQL

#sql-server #xml #tsql #optimization #xquery

#sql-сервер #xml #tsql #оптимизация #xquery

Вопрос:

Привет, я пытался вставить в две таблицы (группы и поля) из XML в SQL. Но решение либо не решает мою проблему, либо производительность низкая, поскольку группы и поля могут исчисляться сотнями тысяч.

Образец XML:

 <?xml version="1.0" encoding="utf-16"?>
<FB_Flow
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:xsd="http://www.w3.org/2001/XMLSchema" id="1">
    <groups>
        <FB_FlowGroup counter="1125" position="2" positionparent="0" id="0">
            <fields>
                <FB_FlowField>
                    <value>TEST1</value>
                    <counter>111</counter>
                    <lineposition>1</lineposition>
                </FB_FlowField>
                <FB_FlowField>
                    <value>TEST2</value>
                    <counter>222</counter>
                    <lineposition>2</lineposition>
                    <groupid>0</groupid>
                </FB_FlowField>
                <FB_FlowField>
                    <value>TEST3</value>
                    <counter>333</counter>
                    <lineposition>3</lineposition>
                </FB_FlowField>
            </fields>
        </FB_FlowGroup>
        <FB_FlowGroup counter="1126" position="3" positionparent="2" id="0">
            <fields>
                <FB_FlowField>
                    <value>TEST1</value>
                    <counter>18</counter>
                    <lineposition>1</lineposition>
                </FB_FlowField>
            </fields>
        </FB_FlowGroup>     
    </groups>
</FB_Flow>

Первая часть работает нормально (для получения списка всех групп)

    insert into @Groups (intGroupCounter,intGroupPosition,intGroupPositionParent)
   SELECT
        gcounter = Groups.value('@counter[1]', 'int'),
        gposition = Groups.value('@position[1]', 'int'),
        gpositionparent = Groups.value('@positionparent[1]', 'int')
FROM
        @FlowXML.nodes('/FB_Flow/groups/FB_FlowGroup') AS XTbl(Groups)

Эта вторая часть по большей части завершается неудачно (чтобы получить все поля с позицией родительской группы):

    insert into @Fields (intGroupPosition,vFieldValue,intFieldCounter,intFieldPosition)
       SELECT
        gposition = XTbl.Groups.value('@position', 'int'),
        fValue = XTbl2.Fields.value('value[1]', 'varchar(max)'),
        fcounter = XTbl2.Fields.value('counter[1]', 'int'),
        fposition = XTbl2.Fields.value('lineposition[1]', 'int')
FROM
        @FlowXML.nodes('/FB_Flow/groups/FB_FlowGroup') AS XTbl(Groups)
cross APPLY
    Groups.nodes('fields/FB_FlowField') AS XTbl2(Fields)

Я обходил это, используя курсор и выбирая группу по атрибуту position, но производительность очень низкая.

    DECLARE @GroupCounter int,
            @GroupPosition int,
            @GroupPositionParent int,
            @GroupID int
            
   DECLARE @Groups table
   (
        intGroupCounter int not null,
        intGroupPosition int not null,
        intGroupPositionParent int null default 0
   )
            
   insert into @Groups (intGroupCounter,intGroupPosition,intGroupPositionParent)
   SELECT
        gcounter = Groups.value('@counter[1]', 'int'),
        gposition = Groups.value('@position[1]', 'int'),
        gpositionparent = Groups.value('@positionparent[1]', 'int')
FROM
        @FlowXML.nodes('/FB_Flow/groups/FB_FlowGroup') AS XTbl(Groups)

   DECLARE cur cursor for 
   SELECT
        intGroupCounter,
        intGroupPosition,
        intGroupPositionParent
    FROM
        @Groups

    OPEN cur

    FETCH NEXT FROM cur INTO @GroupCounter, @GroupPosition, @GroupPositionParent

    WHILE @@FETCH_STATUS = 0
    BEGIN
        insert into FB_T_FlowGroups (FH_ID,DTC_GroupCounter,Position,PositionParent)
        values (@FlowHeaderID,@GroupCounter,@GroupPosition,@GroupPositionParent)

        select @GroupID = @@IDENTITY 

        --declare @Path varchar(max) = '/FB_Flow/groups/FB_FlowGroup[@position="sql:variable("@GroupPosition")"]/fields/FB_FlowField' 
        
        insert into FB_T_FlowGroupField (FlowGroupID,ItemValue,DTC_ItemCounter)
        SELECT
            @GroupID,
            XTbl.Fields.value('value[1]', 'varchar(max)'),
            XTbl.Fields.value('counter[1]', 'int')
        FROM
            @FlowXML.nodes('/FB_Flow/groups/FB_FlowGroup[@position=sql:variable("@GroupPosition")]/fields/FB_FlowField') AS XTbl(Fields)
        

        FETCH NEXT FROM cur INTO @GroupCounter, @GroupPosition, @GroupPositionParent
    END
    CLOSE cur
    DEALLOCATE cur

Есть идеи?

Ответ №1:

Какая у вас версия SQL Server ( SELECT @@VERSION; )?

Пожалуйста, попробуйте следующий подход без курсора. Это должно дать вам огромное улучшение производительности:

  • Атрибутам XML не требуется позиция [1]. Атрибуты всегда уникальны.
  • Элементы XML нуждаются в корректировке в выражении XPath — text() .

SQL

 DECLARE @FlowXML XML =
N'<FB_Flow xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xmlns:xsd="http://www.w3.org/2001/XMLSchema" id="1">
    <groups>
        <FB_FlowGroup counter="1125" position="2" positionparent="0" id="0">
            <fields>
                <FB_FlowField>
                    <value>TEST1</value>
                    <counter>111</counter>
                    <lineposition>1</lineposition>
                </FB_FlowField>
                <FB_FlowField>
                    <value>TEST2</value>
                    <counter>222</counter>
                    <lineposition>2</lineposition>
                    <groupid>0</groupid>
                </FB_FlowField>
                <FB_FlowField>
                    <value>TEST3</value>
                    <counter>333</counter>
                    <lineposition>3</lineposition>
                </FB_FlowField>
            </fields>
        </FB_FlowGroup>
        <FB_FlowGroup counter="1126" position="3" positionparent="2" id="0">
            <fields>
                <FB_FlowField>
                    <value>TEST1</value>
                    <counter>18</counter>
                    <lineposition>1</lineposition>
                </FB_FlowField>
            </fields>
        </FB_FlowGroup>
    </groups>
</FB_Flow>';

-- insert into @Groups (intGroupCounter,intGroupPosition,intGroupPositionParent)
SELECT gcounter = Groups.value('@counter', 'INT')
    , gposition = Groups.value('@position', 'INT')
    , gpositionparent = Groups.value('@positionparent', 'INT')
FROM @FlowXML.nodes('/FB_Flow/groups/FB_FlowGroup') AS XTbl(Groups);

--insert into @Fields (intGroupPosition,vFieldValue,intFieldCounter,intFieldPosition)
SELECT gposition = XTbl.Groups.value('@position', 'INT')
    , fValue = XTbl2.Fields.value('(value/text())[1]', 'VARCHAR(MAX)')
    , fcounter = XTbl2.Fields.value('(counter/text())[1]', 'INT')
    , fposition = XTbl2.Fields.value('(lineposition/text())[1]', 'INT')
FROM @FlowXML.nodes('/FB_Flow/groups/FB_FlowGroup') AS XTbl(Groups)
    CROSS APPLY Groups.nodes('fields/FB_FlowField') AS XTbl2(Fields);

Комментарии:

1. Отлично, исправил мою проблему, спасибо и хорошо объяснил.

2. @JeffCorn, рад слышать, что предложенное решение работает для вас. Пожалуйста, не забудьте отметить это как ответ. Кроме того, пожалуйста, проголосуйте здесь: feedback.azure.com/forums/908035-sql-server/suggestions /…