Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Поиск в иерархичном XML / 9 сообщений из 9, страница 1 из 1
10.02.2016, 19:25
    #39167995
AlanDenton
AlanDenton
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Поиск в иерархичном XML
Существует абстрактный план запроса:

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
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 (возможно не один).

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

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
;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).

Кто может подсказать или направить в правильную сторону?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
10.02.2016, 20:11
    #39168016
invm
invm
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Поиск в иерархичном XML
Код: sql
1.
2.
3.
4.
5.
with xmlnamespaces(default 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
select top (1)
 t.n.query('.')
from
 @xml.nodes('//Warnings') t(n);
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
10.02.2016, 20:17
    #39168023
AlanDenton
AlanDenton
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Поиск в иерархичном XML
Такой вариант подошел:

Код: sql
1.
2.
SELECT TOP(1) t.c.query('.')
FROM @xml.nodes('//*:Warnings') t(c)


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

invm, большое спасибо за помощь!
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Период между сообщениями больше года.
11.07.2019, 17:16
    #39836495
tunknown
tunknown
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Поиск в иерархичном XML
В известных диагностических скриптах есть тяжёлый запрос:

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
-- 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 работает медленно и выдаёт в профилере много сообщений:
Код: plaintext
Экземпляр типа данных XML имеет слишком много уровней вложенных узлов. Максимально допустимая глубина составляет 128 уровней.

Код: sql
1.
query_plan.value ( 'not(//*:MissingIndexes)','bit' )=0

Обсуждаемое условие на малом количестве записей быстрее Like, но на большом количестве записей работает медленно и с большим количеством таких сообщений в профилере.

Код: sql
1.
2.
(query_plan.value ( 'not(/*/*/*/*/*/*/*/*/*/*[local-name()="MissingIndexes"])','bit' )=0
or query_plan.value ( 'not(/*/*/*/*/*/*/*[local-name()="MissingIndexes"])','bit' )=0)

Условие с ограничением области поиска начинает работать быстрее на большом количестве записей и выдаёт существенно меньше сообщений в профилере. Но приходится явно указывать варианты.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
11.07.2019, 18:16
    #39836528
invm
invm
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Поиск в иерархичном XML
tunknown,

Код: sql
1.
2.
3.
4.
5.
6.
7.
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);

?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
11.07.2019, 18:33
    #39836543
Владислав Колосов
Владислав Колосов
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Поиск в иерархичном XML
tunknown,

фантазия у авторов скрипта разыгралась от недостатка образования. Информацию о недостающих индексах можно получить из динамических представлений sys.dm_db_missing_index...
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
12.07.2019, 09:36
    #39836665
tunknown
tunknown
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Поиск в иерархичном XML
invm
Код: sql
1.
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.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
12.07.2019, 10:17
    #39836690
invm
invm
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Поиск в иерархичном XML
tunknown,

Код: sql
1.
WHERE query_plan.exist(N'//*:MissingIndexes') = 1

?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
12.07.2019, 12:40
    #39836786
tunknown
tunknown
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Поиск в иерархичном XML
invm
Код: sql
1.
WHERE query_plan.exist(N'//*:MissingIndexes') = 1

?То же, что и ранее проверенное
Код: sql
1.
query_plan.value ( 'not(//*:MissingIndexes)','bit' )=0

хотя могло бы и побыстрее быть, т.к. внутренняя функция не используется.

Вот, если бы материализовать часть бинарного xml в текст, например, зная, что всё, что нужно встретится в первых 2000 символов. Но в явном виде это не работает.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Поиск в иерархичном XML / 9 сообщений из 9, страница 1 из 1
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]