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

Откуда: Россия, Ростовская область, г. Таганрог
Сообщений: 1295
with cte as
(
select 1 pk, cast(null as int) fkOwner, 1 as fkMainOwner
union all
select 2, 1, 1
union all
select 3, 1, 1
union all
select 4, 2, 1
union all
select 5, 2, 1

--Новый справочник
union all
select 6, null, 6
union all
select 7, 6, 6
union all
select 8, 7, 6
)
,

cteTree as (
select t.pk,
       t.fkOwner 
  from cte t
 where t.pk = 2 --Стартуем эту ветку

 union all

select c.pk,
       c.fkOwner
  from cteTree t
 inner
  join cte c
    on t.pk = c.fkOwner
)

select *
  from cteTree

Доброе время суток всем.
Я привёл упрощённый пример справочника в котором заложена иерархия.

Становится задача получать значения с любого уровня вложенности (не по fkMainOwner).
В МсСКЛ мне известен метод только с рекурсивным ЦТЕ, но с ним у меня проблема на условии on t.pk = c.fkOwner и с итерационным характером самой выборки. План запроса значение t.pk воспринимает как значение скалярной функции. Тут я как бы интуитивно алгоритм работы понимаю, но всё же ...

Вообщем работает долго. А у меня много групп, в каждой группе более одного узла из этого деверва. Т. е. для каждой группы мне надо строить дерево вниз с нескольких корней. Хочу что бы это работало быстро.

Если кто осведомлён в предметной области, то это лего объяснить на примере справочника диагнозов МКБ и отчётов по заболеваемости там где много групп.

Мне нужно если не "connect by prior", то эффективный метод написания быстрого запроса для любого уровня вложенности.

Уже две пагубные мысли витают в голове.
1) для каждого значения дерева хрнаить все его родительские
2) по-другому проверять (начиная с нижнего уровня) в какую группу попадает экземпляр сущности с нужным диагнозом, но тогда для каждого поставленного диагноза надо будет идти по дереву вверх и проверять не попадает ли новый уровень в нужную мне группу.


Пока запрос мой выглядит примерно так
--declare @fkMkb uniqueidentifier;
--set @fkMkb = dbo.fnCommonDictionaryGetPkBySyn('MKB10');

with cteRoot as (

select t.pk as pkGroup,
       gv.fkCommonDictionary
       --Возвращаем нужные группы из справочника групп для конкретного отчёта
  from dbo.fnCommonDictionaryGetTreeByPkOrSyn(null, 'GROUP_REPORT_MKB_TEST16VN', null) t --Сделать параметром
 
 inner
  join dbo.tGroup g with(nolock)
    on g.fkCommonDictionary = t.pk
   and g.fkOwner is null
  
 inner
  join dbo.tGroup gV with(nolock)
    on gV.fkOwner = g.pk
    
 where t.level <> 1 --Сделать параметром

 union all
 
 select r.pkGroup,
        cd.pk as fkCommonDictionary
   from cteRoot r
  
  inner
   join dbo.tCommonDictionary cd with(nolock)
     on cd.fkOwner = r.fkCommonDictionary 
    --and cd.fkMainOwner = @fkMkb
     
 )
/* 
select r.*
  from cteRoot r
*/

select r.pkGroup,
       count(1) as mkbCount
  from cteRoot r
 group 
    by r.pkGroup

Прям перед постом он начал работать быстро, благодаря тому что сервер "разгрузился", но всё же хочется что бы работал как обычный запрос с join-ами, использовал индексы, а не процессор.
3 авг 11, 14:53    [11064868]     Ответить | Цитировать Сообщить модератору
 Re: Иерархия, СТЕ, Оптимизация (индексы, структура)  [new]
hierarchyid
Guest
NIIIK,

использовать тип данных hierarchyid http://msdn.microsoft.com/ru-ru/library/bb677290.aspx

и хранить иерархию в этом типе данных
3 авг 11, 15:21    [11065191]     Ответить | Цитировать Сообщить модератору
 Re: Иерархия, СТЕ, Оптимизация (индексы, структура)  [new]
NIIIK
Member

Откуда: Россия, Ростовская область, г. Таганрог
Сообщений: 1295
Круто, спасибо!

Хорошо что теперь 2008ой на проекте.
3 авг 11, 19:06    [11067247]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить