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

Откуда: Москва
Сообщений: 888
Добрый вечер, господа. Перекопал как положено весь форум, но вариант не нашёл(
Рекурсию делал только через CTE, поэтому даже не представляю, как можно сделать без него.
Вопрос возник в связи с тем, что данное CTE используется во VIEW. На view завязано достаточно много из стороннего приложения, поэтому отвязаться от него не получится.

with tree as(
select ElementID as CSKUId, ElementID, ParentId, PGCode, [level], NameRus, ExtBrandId
from v_Goods_Elements_Work
where [level]=7
union all
select	A.CSKUId, dt.ElementID, dt.ParentId, dt.PGCode, dt.[level], dt.NameRus, dt.ExtBrandId
from tree as A
	inner join v_Goods_Elements_Work as dt on a.ParentID=dt.ElementID
	
)

select CSKUId, ElementID, ParentId, PGCode, [level], NameRus, ExtBrandId
from tree [/SQL]

Level = {-50,-40...6,7}. 

Строится данная VIEWшка из ещё оной, но там просто

[SRC SQL]CREATE VIEW v_Goods_Elements_Work AS
SELECT TOP 100 PERCENT FROM General..t_Goods_Element


Т.е. таблица находится в другой базе данных. Так вот. Можно ли как-то реализовать рекурсию в представлении без использования CTE??
Просто запрос к данному представлению работает достаточно долго (около 3 секунд).
Я попробовал без рекурсии хард кодингом сделать, тупо для каждого уровня по очереди перебирая отдельными селектами, отрабатывает за 0,3 сек. Но я сделал только дла уровней от 0 до 7) А надо ещё до -50 уровня, а это уже мясо))

Если что непонятно, готов пояснить)

Заранее спасибо!
16 май 13, 16:51    [14306882]     Ответить | Цитировать Сообщить модератору
 Re: Реализация рекурсии без CTE  [new]
Glory
Member

Откуда:
Сообщений: 104751
Сергей Викт.
Можно ли как-то реализовать рекурсию в представлении без использования CTE??

Нет.
Сергей Викт.
Я попробовал без рекурсии хард кодингом сделать, тупо для каждого уровня по очереди перебирая отдельными селектами, отрабатывает за 0,3 сек. Но я сделал только дла уровней от 0 до 7) А надо ещё до -50 уровня, а это уже мясо))

И что мешает сделать "хард код" 50 уровня в представлении ?

Сергей Викт.
Просто запрос к данному представлению работает достаточно долго (около 3 секунд).

И вы проанализировали план выполнения ? Нашли узкое место ?
16 май 13, 16:53    [14306906]     Ответить | Цитировать Сообщить модератору
 Re: Реализация рекурсии без CTE  [new]
Сергей Викт.
Member

Откуда: Москва
Сообщений: 888
Glory
Сергей Викт.
Можно ли как-то реализовать рекурсию в представлении без использования CTE??

Нет.

Блин, вот это уже крайне печально.

Glory
Сергей Викт.
Я попробовал без рекурсии хард кодингом сделать, тупо для каждого уровня по очереди перебирая отдельными селектами, отрабатывает за 0,3 сек. Но я сделал только дла уровней от 0 до 7) А надо ещё до -50 уровня, а это уже мясо))

И что мешает сделать "хард код" 50 уровня в представлении ?

Вера не позволяет:) (шучу) Можно конечно, но если вдруг добавятся ещё более низкие уровни, то придётся добавлять достаточно много чего.

Glory
Сергей Викт.
Просто запрос к данному представлению работает достаточно долго (около 3 секунд).

И вы проанализировали план выполнения ? Нашли узкое место ?

Пытался. Но вот с планами только разбираюсь.
Как можно сюда прикрепить план выполнения запроса?
16 май 13, 16:59    [14306965]     Ответить | Цитировать Сообщить модератору
 Re: Реализация рекурсии без CTE  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
Сергей Викт.
CREATE VIEW [dbo].[v_Goods_Elements_Work] AS
SELECT	TOP(100) PERCENT *
FROM	General.dbo.t_Goods_Element
GO
CREATE VIEW [dbo].[v_Goods_Elements_Tree] AS
WITH [Tree] AS (
	SELECT	 ElementID	AS CSKUId
		,ElementID
		,ParentId
		,PGCode
		,[level]
		,NameRus
		,ExtBrandId
	FROM	dbo.v_Goods_Elements_Work
	WHERE	[level] = 7
UNION ALL
	SELECT	 T.CSKUId
		,D.ElementID
		,D.ParentId
		,D.PGCode
		,D.[level]
		,D.NameRus
		,D.ExtBrandId
	FROM	[Tree]				T
	JOIN	dbo.v_Goods_Elements_Work	D ON D.ElementID = T.ParentID
)	SELECT	*
	FROM	[Tree]
