Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Cross apply, CLR table valued function и параллелизм  [new]
Dmitrii Stepanov
Member

Откуда:
Сообщений: 15
Добрый день!
У меня неожиданно возникла следующая проблема.
Есть таблица, в которой хранятся пространственные данные типа geometry. Для простоты можно считать, что таблица состоит из двух столбцов: PR_IDX (int, primary key) и S_GEOMETRY (geometry). Все объекты в таблице площадные. В таблице около 10 000 записей. По столбцу S_GEOMETRY построен пространственный индекс.
Необходимо создать представление, которое показывает наложения полигонов друг на друга. С самим запросом проблем нет. Единственное, для того, чтобы "разбить" получаемые наложения на отдельные полигоны, я создал на C# простенькую TVF. В итоге получился примерно такой запрос:
WITH CTE AS (
    SELECT
        L.PR_IDX AS L_IDX,
        R.PR_IDX AS R_IDX,
        L.S_GEOMETRY AS L_GEOMETRY,
        R.S_GEOMETRY AS R_GEOMETRY
    FROM dbo.Table1 AS L
    INNER JOIN dbo.Table1 AS R
    ON L.PR_IDX < R.PR_IDX AND L.S_GEOMETRY.STOverlaps(R.S_GEOMETRY) = 1)
SELECT
    C.L_IDX,
    C.R_IDX,
    D.S_GEOMETRY
FROM CTE AS C
CROSS APPLY dbo.fDestroyGeometry(C.L_GEOMETRY.STIntersection(C.R_GEOMETRY)) AS D

Так вот, проблема в том, что из-за CROSS APPLY весь запрос выполняется последовательно и его выполнение занимает порядка 5 минут. При этом, если попробовать выполнить запрос из CTE отдельно, то он использует параллелизм и выполняется 40 секунд. Если сделать выборку из CTE в отдельную таблицу (через select ... into), и уже по новой таблице выполнить запрос с использованием TVF, то запрос выполняется за менее чем 1 секунду (то есть причина не в реализации TVF).
TVF не использует пользовательские или системные данные, является детерминированной и точной (атрибуты указаны явно). Сборка создана с PERMISSION_SET = SAFE.
Была идея отдельно создать индексированное представление, и уже обращаться к нему, но метод STOverlaps не является точным.
Можно ли как-то "заставить" MS SQL использовать параллелизм в таком случае? Пусть даже хотя бы до запроса с CROSS APPLY.
Ведь с точки зрения здорового человека табличная функция не использует никакие разделяемые данные и её можно спокойно использовать в многопоточном сценарии.
16 окт 17, 20:53    [20874085]     Ответить | Цитировать Сообщить модератору
 Re: Cross apply, CLR table valued function и параллелизм  [new]
Dmitrii Stepanov
Member

Откуда:
Сообщений: 15
Забыл добавить, что версия MS SQL 2014 SP2 x64.
16 окт 17, 20:55    [20874089]     Ответить | Цитировать Сообщить модератору
 Re: Cross apply, CLR table valued function и параллелизм  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36693
https://blogs.msdn.microsoft.com/psssql/2013/08/28/sql-2012-query-plan-enhancement-i-want-to-know-why-my-query-is-not-parallelized/
16 окт 17, 21:02    [20874100]     Ответить | Цитировать Сообщить модератору
 Re: Cross apply, CLR table valued function и параллелизм  [new]
Andy_OLAP
Member

Откуда: я знаю, что Хапоэль Беэр-Шева - чемпион
Сообщений: 3151
Dmitrii Stepanov, можно попробовать после select указать option (maxdop 4/8, force order, hash join). 4-8 подбирать по числу ядер и файлов в tempdb.
16 окт 17, 21:03    [20874101]     Ответить | Цитировать Сообщить модератору
 Re: Cross apply, CLR table valued function и параллелизм  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36693
Andy_OLAP
Dmitrii Stepanov, можно попробовать после select указать option (maxdop 4/8, force order, hash join). 4-8 подбирать по числу ядер и файлов в tempdb.
Уж лучше начать с QUERYTRACEON 8649
16 окт 17, 21:07    [20874109]     Ответить | Цитировать Сообщить модератору
 Re: Cross apply, CLR table valued function и параллелизм  [new]
Dmitrii Stepanov
Member

Откуда:
Сообщений: 15
Andy_OLAP, забыл написать, что да, хинты тоже пробовал, не помогло.
16 окт 17, 21:08    [20874110]     Ответить | Цитировать Сообщить модератору
 Re: Cross apply, CLR table valued function и параллелизм  [new]
