я есть 34 строки в базе данных, каждая строка имеет столбец, содержащий xml - xml фактически находится в столбце NVARCHAR (MAX), а не в столбце XML.

Для каждой строки я выбираю значения в элементах xml как один набор результатов. Производительность довольно плохая. Я пробовал два разных запроса. Первое выполнение занимает примерно 22 секунды, а второе - 7.

Даже через 7 секунд это намного медленнее, чем оптимально, я надеюсь, что максимум 1-2 секунды.

Итак, я прочитал в Интернете слух, что если вы преобразуете данные NVARCHAR в XML, используя временную таблицу или табличную переменную, вы добьетесь увеличения производительности, что, по крайней мере, в моем случае было истиной ... Теперь оно выполняется менее чем за секунду , Сейчас я ищу объяснение, которое может объяснить, почему эти два подхода действительно влияют на производительность.

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)

Этот запрос использует метод text (), но сначала преобразует столбец 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)