Потоковая передача рекурсивных CTE SQL Server 2008

У меня есть набор данных из таблицы (TableA), которая относится к себе через TableB. Родители в TableA имеют детей в TableA. У этих детей тоже могут быть дети. Здесь ничего удивительного.

У меня есть набор строк верхнего уровня из TableA, с которым мне нужно работать. Прежде чем я смогу работать с этими строками, у меня должна быть под рукой каждая дочерняя строка. Я должен иметь возможность работать с каждой строкой верхнего уровня TableA (и ее дочерними элементами) как можно быстрее в моем приложении.

Я не могу найти способ сделать это.

Использование рекурсивного CTE (TableA верхнего уровня, установленного как привязка, TableB->TableA объединение как объединение), не соответствует требованиям. Полный набор верхнего уровня из TableA возвращается в CTE, прежде чем он будет работать на уровне 2 дочерних элементов. Затем он работает на уровне 3. Затем на уровне 4 и т. Д. Поскольку мой набор верхнего уровня составляет более 400 000 строк, мое клиентское приложение не может начать работать со строками, пока ВЕСЬ набор данных не будет загружен на сервер.

Мне нужен способ получше. Я пробовал передавать клиенту плоский набор TableA строк верхнего уровня и заставлять клиента многократно выдавать рекурсивный оператор CTE для каждой TableA строки верхнего уровня. Это действительно работает. Но слишком много шума. Постоянная скорость извлечения строк слишком велика из-за многократной повторной выдачи операторов.

Мне нужно креативное решение.

Фрагмент используемого мной CTE для каждой записи. В этом примере TableA - это Member, а TableB - это MemberReplacement. Я вырвал большую часть оператора select посередине и большую часть соединений.

WITH T_MemberRecurse
(
    MemberId,
    IncludedMemberId,
    Level
) AS (
    SELECT      Member.Id,
                Member.Id,
                0
    FROM        MemberInput
    INNER JOIN  MemberInputItem
        ON      MemberInputItem.MemberInputId = MemberInput.Id
    INNER JOIN  Member
        ON      Member.Id = MemberInputItem.MemberId
    UNION ALL
    SELECT      T_MemberRecurse.MemberId,
                Member2.Id,
                Level + 1
    FROM        T_MemberRecurse
    INNER JOIN  Member
        ON      Member.Id = T_MemberRecurse.IncludedMemberId
    INNER JOIN  MemberReplacement
        ON      MemberReplacement.MemberId = Member.Id
    INNER JOIN  Member Member2
        ON      Member2.Id = MemberReplacement.OriginalMemberId
)
SELECT      Member.Id,
            T_MemberRecurse.IncludedMemberId,
            T_MemberRecurse.Level,

FROM        MemberInput
INNER JOIN  LotsOfTables

recursion sql-server-2008 common-table-expression
person Jerome Haltom    schedule 19.01.2012    source источник
comment
Можете ли вы опубликовать образец кода и образцы данных?   -  person JNK    schedule 19.01.2012
comment
Просто сделал. Я добавил пример CTE для каждой записи. Проблема с размещением примеров для этого заключается в том, что ни один из моих примеров даже не спроектирован правильно, чтобы решить эту проблему.   -  person Jerome Haltom    schedule 19.01.2012
comment
Извините, "неправильно спроектировано" - что вы имеете в виду? Возвращают ли ваши нынешние решения неверные результаты или они просто недостаточно эффективны?   -  person Andriy M    schedule 20.01.2012
comment
Они недостаточно эффективны. Как уже упоминалось, подход CTE не работает, поскольку рекурсивные CTE не начинают возвращать записи второго уровня, пока не будут возвращены ВСЕ записи первого уровня. CTE производит правильные данные, но не в правильном порядке или скорости.   -  person Jerome Haltom    schedule 18.02.2012

Ответы (1)


arrow_upward
1
arrow_downward

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

Превратите свой CTE в функцию, возвращающую набор строк, с одним параметром, желаемым идентификатором участника.

Потом:

SELECT
   *
FROM
   Member M
   CROSS APPLY dbo.MemberChildren(M.Id) C
WHERE
   {Conditions for desired set of Members here}
WITH (FAST 20);

Пожалуйста, дайте мне знать, работает ли это. Идея состоит в том, чтобы заставить двигатель двигаться в глубину, а не в ширину. Это может снизить общую производительность сервера, но теоретически должно позволить вашему клиенту начать работу с некоторыми строками данных.

Обновить

Вторая идея: получить информацию о родительском и дочернем элементах отдельно и выполнить логическое объединение в клиенте. (Упорядоченный вложенный цикл, который только продвигает упорядоченный второй / внутренний ввод до тех пор, пока он не станет несовпадающим.) Получите сразу меньшие фрагменты, используя диапазоны ключей или row_number. Или получите весь родительский набор, а затем получите меньший набор дочерних строк.

Обновление 2

Идея 3. Вместо рекурсивного CTE используйте 5 простых соединений, чтобы получить все необходимые данные. Это звучит ужасно, но должно позволить вам сделать FAST 100, чтобы начать работу с данными.

person ErikE    schedule 06.02.2012
comment
Хм. Это может сработать. Пытался избежать объявления функции для выполнения, но это могло быть подходящим и могло работать нормально. Я попробую в понедельник. - person Jerome Haltom; 18.02.2012
comment
CROSS APPLY с подзапросом, содержащим внешнюю ссылку, теоретически может идти в глубину, но, возможно, и нет. Таким образом, функция. - person ErikE; 19.02.2012
comment
Если бы вы могли поместить CTE в подзапрос, я бы решил это некоторое время назад. знак равно - person Jerome Haltom; 19.02.2012
comment
На сколько уровней он идет? Изначально у меня создалось впечатление, что их было всего 2 или 3. - person ErikE; 19.02.2012
comment
Это сработало. Большое спасибо! Я не знал о CROSS APPLY. - person Jerome Haltom; 28.02.2012
comment
Как ни странно, это улучшает общее время отклика. - person Jerome Haltom; 28.02.2012
comment
Похоже, что набор данных использовал слишком много tempdb. Теперь он, наверное, может лениво катиться. Если бы мой ответ помог вам, отметили бы вы его как принятый? - person ErikE; 28.02.2012