Dmitrii Stepanov
Member

Откуда:
Сообщений: 15
Гавриленко Сергей Алексеевич, спасибо за ссылку. Разве этот флаг не требует привилегий администратора?
16 окт 17, 21:11    [20874116]     Ответить | Цитировать Сообщить модератору
 Re: Cross apply, CLR table valued function и параллелизм  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36693
Dmitrii Stepanov
Гавриленко Сергей Алексеевич, спасибо за ссылку. Разве этот флаг не требует привилегий администратора?
Тут написано, что требует, но не факт, что не будет работать через цепочки владения и прочие execute as.

В любом случае, прежде чем принимать лекарство, неплохо бы выяснить, в чем причины.
16 окт 17, 21:16    [20874125]     Ответить | Цитировать Сообщить модератору
 Re: Cross apply, CLR table valued function и параллелизм  [new]
Andy_OLAP
Member

Откуда: я знаю, что Хапоэль Беэр-Шева - чемпион
Сообщений: 3151
Dmitrii Stepanov, собственно, тут вариант один - переписать T-SQL Inline Table Valued Function, если это реально.
16 окт 17, 21:16    [20874126]     Ответить | Цитировать Сообщить модератору
 Re: Cross apply, CLR table valued function и параллелизм  [new]
Dmitrii Stepanov
Member

Откуда:
Сообщений: 15
Andy_OLAP, если бы всё было так просто, то сразу бы написал через inline tvf:)

Гавриленко Сергей Алексеевич, в плане выполнения вообще нет NonParallelPlanReason. Как я понимаю, это означает, что SQL Server в принципе не против того, что запрос можно параллелить. С QUERYTRACEON 8649 запрос выполнился параллельно.
17 окт 17, 10:13    [20874894]     Ответить | Цитировать Сообщить модератору
 Re: Cross apply, CLR table valued function и параллелизм  [new]
Dmitrii Stepanov
Member

Откуда:
Сообщений: 15
Но всё равно не понятно, что с этим можно дальше делать. Да, запрос может выполняться параллельно. Да, он выполняется параллельно намного быстрее, чем последовательно. Но как заставить SQL Server "понять" это с учетом того, что необходимо создать представление?
17 окт 17, 11:57    [20875384]     Ответить | Цитировать Сообщить модератору
 Re: Cross apply, CLR table valued function и параллелизм  [new]
Andy_OLAP
Member

Откуда: я знаю, что Хапоэль Беэр-Шева - чемпион
Сообщений: 3151
Dmitrii Stepanov
Но всё равно не понятно, что с этим можно дальше делать. Да, запрос может выполняться параллельно. Да, он выполняется параллельно намного быстрее, чем последовательно. Но как заставить SQL Server "понять" это с учетом того, что необходимо создать представление?

Сделайте view поверх строк из table-valued function, а внутри функции используйте option (querytraceon 8649).
17 окт 17, 12:08    [20875471]     Ответить | Цитировать Сообщить модератору
 Re: Cross apply, CLR table valued function и параллелизм  [new]
invm
Member

Откуда: Москва
Сообщений: 9122
Dmitrii Stepanov
С QUERYTRACEON 8649 запрос выполнился параллельно.
Cost threshold for parallelism какой? Стоимость запроса без TF 8649 какая?
17 окт 17, 12:29    [20875549]     Ответить | Цитировать Сообщить модератору
 Re: Cross apply, CLR table valued function и параллелизм  [new]
Dmitrii Stepanov
Member

Откуда:
Сообщений: 15
Andy_OLAP, насколько я помню, OPTION нельзя использовать в udf, не?
17 окт 17, 12:46    [20875608]     Ответить | Цитировать Сообщить модератору
 Re: Cross apply, CLR table valued function и параллелизм  [new]
Dmitrii Stepanov
Member

Откуда:
Сообщений: 15
invm, CTFP = 50. Стоимости запросов 3500 и 3630 соответственно.
17 окт 17, 12:57    [20875661]     Ответить | Цитировать Сообщить модератору
 Re: Cross apply, CLR table valued function и параллелизм  [new]
Andy_OLAP
Member

Откуда: я знаю, что Хапоэль Беэр-Шева - чемпион
Сообщений: 3151
Dmitrii Stepanov
Andy_OLAP, насколько я помню, OPTION нельзя использовать в udf, не?

Да, но можно через таблицу постоянную. Заполнять в нее, а поверх новой таблицы view.
17 окт 17, 13:16    [20875759]     Ответить | Цитировать Сообщить модератору
 Re: Cross apply, CLR table valued function и параллелизм  [new]
Dmitrii Stepanov
Member

Откуда:
Сообщений: 15
Andy_OLAP
Dmitrii Stepanov
Andy_OLAP, насколько я помню, OPTION нельзя использовать в udf, не?

Да, но можно через таблицу постоянную. Заполнять в нее, а поверх новой таблицы view.

UDF не могут изменять состояние БД. Соответственно, через UDF нельзя очистить постоянную таблицу.
17 окт 17, 13:32    [20875817]     Ответить | Цитировать Сообщить модератору
 Re: Cross apply, CLR table valued function и параллелизм  [new]
Andy_OLAP
Member

Откуда: я знаю, что Хапоэль Беэр-Шева - чемпион
Сообщений: 3151
Dmitrii Stepanov
Andy_OLAP
пропущено...

Да, но можно через таблицу постоянную. Заполнять в нее, а поверх новой таблицы view.

UDF не могут изменять состояние БД. Соответственно, через UDF нельзя очистить постоянную таблицу.

Имел в виду, что повесить на Table1 триггер - если таблица поменялась, то вызывать пересчет новой таблицы Table2. Даже не триггер, а просто в процедуре загрузки Table1 вызвать затем пересчет новой таблицы Table2.
И view поверх таблицы Table2.
"выполнить запрос из CTE отдельно, то он использует параллелизм и выполняется 40 секунд." - видимо, CTE возвращает физически не так много строк - ну так и пишите их в Table2, будете точно знать, сколько вернется внутри view.
17 окт 17, 13:39    [20875845]     Ответить | Цитировать Сообщить модератору
 Re: Cross apply, CLR table valued function и параллелизм  [new]
Dmitrii Stepanov
Member

Откуда:
Сообщений: 15
Andy_OLAP
Dmitrii Stepanov
пропущено...

UDF не могут изменять состояние БД. Соответственно, через UDF нельзя очистить постоянную таблицу.

Имел в виду, что повесить на Table1 триггер - если таблица поменялась, то вызывать пересчет новой таблицы Table2. Даже не триггер, а просто в процедуре загрузки Table1 вызвать затем пересчет новой таблицы Table2.
И view поверх таблицы Table2.
"выполнить запрос из CTE отдельно, то он использует параллелизм и выполняется 40 секунд." - видимо, CTE возвращает физически не так много строк - ну так и пишите их в Table2, будете точно знать, сколько вернется внутри view.

Эмм... При изменении данных надо делать пересчёт на всей таблице (так по логике). В итоге каждый insert/update будет ждать пересчёт по 30-40 секунд в лучшем случае.
17 окт 17, 14:01    [20875953]     Ответить | Цитировать Сообщить модератору
 Re: Cross apply, CLR table valued function и параллелизм  [new]
Andy_OLAP
Member

Откуда: я знаю, что Хапоэль Беэр-Шева - чемпион
Сообщений: 3151
Dmitrii Stepanov
Andy_OLAP
пропущено...

Имел в виду, что повесить на Table1 триггер - если таблица поменялась, то вызывать пересчет новой таблицы Table2. Даже не триггер, а просто в процедуре загрузки Table1 вызвать затем пересчет новой таблицы Table2.
И view поверх таблицы Table2.
"выполнить запрос из CTE отдельно, то он использует параллелизм и выполняется 40 секунд." - видимо, CTE возвращает физически не так много строк - ну так и пишите их в Table2, будете точно знать, сколько вернется внутри view.

Эмм... При изменении данных надо делать пересчёт на всей таблице (так по логике). В итоге каждый insert/update будет ждать пересчёт по 30-40 секунд в лучшем случае.

Неужели таблица так часто меняется, что 40 секунд не подождать после ее перезаполнения? Или в любой момент в течение суток может измениться одна строка и это происходит очень часто?
17 окт 17, 15:08    [20876290]     Ответить | Цитировать Сообщить модератору
 Re: Cross apply, CLR table valued function и параллелизм  [new]
Dmitrii Stepanov
Member

Откуда:
Сообщений: 15
В принципе, нашел некоторый обходной путь. Оригинал здесь.
Если вкратце, то суть в следующем:
SQL Server не может адекватно оценить стоимость выполнения udf и ставит ей низкую оценку, в результате чего запрос не распараллеливается. Если мы знаем, что наша udf выполняет сложную вычислительную задачу (с точки зрения использования процессора), то для эмуляции вычислений можно использовать inline-udf, а сам запрос отправить в cross apply. В результате мы получим распараллеливание. Пример такой функции в статье по ссылке выше.
17 окт 17, 16:15    [20876605]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить