Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Поиск в иерархичном XML  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
Существует абстрактный план запроса:

DECLARE @xml XML = '
<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.2" Build="11.0.6020.0">
  <BatchSequence>
    <Batch>
      <Statements>
        <StmtSimple StatementText="&#xD;&#xA;SELECT DISTINCT d.SalesOrderID, d.UnitPrice, h.OrderDate&#xD;&#xA;FROM Sales.SalesOrderHeader h&#xD;&#xA;JOIN Sales.SalesOrderDetail d ON h.SalesOrderID = d.SalesOrderID&#xD;&#xA;,Sales.Currency c, Sales.CountryRegionCurrency r&#xD;&#xA;WHERE h.DueDate &gt; h.ShipDate" StatementId="1" StatementCompId="1" StatementType="SELECT" RetrievedFromCache="true" StatementSubTreeCost="45.2634" StatementEstRows="3.14624e+006" StatementOptmLevel="FULL" QueryHash="0x3ECB68B1AA9F7DCD" QueryPlanHash="0x9E109FEC6A3EA1D7">
          <QueryPlan CachedPlanSize="40" CompileTime="15" CompileCPU="15" CompileMemory="952">
            <MemoryGrantInfo SerialRequiredMemory="512" SerialDesiredMemory="5760" />
            <OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="38400" EstimatedPagesCached="9600" EstimatedAvailableDegreeOfParallelism="2" />
            <RelOp NodeId="0" PhysicalOp="Stream Aggregate" LogicalOp="Aggregate" EstimateRows="3.14624e+006" EstimateIO="0" EstimateCPU="3.54802" AvgRowSize="27" EstimatedTotalSubtreeCost="45.2634" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row">
              <StreamAggregate>
                <GroupBy>
                  <ColumnReference Database="[AdventureWorks2012]" Schema="[Sales]" Table="[SalesOrderDetail]" Alias="[d]" Column="UnitPrice" />
                  <ColumnReference Database="[AdventureWorks2012]" Schema="[Sales]" Table="[SalesOrderHeader]" Alias="[h]" Column="SalesOrderID" />
                </GroupBy>
                <RelOp NodeId="1" PhysicalOp="Nested Loops" LogicalOp="Inner Join" EstimateRows="3.94981e+006" EstimateIO="0" EstimateCPU="16.5102" AvgRowSize="31" EstimatedTotalSubtreeCost="41.7154" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row">
                  <NestedLoops Optimized="0">
                    <RelOp NodeId="3" PhysicalOp="Top" LogicalOp="Top" EstimateRows="1" EstimateIO="0" EstimateCPU="1e-007" AvgRowSize="9" EstimatedTotalSubtreeCost="0.0032832" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row">
                      <OutputList />
                      <Top RowCount="0" IsPercent="0" WithTies="0">
                        <TopExpression>
                          <ScalarOperator ScalarString="(1)">
                            <Const ConstValue="(1)" />
                          </ScalarOperator>
                        </TopExpression>
                        <RelOp NodeId="4" PhysicalOp="Index Scan" LogicalOp="Index Scan" EstimateRows="1" EstimateIO="0.003125" EstimateCPU="0.0002769" AvgRowSize="9" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="109" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row">
                          <OutputList />
                          <IndexScan Ordered="0" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore">
                            <DefinedValues />
                            <Warnings NoJoinPredicate="1" />
                            <Object Database="[AdventureWorks2012]" Schema="[Sales]" Table="[CountryRegionCurrency]" Index="[IX_CountryRegionCurrency_CurrencyCode]" Alias="[r]" TableReferenceId="-1" IndexKind="NonClustered" />
                          </IndexScan>
                        </RelOp>
                      </Top>
                    </RelOp>
                    <RelOp NodeId="7" PhysicalOp="Nested Loops" LogicalOp="Inner Join" EstimateRows="3.94981e+006" EstimateIO="0" EstimateCPU="16.5102" AvgRowSize="31" EstimatedTotalSubtreeCost="25.2019" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row">
                      <Warnings NoJoinPredicate="1" />
                    </RelOp>
                  </NestedLoops>
                </RelOp>
              </StreamAggregate>
            </RelOp>
          </QueryPlan>
        </StmtSimple>
      </Statements>
    </Batch>
  </BatchSequence>
</ShowPlanXML>'

На неизвестном уровне вложенности существует нод Warnings (возможно не один).

Нужно найти первый попавшийся. Пробовал вот таким запросом:

;WITH cte AS 
(
	SELECT
          [type] = t.c.value('local-name(.)', 'VARCHAR(100)')
        , x1 = CAST(NULL AS XML)
        , x = t.c.query('./*')
    FROM @XML.nodes('*') t(c)

    UNION ALL

    SELECT
          t.c.value('local-name(.)', 'VARCHAR(100)')
        , t.c.query('.')
        , t.c.query('./*')
    FROM cte
    CROSS APPLY x.nodes('*') t(c)
)
SELECT TOP(1) [type], x1
FROM cte
WHERE [type] = 'Warnings'

Но что-то мне подсказывает интуиция, что можно сделать это явно проще и элегантнее (без CTE).

Кто может подсказать или направить в правильную сторону?
10 фев 16, 19:25    [18801269]     Ответить | Цитировать Сообщить модератору
 Re: Поиск в иерархичном XML  [new]
invm
Member

Откуда: Москва
Сообщений: 8813
with xmlnamespaces(default 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
select top (1)
 t.n.query('.')
from
 @xml.nodes('//Warnings') t(n);
10 фев 16, 20:11    [18801430]     Ответить | Цитировать Сообщить модератору
 Re: Поиск в иерархичном XML  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
Такой вариант подошел:

SELECT TOP(1) t.c.query('.')
FROM @xml.nodes('//*:Warnings') t(c)

То такие крякозябры на мутную голову начал писать :)

invm, большое спасибо за помощь!
10 фев 16, 20:17    [18801444]     Ответить | Цитировать Сообщить модератору
Между сообщениями интервал более 1 года.
 Re: Поиск в иерархичном XML  [new]
tunknown
Member

Откуда:
Сообщений: 695
В известных диагностических скриптах есть тяжёлый запрос:

-- Find missing index warnings for cached plans in the current database  (Query 53) (Missing Index Warnings)
-- Note: This query could take some time on a busy instance
SELECT TOP(25) OBJECT_NAME(objectid) AS [ObjectName],
               query_plan, cp.objtype, cp.usecounts
FROM sys.dm_exec_cached_plans AS cp WITH (NOLOCK)
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
WHERE CAST(query_plan AS NVARCHAR(MAX)) LIKE N'%MissingIndex%'
AND dbid = DB_ID()
ORDER BY cp.usecounts DESC OPTION (RECOMPILE);
Like по полному тексту xml работает медленно и выдаёт в профилере много сообщений:
Экземпляр типа данных XML имеет слишком много уровней вложенных узлов. Максимально допустимая глубина составляет 128 уровней.

query_plan.value ( 'not(//*:MissingIndexes)','bit' )=0
Обсуждаемое условие на малом количестве записей быстрее Like, но на большом количестве записей работает медленно и с большим количеством таких сообщений в профилере.

(query_plan.value ( 'not(/*/*/*/*/*/*/*/*/*/*[local-name()="MissingIndexes"])','bit' )=0
or query_plan.value ( 'not(/*/*/*/*/*/*/*[local-name()="MissingIndexes"])','bit' )=0)
Условие с ограничением области поиска начинает работать быстрее на большом количестве записей и выдаёт существенно меньше сообщений в профилере. Но приходится явно указывать варианты.
11 июл 19, 17:16    [21925110]     Ответить | Цитировать Сообщить модератору
 Re: Поиск в иерархичном XML  [new]
invm
Member

Откуда: Москва
Сообщений: 8813
tunknown,

SELECT TOP(25) OBJECT_NAME(objectid) AS [ObjectName],
               query_plan, cp.objtype, cp.usecounts
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_text_query_plan(cp.plan_handle, default, default) AS qpt
WHERE qpt.query_plan LIKE N'%MissingIndex%'
AND dbid = DB_ID()
ORDER BY cp.usecounts DESC OPTION (RECOMPILE);
?
11 июл 19, 18:16    [21925174]     Ответить | Цитировать Сообщить модератору
 Re: Поиск в иерархичном XML  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 6853
tunknown,

фантазия у авторов скрипта разыгралась от недостатка образования. Информацию о недостающих индексах можно получить из динамических представлений sys.dm_db_missing_index...
11 июл 19, 18:33    [21925191]     Ответить | Цитировать Сообщить модератору
 Re: Поиск в иерархичном XML  [new]
tunknown
Member

Откуда:
Сообщений: 695
invm
CROSS APPLY sys.dm_exec_text_query_plan(cp.plan_handle, default, default) AS qpt
?
Это за 56 секунд.

Как это ни странно, вариант с xml+OR быстрее- всего 36 секунд, хотя и с сообщениями в профилере. Могу предположить, что текстовый план всё равно внутри берётся из бинарного xml, преобразуемого в текст, но почему-то без сообщений в профилере.

Но вопрос не в этих скриптах, а в произвольном поиске в xml.

Владислав Колосов
Информацию о недостающих индексах можно получить из динамических представлений sys.dm_db_missing_index...
Возможно, sqlserver не всё может сложить в единое место и они пытаются покрыть другой случай, т.к. у них упоминаются в соседних скриптах и sys.dm_db_missing_index_group_stats и sys.dm_db_missing_index_groups.
12 июл 19, 09:36    [21925441]     Ответить | Цитировать Сообщить модератору
 Re: Поиск в иерархичном XML  [new]
invm
Member

Откуда: Москва
Сообщений: 8813
tunknown,

WHERE query_plan.exist(N'//*:MissingIndexes') = 1
?
12 июл 19, 10:17    [21925493]     Ответить | Цитировать Сообщить модератору
 Re: Поиск в иерархичном XML  [new]
tunknown
Member

Откуда:
Сообщений: 695
invm
WHERE query_plan.exist(N'//*:MissingIndexes') = 1
?
То же, что и ранее проверенное
query_plan.value ( 'not(//*:MissingIndexes)','bit' )=0
хотя могло бы и побыстрее быть, т.к. внутренняя функция не используется.

Вот, если бы материализовать часть бинарного xml в текст, например, зная, что всё, что нужно встретится в первых 2000 символов. Но в явном виде это не работает.
12 июл 19, 12:40    [21925644]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить