Как ускорить рекурсивный CTE

Учитывая следующий рекурсивный CTE (немного упрощенный для моего примера):

    WITH myCTE (sort, parentid, myid, level, somedata)
AS
(
-- Anchor member definition
    SELECT 
        CAST(ROW_NUMBER() OVER(ORDER BY o.myid) as decimal(38, 20)) as sort,
        o.parentid, 
        o.myid, 
        0 as level, 
        o.somedata
    FROM 
        table1 t1,
        table2 t1,
        datatable o
    WHERE t1.somebool = 1 AND t2.id = t1.foreignid and o.foreignkey = t2.key
        and o.parentid = ''
    UNION ALL
-- Recursive member definition
    SELECT
        CAST(b.sort + (ROW_NUMBER() OVER(ORDER BY o.myid) / power(10.0, b.level + 1)) as decimal(38, 20)) as sort,
        o.parentid, 
        o.myid, 
        b.level + 1, 
        o.somedata
    FROM datatable o
    INNER JOIN myCTE AS b
        ON o.parentid = b.myid

Общая идея заключается в следующем: начиная с выбора элементов на основе table1 и table2, я хочу запустить запрос и в привязке я хочу найти все данные без родителя, соединить их с данными, имеющими основные элементы и родители и продолжайте копать, пока я не найду все данные. Я имею дело с деревом неизвестной глубины, хотя максимальный уровень, который я нашел, равен 7.

Полезно ли применять дополнительные ограничения к рекурсивному члену? Кажется, это работает лучше, чем больше ограничений я копирую из якоря, но разве для этого недостаточно фильтрации по "o.parentid = b.myid"?


sql recursion tsql common-table-expression recursive-query
person Davio    schedule 29.05.2013    source источник
comment
datatable.parentid проиндексирован? Используется ли в плане выполнения индекс?   -  person Martin Smith    schedule 29.05.2013
comment
Можете ли вы предоставить нам план выполнения? Если вы не знаете, как это сделать, взгляните на это: plixa. nl / how-to-read-query-Execution-планы С планом выполнения есть еще много чего сказать :)   -  person Mark Kremers    schedule 29.05.2013
comment
К сожалению, у меня нет права просматривать план выполнения. :( Но datatable.parentid не индексируется сам по себе. Тем не менее, он индексируется как часть управляющего индекса, попытается проверить, могу ли я каким-то образом его запустить.   -  person Davio    schedule 29.05.2013
comment
Казалось, что попадание в правильный индекс поможет. Я перечислил все столбцы из индекса в моем выборе, и (после кеширования, я полагаю) теперь он выполняется за 1 секунду.   -  person Davio    schedule 29.05.2013
comment
Хорошо, в конце концов, это не оказалось ответом, и мы решили просто сделать один большой плоский выбор и упорядочить дерево в коде вместо того, чтобы запускать 10000 запросов выбора на сервер.   -  person Davio    schedule 29.05.2013
comment
Какой тип данных является парентифицированным? (var) char? Почему вы заказываете по myid? Каким индикатором заказа является ID?   -  person Alexander    schedule 25.06.2013

Ответы (1)


arrow_upward
1
arrow_downward

Вот подход к отладке / настройке, который я бы порекомендовал:

1) Добавьте следующую подсказку запроса, чтобы исключить возможность бесконечного цикла.

...
)
SELECT  parentid ,
        myid ,
        [level]
FROM myCTE 
OPTION (MAXRECURSION 20)

2) Если это дает результаты, попробуйте тот же запрос с закомментированной подсказкой. Он должен выполняться примерно за одно и то же время и возвращать такое же количество результатов.

3) На этом этапе вы должны снова добавить поле сортировки. Если нет специального требования для включения его в рекурсию, я бы рекомендовал вам переместить его в Query против CTE, например.

...
)
SELECT  parentid ,
        myid ,
        [level],
        -- I'm not sure what this accomplishes:
        -- ROW_NUMBER() OVER (ORDER BY CAST(myid) ASC) / CAST (POWER(10,level) AS DECIMAL(38,20)) AS sort
        -- Wouldn't this look nicer?
        ROW_NUMBER() OVER (ORDER BY [level] ASC, CAST ([parentid] AS INT) ASC, CAST ([myid] AS INT) ASC) AS sort

FROM myCTE 
ORDER BY [sort]
OPTION (MAXRECURSION 20)

3) Наконец, добавьте обратно в свои объединения в Table1 и Table2. Я бы посоветовал вам также добавить его в запрос CTE.

Я понимаю, что у вас есть обходной путь, но я был бы признателен, если бы вы нашли время, чтобы увидеть, работает ли мой подход для вас.

person Jim V.    schedule 24.07.2014