GO
А это что за бурда? (выделено красным)

Сергей Викт.
Т.е. таблица находится в другой базе данных
Это неважно.
Сергей Викт.
На view завязано достаточно много из стороннего приложения
Прям все везде вытягивают все колонки?

И вытягивть парент 7-го уровня пропуская всю таблу через спул?
Правильно ли я понимаю что это просто запрос WHERE [level] >= 7 + одна дополнительная колонка (парент 7-го уровня)?

Я одного немогу понять, [level] вычисляется при вставке в таблицу, а CSKUId уже нет. Почему?
При таких обстоятельства hierarchyid по вам плачет.
17 май 13, 00:28    [14308882]     Ответить | Цитировать Сообщить модератору
 Re: Реализация рекурсии без CTE  [new]
Сергей Викт.
Member

Откуда: Москва
Сообщений: 888
[quot Mnior]
Сергей Викт.
CREATE VIEW [dbo].[v_Goods_Elements_Work] AS
SELECT	TOP(100) PERCENT *
FROM	General.dbo.t_Goods_Element
GO
А это что за бурда? (выделено красным)[/qout]
Тот я наврал. Недокопировал.

SELECT     TOP (100) PERCENT ElementID, ParentID, PGCode, NameRus, NameEng, ShortName, [Level], ExtBrandID, Position,PositionPG
FROM         General.dbo.t_Goods_Elements AS ge
ORDER BY ElementID, [Level]

Топ нужен, что б использовать ORDER BY

Mnior
Сергей Викт.
На view завязано достаточно много из стороннего приложения
Прям все везде вытягивают все колонки?

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

         declare @w table(CSKUId bigint)
         insert @w values (10002692)
         insert @w values (10002735)
         insert @w values (10003174)
         insert @w values (10013094)
         insert @w values (10015218)
         insert @w values (10002692)
         insert @w values (10002735)
         insert @w values (10003174)
         insert @w values (10013094)
         insert @w values (10018759)
         insert @w values (10010785)
--SET STATISTICS TIME ON
select TOP 1 bc.ItemId
  from @w w
          inner join v_GoodsTreeElements_test/*новая*/ t with(nolock) on w.CSKUId = t.CSKUId 
          inner join v_BrandCo bc with(nolock) on t.ExtBrandId = bc.ItemId
     where t.Level = 1
     order by bc.Position

--SET STATISTICS TIME OFF
GO

Т.е. есть номера документов. Надо посмотреть в разрезе бизнес направлений и выбрать самую верхнюю позицию для того, чтобы понять в каком бизнес направлении надо увеличивать продажи (ну грубо говоря).
По факту здесь та вьюха используется для того, чтобы получить IDшники ExtBrandID и по ним выбрать самую верхнюю позицию бизнес напрвления из BrandCo.

Mnior
И вытягивть парент 7-го уровня пропуская всю таблу через спул?
При таких обстоятельства hierarchyid по вам плачет.


От обратного: приложение писалось постепенно, начиная с SQLServer 7. Там типа hierrarcyid еще не было. Вроде он с 2005 появился (могу ошибаться).

А что значит выражение:
Mnior
И вытягивть парент 7-го уровня пропуская всю таблу через спул?

Не понял смысла просто, простите, я пока достаточный ламер и всей терминологии не знаю.

Mnior
Правильно ли я понимаю что это просто запрос WHERE [level] >= 7 + одна дополнительная колонка (парент 7-го уровня)?

Да, только ниже 7 уровня, но суть такая.
17 май 13, 09:01    [14309323]     Ответить | Цитировать Сообщить модератору
 Re: Реализация рекурсии без CTE  [new]
Сергей Викт.
Member

Откуда: Москва
Сообщений: 888
Сергей Викт.

Не понял смысла просто, простите, я пока достаточный ламер и всей терминологии не знаю.



Не понял смысла ВОПРОСА) Пропусти, извините.
17 май 13, 09:03    [14309328]     Ответить | Цитировать Сообщить модератору
 Re: Реализация рекурсии без CTE  [new]
Glory
Member

Откуда:
Сообщений: 104751
Сергей Викт.
Топ нужен, что б использовать ORDER BY

ORDER BY внутри представления игнорируется при выполнении.
17 май 13, 09:15    [14309367]     Ответить | Цитировать Сообщить модератору
 Re: Реализация рекурсии без CTE  [new]
Glory
Member

Откуда:
Сообщений: 104751
When ORDER BY is used in the definition of a view, inline function, derived table, or subquery, the clause is used only to determine the rows returned by the TOP clause. The ORDER BY clause does not guarantee ordered results when these constructs are queried, unless ORDER BY is also specified in the query itself.
17 май 13, 09:23    [14309391]     Ответить | Цитировать Сообщить модератору
 Re: Реализация рекурсии без CTE  [new]
Сергей Викт.
Member

Откуда: Москва
Сообщений: 888
Glory
When ORDER BY is used in the definition of a view, inline function, derived table, or subquery, the clause is used only to determine the rows returned by the TOP clause. The ORDER BY clause does not guarantee ordered results when these constructs are queried, unless ORDER BY is also specified in the query itself.


Понял. Значит спрошу у программистов, зачем им это было надо.


ОФФТОП: Glory, alexwyg (вроде так),tpg и другие гуру. Вопрос: сколько лет надо отсидеть в скуле, чтобы хоть на 50% знать столько, сколько знаете вы?:) Я достаточно вьедливый и сам, достаточно много читаю книг, форумов. Но я не представляю, как всё это помещается в вашей голове. Сорри за офтоп.

P.S. Обучаюсь я очень быстро;)
17 май 13, 09:27    [14309400]     Ответить | Цитировать Сообщить модератору
 Re: Реализация рекурсии без CTE  [new]
Гость333
Member

Откуда:
Сообщений: 3683
Сергей Викт.
От обратного: приложение писалось постепенно, начиная с SQLServer 7. Там типа hierrarcyid еще не было. Вроде он с 2005 появился (могу ошибаться).

В 2008.


Сергей Викт.
Glory
When ORDER BY is used in the definition of a view, inline function, derived table, or subquery, the clause is used only to determine the rows returned by the TOP clause. The ORDER BY clause does not guarantee ordered results when these constructs are queried, unless ORDER BY is also specified in the query itself.

Понял. Значит спрошу у программистов, зачем им это было надо.

Это наследие со старых версий MSSQL. Указанное примечание про ORDER BY появилось, начиная с версии 2005. До версии 2005 так можно было создавать отсортированные представления.
17 май 13, 12:04    [14310565]     Ответить | Цитировать Сообщить модератору
 Re: Реализация рекурсии без CTE  [new]
MasterZiv
Member

Откуда: Питер
Сообщений: 34658
Сергей Викт.,

Я разбирался (переделывал) недавно с "рекурсией", правда, в оракле. И не смотря на то, что там есть все возможности с CTE, сделано было и до, и после БЕЗ него.

  • Вариант 1, как было ДО. Очень просто. Геренировался динамически текст запроса на нужную глубину вложенности (она имеет небольшой максимум по постановке задачи, типа -- 5 максимум, больше не бывает).
  • Вариант 2 -- как стало. Тоже очень просто -- создаётся временная табличка, в неё закидываются начальные записи -- основа (база) рекурсии, затем в цикле в запросе на базе записей от предыдущего шага в ту же таблицу добавляются новые записи запросом,
    и так пока рекурсия может продолжаться.

    Рекомендую из двух второй вариант.

    Но мне опять таки НЕ ПОНЯТНО, если у тебя возможность использовать CTE есть, почему ты от неё отказываешься. Т.е. ты не понял проблему, но почему-то считаешь, что если сделать по-другому, то магическим образом всё станет быстро и хорошо.
  • 17 май 13, 12:16    [14310694]     Ответить | Цитировать Сообщить модератору
     Re: Реализация рекурсии без CTE  [new]
    Сергей Викт.
    Member

    Откуда: Москва
    Сообщений: 888
    MasterZiv
    Сергей Викт.,
    Но мне опять таки НЕ ПОНЯТНО, если у тебя возможность использовать CTE есть, почему ты от неё отказываешься. Т.е. ты не понял проблему, но почему-то считаешь, что если сделать по-другому, то магическим образом всё станет быстро и хорошо.


    Спасибо за совет. Я ни в коем случае не говорю,что уход от СТЕ ускорит работу запроса к вьюхе. Возможно, она коряво написана. Пытаюсь разобраться с планом выполнения запроса, нихрена не понимаю) Clustered Index Seek 94%. ещё 4% Index Seek (по полю Level). Но что можно оптимизировать и как, пока не допёр. Стараюсь, читаю сейчас BOL по производительности, т.к. оптимизация - это моя основна работа теперь))))
    17 май 13, 12:22    [14310752]     Ответить | Цитировать Сообщить модератору
     Re: Реализация рекурсии без CTE  [new]
    Сергей Викт.
    Member

    Откуда: Москва
    Сообщений: 888
    Гость333
    В 2008.
    Извиняюсь, ошибся.

    Гость333
    Это наследие со старых версий MSSQL. Указанное примечание про ORDER BY появилось, начиная с версии 2005. До версии 2005 так можно было создавать отсортированные представления.


    Теперь понятно, т.е. по факту из представлений ORDER BY можно смело удалять.:) Т.к. минимальная версия из используемых серверов 2005. Спасибо.
    17 май 13, 12:25    [14310771]     Ответить | Цитировать Сообщить модератору
     Re: Реализация рекурсии без CTE  [new]
    Winnipuh
    Member [заблокирован]

    Откуда: Київ
    Сообщений: 10428
    MasterZiv
    Сергей Викт.,

    Я разбирался (переделывал) недавно с "рекурсией", правда, в оракле. И не смотря на то, что там есть все возможности с CTE, сделано было и до, и после БЕЗ него.

  • Вариант 1, как было ДО. Очень просто. Геренировался динамически текст запроса на нужную глубину вложенности (она имеет небольшой максимум по постановке задачи, типа -- 5 максимум, больше не бывает).
  • Вариант 2 -- как стало. Тоже очень просто -- создаётся временная табличка, в неё закидываются начальные записи -- основа (база) рекурсии, затем в цикле в запросе на базе записей от предыдущего шага в ту же таблицу добавляются новые записи запросом,
    и так пока рекурсия может продолжаться.

    Рекомендую из двух второй вариант.

    Но мне опять таки НЕ ПОНЯТНО, если у тебя возможность использовать CTE есть, почему ты от неё отказываешься. Т.е. ты не понял проблему, но почему-то считаешь, что если сделать по-другому, то магическим образом всё станет быстро и хорошо.


  • я так понял ему надо во вью, вариант 2 вряд ли пройдет.
    17 май 13, 12:30    [14310823]     Ответить | Цитировать Сообщить модератору
     Re: Реализация рекурсии без CTE  [new]
    Сергей Викт.
    Member

    Откуда: Москва
    Сообщений: 888
    Winnipuh
    я так понял ему надо во вью, вариант 2 вряд ли пройдет.

    Да. Но я хочу узнать у программистов WMS, сможем ли отцепиться от представления. Ибо что-то мне не нравится:))
    17 май 13, 12:36    [14310873]     Ответить | Цитировать Сообщить модератору
     Re: Реализация рекурсии без CTE  [new]
    Зайцев Фёдор
    Member

    Откуда: Лужки
    Сообщений: 5308
    Сергей Викт.
    Winnipuh
    я так понял ему надо во вью, вариант 2 вряд ли пройдет.

    Да. Но я хочу узнать у программистов WMS, сможем ли отцепиться от представления. Ибо что-то мне не нравится:))

    Отказ от CTE - такая важная задача, что "отцепиться от представления" уже не кажется дорогой ценой?
    17 май 13, 13:11    [14311203]     Ответить | Цитировать Сообщить модератору
     Re: Реализация рекурсии без CTE  [new]
    Сергей Викт.
    Member

    Откуда: Москва
    Сообщений: 888
    Зайцев Фёдор,

    дело не в этом. Просто хочется добиться оптимального результата, поэтому рассматриваю все возможные варианты.
    17 май 13, 13:27    [14311376]     Ответить | Цитировать Сообщить модератору
     Re: Реализация рекурсии без CTE  [new]
    MasterZiv
    Member

    Откуда: Питер
    Сообщений: 34658
    Winnipuh

    я так понял ему надо во вью, вариант 2 вряд ли пройдет.


    Ну кто его знает...
    17 май 13, 16:58    [14313209]     Ответить | Цитировать Сообщить модератору
     Re: Реализация рекурсии без CTE  [new]
    MasterZiv
    Member

    Откуда: Питер
    Сообщений: 34658
    Сергей Викт.
    Зайцев Фёдор,

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


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

    Это никак почти не влияет на оптимальность, но даёт тебе гибкость в дальнейшем в использовании средств.
    17 май 13, 17:00    [14313228]     Ответить | Цитировать Сообщить модератору
     Re: Реализация рекурсии без CTE  [new]
    Сергей Викт.
    Member

    Откуда: Москва
    Сообщений: 888
    MasterZiv, не могу не согласиться. Лично мне больше симпатизирует процедурный подход, но т.к. это делалось до меня задолго, то я стараюсь как могу оптимизировать то, что уже имеется.
    17 май 13, 17:03    [14313243]     Ответить | Цитировать Сообщить модератору
     Re: Реализация рекурсии без CTE  [new]
    Mnior
    Member

    Откуда: Кишинёв
    Сообщений: 6723
    Сергей Викт.
    [src sql]
    SELECT TOP (100) PERCENT ElementID, ParentID, PGCode, NameRus, NameEng, ShortName, [Level], ExtBrandID, Position,PositionPG
    FROM General.dbo.t_Goods_Elements AS ge
    ORDER BY ElementID, [Level]
    ORDER BY с TOP не игнорируется.
    Явно видно что это костыль, чтобы не исправлять клиента.
    Этой VIEW нельзя пользоваться, ибо даже при получении одной строки (по условию) будет считываться дохрена. В принципе, в основном тормоза тут. Эта вью только для того злополучного вызова с клиента, которые не делает сортировку у себя.
    Используйте или таблицу напрямую или создайте другую вью.

    Сергей Викт.
    От обратного: приложение писалось постепенно, начиная с SQLServer 7. Там типа hierrarcyid еще не было. Вроде он с 2005 появился (могу ошибаться).
    Ну так кто мешает исправлять дальше?

    Где ответ на этот вопрос?:
    Mnior
    Я одного немогу понять, [level] вычисляется при вставке в таблицу, а CSKUId уже нет. Почему?
    17 май 13, 20:16    [14314075]     Ответить | Цитировать Сообщить модератору
     Re: Реализация рекурсии без CTE  [new]
    Mnior
    Member

    Откуда: Кишинёв
    Сообщений: 6723
    MasterZiv
    Хочешь добиться оптимального результата -- замени запрос вызовом хранимой процедуры,
    Ага, у него же эта вью (с этой колонкой) используется в 100500 местах. Это точно не оптимально. Х с ней что надо перелопатить код, вы придумайте процедуру так, что можно быть свободно впендёрить её в эти 100500 мест и работало оптимально.
    Там join так, там join сяк, там одна строка, там много, а там вообще ногу сломит.
    Давайте каждый пук обвернём в процедуру?! Фтопку, процедурномыслие.

    Ту решается вопрос вычислять или хранить.
    Коль говорится что используется часто, то лучше сторить в табле (индексе).

    MasterZiv
    Лично мне больше симпатизирует процедурный подход
    Кто-то "каждый пук обворачивает в процедуру", а кто-то нет (alexeyvg).
    Когда я пишу первым "Фтопку, процедурномыслие", то вторые начинаю наезжать и говорить что у меня "иной подход".
    Задолбало мягко говоря, вот это чтение по диагонали. Дьявол в деталях.
    17 май 13, 20:33    [14314103]     Ответить | Цитировать Сообщить модератору
     Re: Реализация рекурсии без CTE  [new]
    ziktuw
    Member

    Откуда:
    Сообщений: 3552
    Сергей Викт.
    Можно ли как-то реализовать рекурсию в представлении без использования CTE??
    Просто запрос к данному представлению работает достаточно долго (около 3 секунд).
    Я попробовал без рекурсии хард кодингом сделать, тупо для каждого уровня по очереди перебирая отдельными селектами, отрабатывает за 0,3 сек. Но я сделал только дла уровней от 0 до 7) А надо ещё до -50 уровня, а это уже мясо))
    Заранее спасибо!


    Решал подобную задачу так. Рекурсию заменил циклом вставки в табличную переменную. Каждая итерация цикла - один инсерт всех записей одного уровня, пока не останется потомков (буквально проверкой @@ROWCOUNT на ноль от предыдущей операции вставки). Таким образом в цикле случалось столько итераций, сколько максимально уровней дерева в структуре. Этот код вставил в табличную функцию. Вызов табличной функции вставил в требуемый VIEW. В итоге получил увеличение производительности по сравнению с рекурсивной CTE в три раза и не нарушил интерфейс - та же VIEW с тем же выходным набором данных.
    18 май 13, 11:22    [14315671]     Ответить | Цитировать Сообщить модератору
     Re: Реализация рекурсии без CTE  [new]
    Mnior
    Member

    Откуда: Кишинёв
    Сообщений: 6723
    Глеб,

    Сергей Викт., пропустите мимо.
    Решений много, проблема в выборе лучшего.
    18 май 13, 16:12    [14316503]     Ответить | Цитировать Сообщить модератору
     Re: Реализация рекурсии без CTE  [new]
    Гость333
    Member

    Откуда:
    Сообщений: 3683
    Mnior
    Этой VIEW нельзя пользоваться, ибо даже при получении одной строки (по условию) будет считываться дохрена.

    Откуда вдруг такой вывод?
    19 май 13, 03:39    [14318103]     Ответить | Цитировать Сообщить модератору
    Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
    Все форумы / Microsoft SQL Server Ответить