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

Откуда:
Сообщений: 21
Всем доброго!
Есть таблица:
CREATE TABLE [dbo].[ATestInsert](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[xml] [xml] NOT NULL,
 CONSTRAINT [PK_ATest] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

1. подсчет кол-ва строк таким образом выполняется очень медленно (порядка 20 сек на таблице с ~1млн записей):
SELECT COUNT(*) FROM ATestInsert
WHERE xml.exist('/root/groups/group/@id[. = 3]') = 1

2. В тоже время, следующая выборка, выполняется довольно быстро (~0.002 сек):
SELECT * FROM ATestInsert
WHERE xml.exist('/root/groups/group/@id[. = 3]') = 1

Планы выполнения соответственно, для 1 и для 2-го случаев
StmtText                                                                                                                                                                                                                                                         StmtId      NodeId      Parent      PhysicalOp                     LogicalOp                      Argument                                                                                                                                                                                                                                                         DefinedValues                                                      EstimateRows  EstimateIO    EstimateCPU   AvgRowSize  TotalSubtreeCost OutputList                                                                                                       Warnings Type                                                             Parallel EstimateExecutions
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- ----------- ----------- ------------------------------ ------------------------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------ ------------- ------------- ------------- ----------- ---------------- ---------------------------------------------------------------------------------------------------------------- -------- ---------------------------------------------------------------- -------- ------------------
SELECT COUNT(*) FROM ATestInsert

WHERE xml.exist('/root/groups/group/@id[. = 3]') = 1                                                                                                                                                                          2           6           0           NULL                           NULL                           2                                                                                                                                                                                                                                                                NULL                                                               1             NULL          NULL          NULL        110503,6         NULL                                                                                                             NULL     SELECT                                                           0        NULL
  |--Compute Scalar(DEFINE:([Expr1009]=CONVERT_IMPLICIT(int,[globalagg1012],0)))                                                                                                                                                                                 2           8           6           Compute Scalar                 Compute Scalar                 DEFINE:([Expr1009]=CONVERT_IMPLICIT(int,[globalagg1012],0))                                                                                                                                                                                                      [Expr1009]=CONVERT_IMPLICIT(int,[globalagg1012],0)                 1             0             1E-07         11          110503,6         [Expr1009]                                                                                                       NULL     PLAN_ROW                                                         0        1
       |--Stream Aggregate(DEFINE:([globalagg1012]=SUM([partialagg1011])))                                                                                                                                                                                       2           9           8           Stream Aggregate               Aggregate                      NULL                                                                                                                                                                                                                                                             [globalagg1012]=SUM([partialagg1011])                              1             0             1,7E-06       15          110503,6         [globalagg1012]                                                                                                  NULL     PLAN_ROW                                                         0        1
            |--Parallelism(Gather Streams)                                                                                                                                                                                                                       2           10          9           Parallelism                    Gather Streams                 NULL                                                                                                                                                                                                                                                             NULL                                                               2             0             0,02850215    15          110503,6         [partialagg1011]                                                                                                 NULL     PLAN_ROW                                                         1        1
                 |--Stream Aggregate(DEFINE:([partialagg1011]=Count(*)))                                                                                                                                                                                         2           11          10          Stream Aggregate               Aggregate                      NULL                                                                                                                                                                                                                                                             [partialagg1011]=Count(*)                                          2             0             0,2970005     15          110503,5         [partialagg1011]                                                                                                 NULL     PLAN_ROW                                                         1        1
                      |--Nested Loops(Left Semi Join, OUTER REFERENCES:([mlst].[dbo].[ATestInsert].[xml]))                                                                                                                                                       2           12          11          Nested Loops                   Left Semi Join                 OUTER REFERENCES:([mlst].[dbo].[ATestInsert].[xml])                                                                                                                                                                                                              NULL                                                               990000        0             2,299         9           110503,2         NULL                                                                                                             NULL     PLAN_ROW                                                         1        1
                           |--Clustered Index Scan(OBJECT:([mlst].[dbo].[ATestInsert].[PK_ATest]))                                                                                                                                                               2           13          12          Clustered Index Scan           Clustered Index Scan           OBJECT:([mlst].[dbo].[ATestInsert].[PK_ATest])                                                                                                                                                                                                                   [mlst].[dbo].[ATestInsert].[xml]                                   1100000       18,95201      0,6050785     4035        19,55709         [mlst].[dbo].[ATestInsert].[xml]                                                                                 NULL     PLAN_ROW                                                         1        1
                           |--Filter(WHERE:(xsd_cast(XML Reader with XPath filter.[value],XML Reader with XPath filter.[lvalue],XML Reader with XPath filter.[tid],(18),(15)) IS NOT NULL AND xsd_cast(XML Reader with XPath filter.[value],XML Reader with XPat 2           14          12          Filter                         Filter                         WHERE:(xsd_cast(XML Reader with XPath filter.[value],XML Reader with XPath filter.[lvalue],XML Reader with XPath filter.[tid],(18),(15)) IS NOT NULL AND xsd_cast(XML Reader with XPath filter.[value],XML Reader with XPath filter.[lvalue],XML Reader with XPa NULL                                                               3,904083      0             0,000376      9           110481,4         NULL                                                                                                             NULL     PLAN_ROW                                                         1        1100000
                                |--Table-valued function                                                                                                                                                                                                         2           15          14          Table-valued function          Table-valued function          NULL                                                                                                                                                                                                                                                             NULL                                                               20            0             1,004         4061        110440           XML Reader with XPath filter.[tid], XML Reader with XPath filter.[value], XML Reader with XPath filter.[lvalue]  NULL     PLAN_ROW                                                         1        1100000


StmtText                                                                                                                                                                                                                                                         StmtId      NodeId      Parent      PhysicalOp                     LogicalOp                      Argument                                                                                                                                                                                                                                                         DefinedValues                                                      EstimateRows  EstimateIO    EstimateCPU   AvgRowSize  TotalSubtreeCost OutputList                                                                                                       Warnings Type                                                             Parallel EstimateExecutions
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- ----------- ----------- ------------------------------ ------------------------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------ ------------- ------------- ------------- ----------- ---------------- ---------------------------------------------------------------------------------------------------------------- -------- ---------------------------------------------------------------- -------- ------------------
SELECT TOp 1000 * FROM ATestInsert

WHERE xml.exist('/root/groups/group/@id[. = 3]') = 1                                                                                                                                                                                  1           1           0           NULL                           NULL                           1                                                                                                                                                                                                                                                                NULL                                                               990000        NULL          NULL          NULL        110506,1         NULL                                                                                                             NULL     SELECT                                                           0        NULL
  |--Nested Loops(Left Semi Join, OUTER REFERENCES:([mlst].[dbo].[ATestInsert].[xml]))                                                                                                                                                                           1           2           1           Nested Loops                   Left Semi Join                 OUTER REFERENCES:([mlst].[dbo].[ATestInsert].[xml])                                                                                                                                                                                                              NULL                                                               990000        0             4,598         4039        110506,1         [mlst].[dbo].[ATestInsert].[Id], [mlst].[dbo].[ATestInsert].[xml]                                                NULL     PLAN_ROW                                                         0        1
       |--Clustered Index Scan(OBJECT:([mlst].[dbo].[ATestInsert].[PK_ATest]))                                                                                                                                                                                   1           3           2           Clustered Index Scan           Clustered Index Scan           OBJECT:([mlst].[dbo].[ATestInsert].[PK_ATest])                                                                                                                                                                                                                   [mlst].[dbo].[ATestInsert].[Id], [mlst].[dbo].[ATestInsert].[xml]  1100000       18,95201      1,210157      4039        20,16217         [mlst].[dbo].[ATestInsert].[Id], [mlst].[dbo].[ATestInsert].[xml]                                                NULL     PLAN_ROW                                                         0        1
       |--Filter(WHERE:(xsd_cast(XML Reader with XPath filter.[value],XML Reader with XPath filter.[lvalue],XML Reader with XPath filter.[tid],(18),(15)) IS NOT NULL AND xsd_cast(XML Reader with XPath filter.[value],XML Reader with XPath filter.[lvalue],XM 1           4           2           Filter                         Filter                         WHERE:(xsd_cast(XML Reader with XPath filter.[value],XML Reader with XPath filter.[lvalue],XML Reader with XPath filter.[tid],(18),(15)) IS NOT NULL AND xsd_cast(XML Reader with XPath filter.[value],XML Reader with XPath filter.[lvalue],XML Reader with XPa NULL                                                               3,904083      0             0,000376      9           110481,4         NULL                                                                                                             NULL     PLAN_ROW                                                         0        1100000
            |--Table-valued function                                                                                                                                                                                                                             1           5           4           Table-valued function          Table-valued function          NULL                                                                                                                                                                                                                                                             NULL                                                               20            0             1,004         4061        110440           XML Reader with XPath filter.[tid], XML Reader with XPath filter.[value], XML Reader with XPath filter.[lvalue]  NULL     PLAN_ROW                                                         0        1100000


Собственно вопрос в том, как ускорить выполнение 1-го запроса?
Пробовал наложить индекс на поле [xml], выигрыша в производительности не получил.
26 фев 10, 14:08    [8398365]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса с условием по XML-полю  [new]
Glory
Member

Откуда:
Сообщений: 104751
Не совсем понятно, почему вы сравниваете планы разных запросов ?
Выбрать 1000 записей и просканировать 100000 записей - это разные вещи
26 фев 10, 14:37    [8398681]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса с условием по XML-полю  [new]
Диман.
Member

Откуда:
Сообщений: 21
Ну я следовал мысли, что...
Гладя на планы, видно, что у обоих запросов, 100% выполнения занимает оператор [XML Reader with XPath filter].
Отсюда, я и задался вопросом, почему один и тот же оператор, в разных случаях, имеет разную производительность.
Причем, оценочные показатели выполнения оператора в этих двух случаях не отличаются.

Ещё я сравнивал с запросом без условия (производительность ~0.08 сек)
SELECT COUNT(*) FROM ATestInsert

Получал такой план
SELECT COUNT(*) FROM ATestInsert  1           1           0           NULL                           NULL                           1                                                            NULL                                                1             NULL          NULL          NULL        19,9156          NULL              NULL     SELECT                                                           0        NULL
  |--Compute Scalar(DEFINE:([Expr1003]=CONVERT_IMPLICIT(int,[globalagg1005],0)))                                                                                                                                              1           3           1           Compute Scalar                 Compute Scalar                 DEFINE:([Expr1003]=CONVERT_IMPLICIT(int,[globalagg1005],0))  [Expr1003]=CONVERT_IMPLICIT(int,[globalagg1005],0)  1             0             1E-07         11          19,9156          [Expr1003]        NULL     PLAN_ROW                                                         0        1
       |--Stream Aggregate(DEFINE:([globalagg1005]=SUM([partialagg1004])))                                                                                                                                                    1           4           3           Stream Aggregate               Aggregate                      NULL                                                         [globalagg1005]=SUM([partialagg1004])               1             0             1,7E-06       15          19,9156          [globalagg1005]   NULL     PLAN_ROW                                                         0        1
            |--Parallelism(Gather Streams)                                                                                                                                                                                    1           5           4           Parallelism                    Gather Streams                 NULL                                                         NULL                                                2             0             0,02850215    15          19,91559         [partialagg1004]  NULL     PLAN_ROW                                                         1        1
                 |--Stream Aggregate(DEFINE:([partialagg1004]=Count(*)))                                                                                                                                                      1           6           5           Stream Aggregate               Aggregate                      NULL                                                         [partialagg1004]=Count(*)                           2             0             0,3300005     15          19,88709         [partialagg1004]  NULL     PLAN_ROW                                                         1        1
                      |--Clustered Index Scan(OBJECT:([tdb].[dbo].[ATestInsert].[PK_ATest]))                                                                                                                                 1           7           6           Clustered Index Scan           Clustered Index Scan           OBJECT:([tdb].[dbo].[ATestInsert].[PK_ATest])               NULL                                                1100000       18,95201      0,6050785     9           19,55709         NULL              NULL     PLAN_ROW                                                         1        1

Не совсем понятно в какую сторону копать, чтобы увеличить производительность запроса
SELECT COUNT(*) FROM ATestInsert
WHERE xml.exist('/root/groups/group/@id[. = 3]') = 1
26 фев 10, 15:33    [8399285]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса с условием по XML-полю  [new]
Glory
Member

Откуда:
Сообщений: 104751
Диман.
Ну я следовал мысли, что...
Гладя на планы, видно, что у обоих запросов, 100% выполнения занимает оператор [XML Reader with XPath filter].
Отсюда, я и задался вопросом, почему один и тот же оператор, в разных случаях, имеет разную производительность.

Ну вам может и видны проценты какие-то, а нам нет
26 фев 10, 15:35    [8399308]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса с условием по XML-полю  [new]
Диман.
Member

Откуда:
Сообщений: 21
Glory
Ну вам может и видны проценты какие-то, а нам нет

Прошу прощения.

В аттаче план, сохраненный в XML.

Вот графическое представление
Картинка с другого сайта.

К сообщению приложен файл (exec_plan.sqlplan - 25Kb) cкачать
26 фев 10, 15:55    [8399520]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса с условием по XML-полю  [new]
Glory
Member

Откуда:
Сообщений: 104751
Сделайте вот так - https://www.sql.ru/faq/faq_topic.aspx?fid=393 для получения реальных планов выполнения в текстовом виде
26 фев 10, 16:13    [8399696]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса с условием по XML-полю  [new]
Диман.
Member

Откуда:
Сообщений: 21
Glory
Сделайте вот так - https://www.sql.ru/faq/faq_topic.aspx?fid=393 для получения реальных планов выполнения в текстовом виде


Ну в первом посте пналогично полученные планы
SET SHOWPLAN_ALL ON
За исключением того, что "Filter" обрезан был.

Повторяю:
StmtText
-------------------------------------------------------------------------------------

SELECT * FROM ATestInsert
WHERE xml.exist('/root/groups/group/@id[. = 3]') = 1

StmtText
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  |--Nested Loops(Left Semi Join, OUTER REFERENCES:([tdb].[dbo].[ATestInsert].[xml]))
       |--Clustered Index Scan(OBJECT:([tdb].[dbo].[ATestInsert].[PK_ATest]))
       |--Filter(WHERE:(xsd_cast(XML Reader with XPath filter.[value],XML Reader with XPath filter.[lvalue],XML Reader with XPath filter.[tid],(18),(15)) IS NOT NULL AND xsd_cast(XML Reader with XPath filter.[value],XML Reader with XPath filter.[lvalue],XML Reader with XPath filter.[tid],(18),(15))=3))
            |--Table-valued function


StmtText
----------------------------------------------------------------------------------------

SELECT COUNT(*) FROM ATestInsert
WHERE xml.exist('/root/groups/group/@id[. = 3]') = 1


StmtText
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  |--Compute Scalar(DEFINE:([Expr1009]=CONVERT_IMPLICIT(int,[globalagg1012],0)))
       |--Stream Aggregate(DEFINE:([globalagg1012]=SUM([partialagg1011])))
            |--Parallelism(Gather Streams)
                 |--Stream Aggregate(DEFINE:([partialagg1011]=Count(*)))
                      |--Nested Loops(Left Semi Join, OUTER REFERENCES:([tdb].[dbo].[ATestInsert].[xml]))
                           |--Clustered Index Scan(OBJECT:([tdb].[dbo].[ATestInsert].[PK_ATest]))
                           |--Filter(WHERE:(xsd_cast(XML Reader with XPath filter.[value],XML Reader with XPath filter.[lvalue],XML Reader with XPath filter.[tid],(18),(15)) IS NOT NULL AND xsd_cast(XML Reader with XPath filter.[value],XML Reader with XPath filter.[lvalue],XML Reader with XPath filter.[tid],(18),(15))=3))
                                |--Table-valued function


26 фев 10, 16:26    [8399845]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса с условием по XML-полю  [new]
Glory
Member

Откуда:
Сообщений: 104751
Диман.
Glory
Сделайте вот так - https://www.sql.ru/faq/faq_topic.aspx?fid=393 для получения реальных планов выполнения в текстовом виде


Ну в первом посте пналогично полученные планы

Потому что просили " для получения реальных планов", а не предварительных
26 фев 10, 16:31    [8399897]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса с условием по XML-полю  [new]
Диман.
Member

Откуда:
Сообщений: 21
Glory

Потому что просили " для получения реальных планов", а не предварительных


Rows                 Executes             StmtText                                                                                                                                                                                                                                                         StmtId      NodeId      Parent      PhysicalOp                     LogicalOp                      Argument                                                                                                                                                                                                                                                         DefinedValues                                                      EstimateRows  EstimateIO    EstimateCPU   AvgRowSize  TotalSubtreeCost OutputList                                                                                                       Warnings Type                                                             Parallel EstimateExecutions
-------------------- -------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- ----------- ----------- ------------------------------ ------------------------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------ ------------- ------------- ------------- ----------- ---------------- ---------------------------------------------------------------------------------------------------------------- -------- ---------------------------------------------------------------- -------- ------------------
716535               1                    SELECT * FROM ATestInsert WHERE xml.exist('/root/groups/group/@id[. = 3]') = 1                                                                                                                                                                                  1           1           0           NULL                           NULL                           NULL                                                                                                                                                                                                                                                             NULL                                                               990000        NULL          NULL          NULL        110506,1         NULL                                                                                                             NULL     SELECT                                                           0        NULL
716535               1                      |--Nested Loops(Left Semi Join, OUTER REFERENCES:([tdb].[dbo].[ATestInsert].[xml]))                                                                                                                                                                           1           2           1           Nested Loops                   Left Semi Join                 OUTER REFERENCES:([tdb].[dbo].[ATestInsert].[xml])                                                                                                                                                                                                              NULL                                                               990000        0             4,598         4039        110506,1         [tdb].[dbo].[ATestInsert].[Id], [tdb].[dbo].[ATestInsert].[xml]                                                NULL     PLAN_ROW                                                         0        1
1100000              1                           |--Clustered Index Scan(OBJECT:([tdb].[dbo].[ATestInsert].[PK_ATest]))                                                                                                                                                                                   1           3           2           Clustered Index Scan           Clustered Index Scan           OBJECT:([tdb].[dbo].[ATestInsert].[PK_ATest])                                                                                                                                                                                                                   [tdb].[dbo].[ATestInsert].[Id], [tdb].[dbo].[ATestInsert].[xml]  1100000       18,95201      1,210157      4039        20,16217         [tdb].[dbo].[ATestInsert].[Id], [tdb].[dbo].[ATestInsert].[xml]                                                NULL     PLAN_ROW                                                         0        1
716535               1100000                     |--Filter(WHERE:(xsd_cast(XML Reader with XPath filter.[value],XML Reader with XPath filter.[lvalue],XML Reader with XPath filter.[tid],(18),(15)) IS NOT NULL AND xsd_cast(XML Reader with XPath filter.[value],XML Reader with XPath filter.[lvalue],XM 1           4           2           Filter                         Filter                         WHERE:(xsd_cast(XML Reader with XPath filter.[value],XML Reader with XPath filter.[lvalue],XML Reader with XPath filter.[tid],(18),(15)) IS NOT NULL AND xsd_cast(XML Reader with XPath filter.[value],XML Reader with XPath filter.[lvalue],XML Reader with XPa NULL                                                               3,904083      0             0,000376      9           110481,4         NULL                                                                                                             NULL     PLAN_ROW                                                         0        1100000
7163399              1100000                          |--Table-valued function                                                                                                                                                                                                                             1           5           4           Table-valued function          Table-valued function          NULL                                                                                                                                                                                                                                                             NULL                                                               20            0             1,004         4061        110440           XML Reader with XPath filter.[tid], XML Reader with XPath filter.[value], XML Reader with XPath filter.[lvalue]  NULL     PLAN_ROW                                                         0        1100000

Rows                 Executes             StmtText                                                                                                                                                                                                                                                         StmtId      NodeId      Parent      PhysicalOp                     LogicalOp                      Argument                                                                                                                                                                                                                                                         DefinedValues                                       EstimateRows  EstimateIO    EstimateCPU   AvgRowSize  TotalSubtreeCost OutputList                                                                                                       Warnings Type                                                             Parallel EstimateExecutions
-------------------- -------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- ----------- ----------- ------------------------------ ------------------------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------- ------------- ------------- ------------- ----------- ---------------- ---------------------------------------------------------------------------------------------------------------- -------- ---------------------------------------------------------------- -------- ------------------
1                    1                    SELECT COUNT(*) FROM ATestInsert WHERE xml.exist('/root/groups/group/@id[. = 3]') = 1                                                                                                                                                                           3           1           0           NULL                           NULL                           NULL                                                                                                                                                                                                                                                             NULL                                                1             NULL          NULL          NULL        110503,6         NULL                                                                                                             NULL     SELECT                                                           0        NULL
0                    0                      |--Compute Scalar(DEFINE:([Expr1009]=CONVERT_IMPLICIT(int,[globalagg1012],0)))                                                                                                                                                                                 3           3           1           Compute Scalar                 Compute Scalar                 DEFINE:([Expr1009]=CONVERT_IMPLICIT(int,[globalagg1012],0))                                                                                                                                                                                                      [Expr1009]=CONVERT_IMPLICIT(int,[globalagg1012],0)  1             0             1E-07         11          110503,6         [Expr1009]                                                                                                       NULL     PLAN_ROW                                                         0        1
1                    1                           |--Stream Aggregate(DEFINE:([globalagg1012]=SUM([partialagg1011])))                                                                                                                                                                                       3           4           3           Stream Aggregate               Aggregate                      NULL                                                                                                                                                                                                                                                             [globalagg1012]=SUM([partialagg1011])               1             0             1,7E-06       15          110503,6         [globalagg1012]                                                                                                  NULL     PLAN_ROW                                                         0        1
4                    1                                |--Parallelism(Gather Streams)                                                                                                                                                                                                                       3           5           4           Parallelism                    Gather Streams                 NULL                                                                                                                                                                                                                                                             NULL                                                2             0             0,02850215    15          110503,6         [partialagg1011]                                                                                                 NULL     PLAN_ROW                                                         1        1
4                    4                                     |--Stream Aggregate(DEFINE:([partialagg1011]=Count(*)))                                                                                                                                                                                         3           6           5           Stream Aggregate               Aggregate                      NULL                                                                                                                                                                                                                                                             [partialagg1011]=Count(*)                           2             0             0,2970005     15          110503,5         [partialagg1011]                                                                                                 NULL     PLAN_ROW                                                         1        1
716535               4                                          |--Nested Loops(Left Semi Join, OUTER REFERENCES:([tdb].[dbo].[ATestInsert].[xml]))                                                                                                                                                       3           7           6           Nested Loops                   Left Semi Join                 OUTER REFERENCES:([tdb].[dbo].[ATestInsert].[xml])                                                                                                                                                                                                              NULL                                                990000        0             2,299         9           110503,2         NULL                                                                                                             NULL     PLAN_ROW                                                         1        1
1100000              4                                               |--Clustered Index Scan(OBJECT:([tdb].[dbo].[ATestInsert].[PK_ATest]))                                                                                                                                                               3           8           7           Clustered Index Scan           Clustered Index Scan           OBJECT:([tdb].[dbo].[ATestInsert].[PK_ATest])                                                                                                                                                                                                                   [tdb].[dbo].[ATestInsert].[xml]                    1100000       18,95201      0,6050785     4035        19,55709         [tdb].[dbo].[ATestInsert].[xml]                                                                                 NULL     PLAN_ROW                                                         1        1
716535               1100000                                         |--Filter(WHERE:(xsd_cast(XML Reader with XPath filter.[value],XML Reader with XPath filter.[lvalue],XML Reader with XPath filter.[tid],(18),(15)) IS NOT NULL AND xsd_cast(XML Reader with XPath filter.[value],XML Reader with XPat 3           9           7           Filter                         Filter                         WHERE:(xsd_cast(XML Reader with XPath filter.[value],XML Reader with XPath filter.[lvalue],XML Reader with XPath filter.[tid],(18),(15)) IS NOT NULL AND xsd_cast(XML Reader with XPath filter.[value],XML Reader with XPath filter.[lvalue],XML Reader with XPa NULL                                                3,904083      0             0,000376      9           110481,4         NULL                                                                                                             NULL     PLAN_ROW                                                         1        1100000
7163399              1100000                                              |--Table-valued function                                                                                                                                                                                                         3           10          9           Table-valued function          Table-valued function          NULL                                                                                                                                                                                                                                                             NULL                                                20            0             1,004         4061        110440           XML Reader with XPath filter.[tid], XML Reader with XPath filter.[value], XML Reader with XPath filter.[lvalue]  NULL     PLAN_ROW                                                         1        1100000

26 фев 10, 16:46    [8400055]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса с условием по XML-полю  [new]
Glory
Member

Откуда:
Сообщений: 104751
Пробуем
SELECT COUNT(*) FROM ATestInsert WHERE xml.exist('/root/groups/group/@id[. = 3]') = 1 OPTION (MAXDOP 0);
26 фев 10, 17:28    [8400387]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса с условием по XML-полю  [new]
Диман.
Member

Откуда:
Сообщений: 21
Glory
Пробуем
SELECT COUNT(*) FROM ATestInsert WHERE xml.exist('/root/groups/group/@id[. = 3]') = 1 OPTION (MAXDOP 0);


К сожалению, в производительности без выигрыша. Время выполнения ~20сек

Вот план:
Rows                 Executes             StmtText                                                                                                                                                                                                                                                         StmtId      NodeId      Parent      PhysicalOp                     LogicalOp                      Argument                                                                                                                                                                                                                                                         DefinedValues                                       EstimateRows  EstimateIO    EstimateCPU   AvgRowSize  TotalSubtreeCost OutputList                                                                                                       Warnings Type                                                             Parallel EstimateExecutions
-------------------- -------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- ----------- ----------- ------------------------------ ------------------------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------- ------------- ------------- ------------- ----------- ---------------- ---------------------------------------------------------------------------------------------------------------- -------- ---------------------------------------------------------------- -------- ------------------
1                    1                    SELECT COUNT(*) FROM ATestInsert WHERE xml.exist('/root/groups/group/@id[. = 3]') = 1 OPTION (MAXDOP 0);                                                                                                                                                         1           1           0           NULL                           NULL                           NULL                                                                                                                                                                                                                                                             NULL                                                1             NULL          NULL          NULL        110503,6         NULL                                                                                                             NULL     SELECT                                                           0        NULL
0                    0                      |--Compute Scalar(DEFINE:([Expr1009]=CONVERT_IMPLICIT(int,[globalagg1012],0)))                                                                                                                                                                                 1           3           1           Compute Scalar                 Compute Scalar                 DEFINE:([Expr1009]=CONVERT_IMPLICIT(int,[globalagg1012],0))                                                                                                                                                                                                      [Expr1009]=CONVERT_IMPLICIT(int,[globalagg1012],0)  1             0             1E-07         11          110503,6         [Expr1009]                                                                                                       NULL     PLAN_ROW                                                         0        1
1                    1                           |--Stream Aggregate(DEFINE:([globalagg1012]=SUM([partialagg1011])))                                                                                                                                                                                       1           4           3           Stream Aggregate               Aggregate                      NULL                                                                                                                                                                                                                                                             [globalagg1012]=SUM([partialagg1011])               1             0             1,7E-06       15          110503,6         [globalagg1012]                                                                                                  NULL     PLAN_ROW                                                         0        1
4                    1                                |--Parallelism(Gather Streams)                                                                                                                                                                                                                       1           5           4           Parallelism                    Gather Streams                 NULL                                                                                                                                                                                                                                                             NULL                                                2             0             0,02850215    15          110503,6         [partialagg1011]                                                                                                 NULL     PLAN_ROW                                                         1        1
4                    4                                     |--Stream Aggregate(DEFINE:([partialagg1011]=Count(*)))                                                                                                                                                                                         1           6           5           Stream Aggregate               Aggregate                      NULL                                                                                                                                                                                                                                                             [partialagg1011]=Count(*)                           2             0             0,2970005     15          110503,5         [partialagg1011]                                                                                                 NULL     PLAN_ROW                                                         1        1
716535               4                                          |--Nested Loops(Left Semi Join, OUTER REFERENCES:([mlst].[dbo].[ATestInsert].[xml]))                                                                                                                                                       1           7           6           Nested Loops                   Left Semi Join                 OUTER REFERENCES:([mlst].[dbo].[ATestInsert].[xml])                                                                                                                                                                                                              NULL                                                990000        0             2,299         9           110503,2         NULL                                                                                                             NULL     PLAN_ROW                                                         1        1
1100000              4                                               |--Clustered Index Scan(OBJECT:([mlst].[dbo].[ATestInsert].[PK_ATest]))                                                                                                                                                               1           8           7           Clustered Index Scan           Clustered Index Scan           OBJECT:([mlst].[dbo].[ATestInsert].[PK_ATest])                                                                                                                                                                                                                   [mlst].[dbo].[ATestInsert].[xml]                    1100000       18,95201      0,6050785     4035        19,55709         [mlst].[dbo].[ATestInsert].[xml]                                                                                 NULL     PLAN_ROW                                                         1        1
716535               1100000                                         |--Filter(WHERE:(xsd_cast(XML Reader with XPath filter.[value],XML Reader with XPath filter.[lvalue],XML Reader with XPath filter.[tid],(18),(15)) IS NOT NULL AND xsd_cast(XML Reader with XPath filter.[value],XML Reader with XPat 1           9           7           Filter                         Filter                         WHERE:(xsd_cast(XML Reader with XPath filter.[value],XML Reader with XPath filter.[lvalue],XML Reader with XPath filter.[tid],(18),(15)) IS NOT NULL AND xsd_cast(XML Reader with XPath filter.[value],XML Reader with XPath filter.[lvalue],XML Reader with XPa NULL                                                3,904083      0             0,000376      9           110481,4         NULL                                                                                                             NULL     PLAN_ROW                                                         1        1100000
7163399              1100000                                              |--Table-valued function                                                                                                                                                                                                         1           10          9           Table-valued function          Table-valued function          NULL                                                                                                                                                                                                                                                             NULL                                                20            0             1,004         4061        110440           XML Reader with XPath filter.[tid], XML Reader with XPath filter.[value], XML Reader with XPath filter.[lvalue]  NULL     PLAN_ROW                                                         1        1100000

26 фев 10, 17:37    [8400449]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса с условием по XML-полю  [new]
Glory
Member

Откуда:
Сообщений: 104751
Извиняюсь, 1 а не 0

SELECT COUNT(*) FROM ATestInsert WHERE xml.exist('/root/groups/group/@id[. = 3]') = 1 OPTION (MAXDOP 1);
26 фев 10, 17:39    [8400454]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса с условием по XML-полю  [new]
Диман.
Member

Откуда:
Сообщений: 21
Glory
Извиняюсь, 1 а не 0

SELECT COUNT(*) FROM ATestInsert WHERE xml.exist('/root/groups/group/@id[. = 3]') = 1 OPTION (MAXDOP 1);


Так вермя выполнения увеличилось до 60 сек.
26 фев 10, 17:49    [8400510]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса с условием по XML-полю  [new]
Glory
Member

Откуда:
Сообщений: 104751
Диман.
Glory
Извиняюсь, 1 а не 0

SELECT COUNT(*) FROM ATestInsert WHERE xml.exist('/root/groups/group/@id[. = 3]') = 1 OPTION (MAXDOP 1);


Так вермя выполнения увеличилось до 60 сек.

А план поменялся ?
26 фев 10, 17:49    [8400514]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса с условием по XML-полю  [new]
Диман.
Member

Откуда:
Сообщений: 21
Glory

А план поменялся ?


Поменялся.

0                    0                      |--Compute Scalar(DEFINE:([Expr1009]=CONVERT_IMPLICIT(int,[Expr1013],0)))                                                                                                                                                                                      1           2           1           Compute Scalar                 Compute Scalar                 DEFINE:([Expr1009]=CONVERT_IMPLICIT(int,[Expr1013],0))                                                                                                                                                                                                           [Expr1009]=CONVERT_IMPLICIT(int,[Expr1013],0)  1             0             0,5940005     11          110506,7         [Expr1009]                                                                                                       NULL     PLAN_ROW                                                         0        1
1                    1                           |--Stream Aggregate(DEFINE:([Expr1013]=Count(*)))                                                                                                                                                                                                         1           3           2           Stream Aggregate               Aggregate                      NULL                                                                                                                                                                                                                                                             [Expr1013]=Count(*)                            1             0             0,5940005     11          110506,7         [Expr1013]                                                                                                       NULL     PLAN_ROW                                                         0        1
716535               1                                |--Nested Loops(Left Semi Join, OUTER REFERENCES:([tdb].[dbo].[ATestInsert].[xml]))                                                                                                                                                                 1           4           3           Nested Loops                   Left Semi Join                 OUTER REFERENCES:([tdb].[dbo].[ATestInsert].[xml])                                                                                                                                                                                                              NULL                                           990000        0             4,598         9           110506,1         NULL                                                                                                             NULL     PLAN_ROW                                                         0        1
1100000              1                                     |--Clustered Index Scan(OBJECT:([tdb].[dbo].[ATestInsert].[PK_ATest]))                                                                                                                                                                         1           5           4           Clustered Index Scan           Clustered Index Scan           OBJECT:([tdb].[dbo].[ATestInsert].[PK_ATest])                                                                                                                                                                                                                   [tdb].[dbo].[ATestInsert].[xml]               1100000       18,95201      1,210157      4035        20,16217         [tdb].[dbo].[ATestInsert].[xml]                                                                                 NULL     PLAN_ROW                                                         0        1
716535               1100000                               |--Filter(WHERE:(xsd_cast(XML Reader with XPath filter.[value],XML Reader with XPath filter.[lvalue],XML Reader with XPath filter.[tid],(18),(15)) IS NOT NULL AND xsd_cast(XML Reader with XPath filter.[value],XML Reader with XPath filter.[ 1           6           4           Filter                         Filter                         WHERE:(xsd_cast(XML Reader with XPath filter.[value],XML Reader with XPath filter.[lvalue],XML Reader with XPath filter.[tid],(18),(15)) IS NOT NULL AND xsd_cast(XML Reader with XPath filter.[value],XML Reader with XPath filter.[lvalue],XML Reader with XPa NULL                                           3,904083      0             0,000376      9           110481,4         NULL                                                                                                             NULL     PLAN_ROW                                                         0        1100000
7163399              1100000                                    |--Table-valued function                                                                                                                                                                                                                   1           7           6           Table-valued function          Table-valued function          NULL                                                                                                                                                                                                                                                             NULL                                           20            0             1,004         4061        110440           XML Reader with XPath filter.[tid], XML Reader with XPath filter.[value], XML Reader with XPath filter.[lvalue]  NULL     PLAN_ROW                                                         0        1100000

26 фев 10, 18:00    [8400580]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса с условием по XML-полю  [new]
Glory
Member

Откуда:
Сообщений: 104751
Диман.
Glory

А план поменялся ?


Поменялся.

0                    0                      |--Compute Scalar(DEFINE:([Expr1009]=CONVERT_IMPLICIT(int,[Expr1013],0)))                                                                                                                                                                                      1           2           1           Compute Scalar                 Compute Scalar                 DEFINE:([Expr1009]=CONVERT_IMPLICIT(int,[Expr1013],0))                                                                                                                                                                                                           [Expr1009]=CONVERT_IMPLICIT(int,[Expr1013],0)  1             0             0,5940005     11          110506,7         [Expr1009]                                                                                                       NULL     PLAN_ROW                                                         0        1
1                    1                           |--Stream Aggregate(DEFINE:([Expr1013]=Count(*)))                                                                                                                                                                                                         1           3           2           Stream Aggregate               Aggregate                      NULL                                                                                                                                                                                                                                                             [Expr1013]=Count(*)                            1             0             0,5940005     11          110506,7         [Expr1013]                                                                                                       NULL     PLAN_ROW                                                         0        1
716535               1                                |--Nested Loops(Left Semi Join, OUTER REFERENCES:([tdb].[dbo].[ATestInsert].[xml]))                                                                                                                                                                 1           4           3           Nested Loops                   Left Semi Join                 OUTER REFERENCES:([tdb].[dbo].[ATestInsert].[xml])                                                                                                                                                                                                              NULL                                           990000        0             4,598         9           110506,1         NULL                                                                                                             NULL     PLAN_ROW                                                         0        1
1100000              1                                     |--Clustered Index Scan(OBJECT:([tdb].[dbo].[ATestInsert].[PK_ATest]))                                                                                                                                                                         1           5           4           Clustered Index Scan           Clustered Index Scan           OBJECT:([tdb].[dbo].[ATestInsert].[PK_ATest])                                                                                                                                                                                                                   [tdb].[dbo].[ATestInsert].[xml]               1100000       18,95201      1,210157      4035        20,16217         [tdb].[dbo].[ATestInsert].[xml]                                                                                 NULL     PLAN_ROW                                                         0        1
716535               1100000                               |--Filter(WHERE:(xsd_cast(XML Reader with XPath filter.[value],XML Reader with XPath filter.[lvalue],XML Reader with XPath filter.[tid],(18),(15)) IS NOT NULL AND xsd_cast(XML Reader with XPath filter.[value],XML Reader with XPath filter.[ 1           6           4           Filter                         Filter                         WHERE:(xsd_cast(XML Reader with XPath filter.[value],XML Reader with XPath filter.[lvalue],XML Reader with XPath filter.[tid],(18),(15)) IS NOT NULL AND xsd_cast(XML Reader with XPath filter.[value],XML Reader with XPath filter.[lvalue],XML Reader with XPa NULL                                           3,904083      0             0,000376      9           110481,4         NULL                                                                                                             NULL     PLAN_ROW                                                         0        1100000
7163399              1100000                                    |--Table-valued function                                                                                                                                                                                                                   1           7           6           Table-valued function          Table-valued function          NULL                                                                                                                                                                                                                                                             NULL                                           20            0             1,004         4061        110440           XML Reader with XPath filter.[tid], XML Reader with XPath filter.[value], XML Reader with XPath filter.[lvalue]  NULL     PLAN_ROW                                                         0        1100000


Ну так он же теперь совпадает с планом
SELECT * FROM ATestInsert
WHERE xml.exist('/root/groups/group/@id[. = 3]') = 1
???
26 фев 10, 18:04    [8400605]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса с условием по XML-полю  [new]
Диман.
Member

Откуда:
Сообщений: 21
Glory

Ну так он же теперь совпадает с планом
SELECT * FROM ATestInsert WHERE xml.exist('/root/groups/group/@id[. = 3]') = 1 ???


Да нет. разные они, планы.

Rows                 Executes             StmtText                                                                                                                                                                                                                                                         StmtId      NodeId      Parent      PhysicalOp                     LogicalOp                      Argument                                                                                                                                                                                                                                                         DefinedValues                                  EstimateRows  EstimateIO    EstimateCPU   AvgRowSize  TotalSubtreeCost OutputList                                                                                                       Warnings Type                                                             Parallel EstimateExecutions
-------------------- -------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- ----------- ----------- ------------------------------ ------------------------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------- ------------- ------------- ------------- ----------- ---------------- ---------------------------------------------------------------------------------------------------------------- -------- ---------------------------------------------------------------- -------- ------------------
1                    1                    SELECT COUNT(*) FROM ATestInsert WHERE xml.exist('/root/groups/group/@id[. = 3]') = 1 OPTION (MAXDOP 1);                                                                                                                                                         1           1           0           NULL                           NULL                           NULL                                                                                                                                                                                                                                                             NULL                                           1             NULL          NULL          NULL        110506,7         NULL                                                                                                             NULL     SELECT                                                           0        NULL
0                    0                      |--Compute Scalar(DEFINE:([Expr1009]=CONVERT_IMPLICIT(int,[Expr1013],0)))                                                                                                                                                                                      1           2           1           Compute Scalar                 Compute Scalar                 DEFINE:([Expr1009]=CONVERT_IMPLICIT(int,[Expr1013],0))                                                                                                                                                                                                           [Expr1009]=CONVERT_IMPLICIT(int,[Expr1013],0)  1             0             0,5940005     11          110506,7         [Expr1009]                                                                                                       NULL     PLAN_ROW                                                         0        1
1                    1                           |--Stream Aggregate(DEFINE:([Expr1013]=Count(*)))                                                                                                                                                                                                         1           3           2           Stream Aggregate               Aggregate                      NULL                                                                                                                                                                                                                                                             [Expr1013]=Count(*)                            1             0             0,5940005     11          110506,7         [Expr1013]                                                                                                       NULL     PLAN_ROW                                                         0        1
716535               1                                |--Nested Loops(Left Semi Join, OUTER REFERENCES:([tdb].[dbo].[ATestInsert].[xml]))                                                                                                                                                                 1           4           3           Nested Loops                   Left Semi Join                 OUTER REFERENCES:([tdb].[dbo].[ATestInsert].[xml])                                                                                                                                                                                                              NULL                                           990000        0             4,598         9           110506,1         NULL                                                                                                             NULL     PLAN_ROW                                                         0        1
1100000              1                                     |--Clustered Index Scan(OBJECT:([tdb].[dbo].[ATestInsert].[PK_ATest]))                                                                                                                                                                         1           5           4           Clustered Index Scan           Clustered Index Scan           OBJECT:([tdb].[dbo].[ATestInsert].[PK_ATest])                                                                                                                                                                                                                   [tdb].[dbo].[ATestInsert].[xml]               1100000       18,95201      1,210157      4035        20,16217         [tdb].[dbo].[ATestInsert].[xml]                                                                                 NULL     PLAN_ROW                                                         0        1
716535               1100000                               |--Filter(WHERE:(xsd_cast(XML Reader with XPath filter.[value],XML Reader with XPath filter.[lvalue],XML Reader with XPath filter.[tid],(18),(15)) IS NOT NULL AND xsd_cast(XML Reader with XPath filter.[value],XML Reader with XPath filter.[ 1           6           4           Filter                         Filter                         WHERE:(xsd_cast(XML Reader with XPath filter.[value],XML Reader with XPath filter.[lvalue],XML Reader with XPath filter.[tid],(18),(15)) IS NOT NULL AND xsd_cast(XML Reader with XPath filter.[value],XML Reader with XPath filter.[lvalue],XML Reader with XPa NULL                                           3,904083      0             0,000376      9           110481,4         NULL                                                                                                             NULL     PLAN_ROW                                                         0        1100000
7163399              1100000                                    |--Table-valued function                                                                                                                                                                                                                   1           7           6           Table-valued function          Table-valued function          NULL                                                                                                                                                                                                                                                             NULL                                           20            0             1,004         4061        110440           XML Reader with XPath filter.[tid], XML Reader with XPath filter.[value], XML Reader with XPath filter.[lvalue]  NULL     PLAN_ROW                                                         0        1100000


Rows                 Executes             StmtText                                                                                                                                                                                                                                                         StmtId      NodeId      Parent      PhysicalOp                     LogicalOp                      Argument                                                                                                                                                                                                                                                         DefinedValues                                                      EstimateRows  EstimateIO    EstimateCPU   AvgRowSize  TotalSubtreeCost OutputList                                                                                                       Warnings Type                                                             Parallel EstimateExecutions
-------------------- -------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- ----------- ----------- ------------------------------ ------------------------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------ ------------- ------------- ------------- ----------- ---------------- ---------------------------------------------------------------------------------------------------------------- -------- ---------------------------------------------------------------- -------- ------------------
716535               1                    SELECT * FROM ATestInsert WHERE xml.exist('/root/groups/group/@id[. = 3]') = 1
716535               1                      |--Nested Loops(Left Semi Join, OUTER REFERENCES:([tdb].[dbo].[ATestInsert].[xml]))                                                                                                                                                                           3           2           1           Nested Loops                   Left Semi Join                 OUTER REFERENCES:([tdb].[dbo].[ATestInsert].[xml])                                                                                                                                                                                                              NULL                                                               990000        0             4,598         4039        110506,1         [tdb].[dbo].[ATestInsert].[Id], [tdb].[dbo].[ATestInsert].[xml]                                                NULL     PLAN_ROW                                                         0        1
1100000              1                           |--Clustered Index Scan(OBJECT:([tdb].[dbo].[ATestInsert].[PK_ATest]))                                                                                                                                                                                   3           3           2           Clustered Index Scan           Clustered Index Scan           OBJECT:([tdb].[dbo].[ATestInsert].[PK_ATest])                                                                                                                                                                                                                   [tdb].[dbo].[ATestInsert].[Id], [tdb].[dbo].[ATestInsert].[xml]  1100000       18,95201      1,210157      4039        20,16217         [tdb].[dbo].[ATestInsert].[Id], [tdb].[dbo].[ATestInsert].[xml]                                                NULL     PLAN_ROW                                                         0        1
716535               1100000                     |--Filter(WHERE:(xsd_cast(XML Reader with XPath filter.[value],XML Reader with XPath filter.[lvalue],XML Reader with XPath filter.[tid],(18),(15)) IS NOT NULL AND xsd_cast(XML Reader with XPath filter.[value],XML Reader with XPath filter.[lvalue],XM 3           4           2           Filter                         Filter                         WHERE:(xsd_cast(XML Reader with XPath filter.[value],XML Reader with XPath filter.[lvalue],XML Reader with XPath filter.[tid],(18),(15)) IS NOT NULL AND xsd_cast(XML Reader with XPath filter.[value],XML Reader with XPath filter.[lvalue],XML Reader with XPa NULL                                                               3,904083      0             0,000376      9           110481,4         NULL                                                                                                             NULL     PLAN_ROW                                                         0        1100000
7163399              1100000                          |--Table-valued function                                                                                                                                                                                                                             3           5           4           Table-valued function          Table-valued function          NULL                                                                                                                                                                                                                                                             NULL                                                               20            0             1,004         4061        110440           XML Reader with XPath filter.[tid], XML Reader with XPath filter.[value], XML Reader with XPath filter.[lvalue]  NULL     PLAN_ROW                                                         0        1100000

26 фев 10, 18:15    [8400698]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса с условием по XML-полю  [new]
Гуест гуестович
Guest
А если попробовать так, то какой будет план?

SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON

GO
CREATE PRIMARY XML INDEX XML_IX_ATestInsert ON [dbo].[ATestInsert]([xml])

GO

SELECT COUNT(*) FROM ATestInsert WHERE xml.exist('/root/groups/group[@id = "3"]') = 1

26 фев 10, 23:46    [8401723]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса с условием по XML-полю  [new]
Диман.
Member

Откуда:
Сообщений: 21
Гуест гуестович
А если попробовать так, то какой будет план?
SET ...


1                    1                    SELECT COUNT(*) FROM ATestInsert WHERE xml.exist('/root/groups/group[@id = "3"]') = 1                                                                                                                                                                            1           1           0           NULL                           NULL                           NULL                                                                                                                                                                                                                                                             NULL                                                                                                   1             NULL          NULL          NULL        393,3961         NULL                                                     NULL     SELECT                                                           0        NULL
0                    0                      |--Compute Scalar(DEFINE:([Expr1012]=CONVERT_IMPLICIT(int,[Expr1017],0)))                                                                                                                                                                                      1           2           1           Compute Scalar                 Compute Scalar                 DEFINE:([Expr1012]=CONVERT_IMPLICIT(int,[Expr1017],0))                                                                                                                                                                                                           [Expr1012]=CONVERT_IMPLICIT(int,[Expr1017],0)                                                          1             0             0,01219693    11          393,3961         [Expr1012]                                               NULL     PLAN_ROW                                                         0        1
1                    1                           |--Stream Aggregate(DEFINE:([Expr1017]=Count(*)))                                                                                                                                                                                                         1           3           2           Stream Aggregate               Aggregate                      NULL                                                                                                                                                                                                                                                             [Expr1017]=Count(*)                                                                                    1             0             0,01219693    11          393,3961         [Expr1017]                                               NULL     PLAN_ROW                                                         0        1
716535               1                                |--Merge Join(Inner Join, MERGE:([tdb].[dbo].[ATestInsert].[Id])=([id:2].[pk1]), RESIDUAL:([tdb].[dbo].[ATestInsert].[Id]=[tdb].[sys].[xml_index_nodes_279672044_256000].[pk1] as [id:2].[pk1]))                                                  1           4           3           Merge Join                     Inner Join                     MERGE:([tdb].[dbo].[ATestInsert].[Id])=([id:2].[pk1]), RESIDUAL:([tdb].[dbo].[ATestInsert].[Id]=[tdb].[sys].[xml_index_nodes_279672044_256000].[pk1] as [id:2].[pk1])                                                                                         NULL                                                                                                   20327,38      0             2,36032       9           393,3839         NULL                                                     NULL     PLAN_ROW                                                         0        1
1100000              1                                     |--Clustered Index Scan(OBJECT:([tdb].[dbo].[ATestInsert].[PK_ATest]), ORDERED FORWARD)                                                                                                                                                        1           5           4           Clustered Index Scan           Clustered Index Scan           OBJECT:([tdb].[dbo].[ATestInsert].[PK_ATest]), ORDERED FORWARD                                                                                                                                                                                                  [tdb].[dbo].[ATestInsert].[Id]                                                                        1100000       18,95201      1,210157      11          20,16217         [tdb].[dbo].[ATestInsert].[Id]                          NULL     PLAN_ROW                                                         0        1
716535               1                                     |--Stream Aggregate(GROUP BY:([id:2].[pk1]))                                                                                                                                                                                                    1           6           4           Stream Aggregate               Aggregate                      GROUP BY:([id:2].[pk1])                                                                                                                                                                                                                                          NULL                                                                                                   20327,38      0             0,04757034    11          370,8614         [id:2].[pk1]                                             NULL     PLAN_ROW                                                         0        1
1100460              1                                          |--Merge Join(Inner Join, MANY-TO-MANY MERGE:([group:1].[pk1])=([id:2].[pk1]), RESIDUAL:([tdb].[sys].[xml_index_nodes_279672044_256000].[pk1] as [group:1].[pk1]=[tdb].[sys].[xml_index_nodes_279672044_256000].[pk1] as [id:2].[pk1] AN 1           7           6           Merge Join                     Inner Join                     MANY-TO-MANY MERGE:([group:1].[pk1])=([id:2].[pk1]), RESIDUAL:([tdb].[sys].[xml_index_nodes_279672044_256000].[pk1] as [group:1].[pk1]=[tdb].[sys].[xml_index_nodes_279672044_256000].[pk1] as [id:2].[pk1] AND [tdb].[sys].[xml_index_nodes_279672044_256000 NULL                                                                                                   74813,3       1,899597      26,98088      15          370,8138         [group:1].[pk1], [id:2].[pk1]                            NULL     PLAN_ROW                                                         0        1
0                    0                                               |--Compute Scalar(DEFINE:([Expr1014]=getdescendantlimit([tdb].[sys].[xml_index_nodes_279672044_256000].[id] as [group:1].[id])))                                                                                                     1           8           7           Compute Scalar                 Compute Scalar                 DEFINE:([Expr1014]=getdescendantlimit([tdb].[sys].[xml_index_nodes_279672044_256000].[id] as [group:1].[id]))                                                                                                                                                   [Expr1014]=getdescendantlimit([tdb].[sys].[xml_index_nodes_279672044_256000].[id] as [group:1].[id])  1,099888E+07  0             1,099888      472         166,4327         [group:1].[id], [group:1].[pk1], [Expr1014]              NULL     PLAN_ROW                                                         0        1
11000000             1                                               |    |--Clustered Index Scan(OBJECT:([tdb].[sys].[xml_index_nodes_279672044_256000].[XML_IX_ATestInsert] AS [group:1]),  WHERE:([tdb].[sys].[xml_index_nodes_279672044_256000].[hid] as [group:1].[hid]='€Á€À€') ORDERED FORWARD)  1           9           8           Clustered Index Scan           Clustered Index Scan           OBJECT:([tdb].[sys].[xml_index_nodes_279672044_256000].[XML_IX_ATestInsert] AS [group:1]),  WHERE:([tdb].[sys].[xml_index_nodes_279672044_256000].[hid] as [group:1].[hid]='€Á€À€') ORDERED FORWARD                                                           [group:1].[id], [group:1].[hid], [group:1].[pk1]                                                       1,099888E+07  125,3587      27,83016      28          153,1888         [group:1].[id], [group:1].[hid], [group:1].[pk1]         NULL     PLAN_ROW                                                         0        1
0                    0                                               |--Compute Scalar(DEFINE:([Expr1013]=getancestor([tdb].[sys].[xml_index_nodes_279672044_256000].[id] as [id:2].[id],(1))))                                                                                                           1           13          7           Compute Scalar                 Compute Scalar                 DEFINE:([Expr1013]=getancestor([tdb].[sys].[xml_index_nodes_279672044_256000].[id] as [id:2].[id],(1)))                                                                                                                                                         [Expr1013]=getancestor([tdb].[sys].[xml_index_nodes_279672044_256000].[id] as [id:2].[id],(1))        477973,8      0             0,04779737    472         175,5006         [id:2].[id], [id:2].[pk1], [Expr1013]                    NULL     PLAN_ROW                                                         0        1
1100460              1                                                    |--Filter(WHERE:([tdb].[sys].[xml_index_nodes_279672044_256000].[value] as [id:2].[value]=3))                                                                                                                                   1           14          13          Filter                         Filter                         WHERE:([tdb].[sys].[xml_index_nodes_279672044_256000].[value] as [id:2].[value]=3)                                                                                                                                                                              NULL                                                                                                   477973,8      0             22,264        20          175,4528         [id:2].[id], [id:2].[pk1]                                NULL     PLAN_ROW                                                         0        1
11000000             1                                                         |--Clustered Index Scan(OBJECT:([tdb].[sys].[xml_index_nodes_279672044_256000].[XML_IX_ATestInsert] AS [id:2]),  WHERE:([tdb].[sys].[xml_index_nodes_279672044_256000].[hid] as [id:2].[hid]='ã€Â€Á€À€') ORDERED FORWARD) 1           15          14          Clustered Index Scan           Clustered Index Scan           OBJECT:([tdb].[sys].[xml_index_nodes_279672044_256000].[XML_IX_ATestInsert] AS [id:2]),  WHERE:([tdb].[sys].[xml_index_nodes_279672044_256000].[hid] as [id:2].[hid]='ã€Â€Á€À€') ORDERED FORWARD                                                               [id:2].[id], [id:2].[value], [id:2].[hid], [id:2].[pk1]                                                477973,8      125,3587      27,83016      34          153,1888         [id:2].[id], [id:2].[value], [id:2].[hid], [id:2].[pk1]  NULL     PLAN_ROW                                                         0        1


1 мар 10, 13:15    [8411403]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса с условием по XML-полю  [new]
Диман.
Member

Откуда:
Сообщений: 21
Типизировав поле XML, в производительности выиграл в два раза (теперь запрос выполняется за ~10сек), но все равно долго.

CREATE XML SCHEMA COLLECTION groupCollection AS 
'<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://xmlfilter/group">
  <xsd:element name="root">
    <xsd:complexType mixed="true">
      <xsd:complexContent mixed="true">
        <xsd:restriction base="xsd:anyType">
          <xsd:sequence>
            <xsd:element name="groups" maxOccurs="unbounded">
              <xsd:complexType mixed="true">
                <xsd:complexContent mixed="true">
                  <xsd:restriction base="xsd:anyType">
                    <xsd:sequence>
                      <xsd:element name="group" maxOccurs="unbounded">
					  <xsd:complexType mixed="true">
						<xsd:complexContent mixed="true">
						  <xsd:restriction base="xsd:anyType">
							<xsd:attribute name="id" type="xsd:int" />
						  </xsd:restriction>
						</xsd:complexContent>
					  </xsd:complexType>
					  </xsd:element>
                    </xsd:sequence>
				  </xsd:restriction>
				</xsd:complexContent>
			  </xsd:complexType>
			 </xsd:element>
            </xsd:sequence>
		  </xsd:restriction>
		</xsd:complexContent>
	  </xsd:complexType>
	 </xsd:element>
</xsd:schema>'

GO

CREATE TABLE [dbo].[ATestInsert1](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[xml] XML(DOCUMENT groupCollection) NOT NULL,
 CONSTRAINT [PK_ATestInsert1] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Причем, наложение Primary XML индекса на поле, не дает выигрыша в производительности, а наоборот (с индеком ~20 сек.).
Остались ещё вопросы.
1. Почему Primary XML индекс по полю типа xml не дает выигрыша в производительности, а наоборот.
2. Как ещё можно попытаться оптимизировать исходный запрос
1 мар 10, 19:02    [8414351]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса с условием по XML-полю  [new]
aleks2
Guest
Диман.

1. подсчет кол-ва строк таким образом выполняется очень медленно (порядка 20 сек на таблице с ~1млн записей):
SELECT COUNT(*) FROM ATestInsert
WHERE xml.exist('/root/groups/group/@id[. = 3]') = 1

2. В тоже время, следующая выборка, выполняется довольно быстро (~0.002 сек):
SELECT * FROM ATestInsert
WHERE xml.exist('/root/groups/group/@id[. = 3]') = 1


Собственно вопрос в том, как ускорить выполнение 1-го запроса?
Пробовал наложить индекс на поле [xml], выигрыша в производительности не получил.


Хе-хе... и ты будешь утверждать, что второй возвращает ВСЕ строки?
Есть такая хрень asynchronous fetching - возврат рекордсета клиенту не дожидаясь окончания выборки. Создает иллюзию скорости. Дык, для второго оно и срабатывает.
Советую глядеть ишо и на статистику.

SELECT SUM(1) FROM ATestInsert
WHERE xml.exist('/root/groups/group/@id[. = 3]') = 1

будет выполняться стока же что и

SELECT COUNT(*) FROM ATestInsert
WHERE xml.exist('/root/groups/group/@id[. = 3]') = 1
2 мар 10, 06:11    [8415693]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса с условием по XML-полю  [new]
Диман.
Member

Откуда:
Сообщений: 21
aleks2
Хе-хе... и ты будешь утверждать, что второй возвращает ВСЕ строки?

пардон, не понял.


aleks2
Есть такая хрень asynchronous fetching - возврат рекордсета клиенту не дожидаясь окончания выборки. Создает иллюзию скорости. Дык, для второго оно и срабатывает.

сравнивал Total execution time в SSMS

aleks2

Советую глядеть ишо и на статистику.

Вы о чем конкретно? поглядеть на статистику выполнения? или на объекты, содержащие статистические сведения?
2 мар 10, 13:09    [8417718]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса с условием по XML-полю  [new]
Диман.
Member

Откуда:
Сообщений: 21
Если есть у кого-то ещё какие мысли, буду рад, если поделитесь.
Пока что с проблемой справиться не могу.
5 мар 10, 13:15    [8437095]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса с условием по XML-полю  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
Диман.
Если есть у кого-то ещё какие мысли, буду рад, если поделитесь.
Пока что с проблемой справиться не могу.
К сожалению, сидим с попкорном, наблюдаем очередное бесполезное издевательство над собой баловство с XML.

Но интересно. Ощущение, что вы нарвались на момент, когда внутренняя модель поведения сервера выходит "из оптимальной зоны". Возможно нужно вскрыть счётчики/флаги и дебажить, дебажить.
Неужели Stream Aggregate + XPath Filter вытупляет cервер, Parallelism как видим не влияет. Отчего? От огромного объёма данных? Не успевает освобождать? Локи страниц O_o?
Если хотите добить, то скажите ещё, что с Count(1) / Sum(1) типа "решило" проблему. x0
Но ваащето, как Glory и aleks2, что-то очень плохо верится в происходящее.

XML SCHEMA, по идее, сократила объём перелопачиваемых данных, что облегчило.
Но чтоб справиться с проблемой, убейте XML и повесьте нормально таблу.
6 мар 10, 01:02    [8440960]     Ответить | Цитировать Сообщить модератору
Между сообщениями интервал более 1 года.
 Re: Оптимизация запроса с условием по XML-полю  [new]
artemds
Member

Откуда:
Сообщений: 46
Для "ускорения" выполнения запроса необходимо все параметры используемые в WHERE вывести в временную таблицу.

create table #orders (cOrder int, cClient int)

insert into #orders
select ordID, ordXML.value('(root/id)[1]', 'int') from Orders 

select * Orders 
inner join #orders on cOrder = ordID
where  #orders.cClient = 13725 


drop table #orders


Этот запрос будет выполняться раз в 50 быстрее чем:

select * Orders 
where ordXML.value('(root/id)[1]', 'int')  = 13725 


Та процедура из которой я выкроил этот код выполняется 0.43 секунды против 1:12 мин. Поэтому, лично я, оформляю всегда подобные злачные (а без них часто бывает еще сложнее) запросы в хранимую процедуру, чтобы хоть оптимизатор лучше работал и статистика обновлялась.
19 июл 13, 09:16    [14587499]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить