Lotsia PLM: Форум по семейству систем PLM/PDM/TDM/ERP/Workflow

Для специалистов по внедрению систем, профессиональных администраторов и пользователей.
Текущее время: 19 ноя 2018, 21:38

Часовой пояс: UTC + 3 часа




Начать новую тему Ответить на тему  [ Сообщений: 3 ] 
Автор Сообщение
 Заголовок сообщения: Путешествия по дереву (mssql2005)
СообщениеДобавлено: 24 фев 2010, 14:17 
Не в сети
Активный участник
Аватара пользователя

Зарегистрирован: 24 авг 2006, 08:06
Сообщения: 1646
Откуда: 55.745578,37.665825
Благодарил (а): 8 раз.
Поблагодарили: 3 раз.
Впервые столкнувшись с рекурсией http://www.lplm.ru/phpBB2/viewtopic.php?f=18&t=612 мы активно полюбили это дело и используем в базе в качестве основы для отчетов и форм на отчетах, весь вопрос в быстродействии - может кто из специалистов подскажет как ускориться?
есть дерево по типу связи 1 (дерево) есть список Мнемо объектов по которым нужно пробежать (например строка вида 'деп,подч,плрб,req')
есть базовая функция
Код:
ALTER  function [LSDBO].[Ric_Get_objChild]
/*24022010*/
/*
прочитать дочерние объекты родителя @ObjID глубина поиска @Mnemo)
*/
 
        (@ObjID numeric(18,0)=0,         --ID объекта
         @Mnemo varchar(4000)           --Мнемо (перечисление всех нужных Mnemo объектов в порядке поиска)
         )

Returns @Ret table                      --вернуть таблицу формата
--(состав колонок определяется общим синтаксисом многооператорной функции возвращающей таблицу, т.е. все что есть в запросах) 
        (id numeric(18,0),            --ID объекта запрос (имя для запуска из отчета)
       Description varchar(255),      --Описание объекта
         Cd datetime,               --Время создания объекта
         Mnemo varchar(4)--,               --Мнемо объекта
         --Level int      --уровень уровня
         )
as
Begin
--MSSQL 2005 Обобщенное табличное выражение для рекурсии (Rec)
WITH Rec (id, description, cd, Mnemo)--,level)
AS (
Select rw.id,
       rw.description,
       rw.cd,
       tw.mnemo--,
       --0
  From lsdbo.object_type tw inner join lsdbo.object_reference rw
       on rw.type_id = tw.id  inner join
       lsdbo.tree_link tl on rw.id = tl.link_id and tl.link_filial_id = 1 
 Where tl.parent_id =@ObjID
       and tw.Mnemo in (select str from LSDBO.Ric_StringToTable(@Mnemo, DEFAULT)/*where listpos=1*/)

  UNION ALL

Select rw.id,
       rw.description,
       rw.cd,
       tw.mnemo--,
       --Rec.level+1
  From lsdbo.object_type tw inner join lsdbo.object_reference rw
       on rw.type_id = tw.id inner join
       lsdbo.tree_link tl on rw.id = tl.link_id and tl.link_filial_id = 1 inner join Rec
       on Rec.id = tl.parent_id
       and tw.Mnemo in (select str from LSDBO.Ric_StringToTable(@Mnemo, DEFAULT)/*where listpos=Rec.level+2*/)
)
Insert @Ret
--Рекурсия
Select *
  from Rec
 Where Rec.Mnemo=(select str 
                    from LSDBO.Ric_StringToTable(@Mnemo, DEFAULT)
                   where listpos=(select max(listpos)
                    from LSDBO.Ric_StringToTable(@Mnemo, DEFAULT)))
 Return
End
есть функция для секции отбора in - преобразование строки в таблицу (взята отсюда http://www.itshop.ru/Massivy-i-Spiski-v-SQL-Server/l9i22765?print=yes) выглядит так
Код:
 ALTER FUNCTION [LSDBO].[Ric_StringToTable]
/*
/*24022010*/
Возвращаемая данной функцией таблица состоит их трех полей: listpos , str и nstr .
Два последних поля содержат значение одного и того же элемента списка,
первое в виде varchar,
а второе - nvarchar
*/
                 (@list      ntext,
                  @delimiter nchar(1) = N',')
 
       RETURNS @tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,
                            str varchar(4000),
                            nstr nvarchar(2000)) AS

   BEGIN
      DECLARE @pos      int,
              @textpos  int,
              @chunklen smallint,
              @tmpstr   nvarchar(4000),
              @leftover nvarchar(4000),
              @tmpval   nvarchar(4000)

      SET @textpos = 1
      SET @leftover = ''
      WHILE @textpos <= datalength(@list) / 2
      BEGIN
         SET @chunklen = 4000 - datalength(@leftover) / 2
         SET @tmpstr = @leftover + substring(@list, @textpos, @chunklen)
         SET @textpos = @textpos + @chunklen

         SET @pos = charindex(@delimiter, @tmpstr)

         WHILE @pos > 0
         BEGIN
            SET @tmpval = ltrim(rtrim(left(@tmpstr, charindex(@delimiter, @tmpstr) - 1)))
            INSERT @tbl (str, nstr) VALUES(@tmpval, @tmpval)
            SET @tmpstr = substring(@tmpstr, @pos + 1, len(@tmpstr))
            SET @pos = charindex(@delimiter, @tmpstr)
         END

         SET @leftover = @tmpstr
      END

      INSERT @tbl(str, nstr) VALUES (ltrim(rtrim(@leftover)), ltrim(rtrim(@leftover)))
   RETURN
   END
проблема в следующем - разбор строки Мнемо для удобства/скорости работы
Вариант 1й
передать строку напрямую в виде переменных для секции in т.е. в виде
Код:
and tw.Mnemo in (@Mnemo01='деп',@Mnemo02='подч',@Mnemo03='плрб',@Mnemo04='req'))
вроде по скорости in раскладывается на or и все более менее - но непонятно какой глубины рекурсию мы захотим получить 2 уровня или 25 - тогда переменных не напасешся, да и пустых значений '' для мелких уровней может быть много...
Вариант 2й
преобразовать строку Мнемо в массив и работать в секции in с ним как сейчас
со всем массивом
Код:
and tw.Mnemo in (select str from LSDBO.Ric_StringToTable(@Mnemo, DEFAULT)

или с текущим по рекурсии элементом массива
Код:
and tw.Mnemo = (select str from LSDBO.Ric_StringToTable(@Mnemo, DEFAULT) where listpos=Rec.level+2)

тут все вроде круто - но функция преобразования часто вызывается, кроме того при таком подходе мы можем сразу выбрать фильтр для результата по последнему значению Мнемо... т.е. типа еще пару вызовов плюс
Код:
--Рекурсия
Select *
  from Rec
 Where Rec.Mnemo=(select str 
                    from LSDBO.Ric_StringToTable(@Mnemo, DEFAULT)
                   where listpos=(select max(listpos)
                    from LSDBO.Ric_StringToTable(@Mnemo, DEFAULT)))

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

_________________

Софт - RicCRM<<LotsiaPDM(4.40)<<MsSQL(5/8)
Уровень администрирования - Альтернативный



Вернуться к началу
 Профиль  
Ответить с цитатой  
 Заголовок сообщения: Re: Путешествия по дереву (mssql2005)
СообщениеДобавлено: 15 апр 2010, 14:27 
Не в сети
Активный участник
Аватара пользователя

Зарегистрирован: 24 авг 2006, 08:06
Сообщения: 1646
Откуда: 55.745578,37.665825
Благодарил (а): 8 раз.
Поблагодарили: 3 раз.
работающий код здесь http://www.lplm.ru/phpBB2/viewtopic.php?f=18&t=624

ps
круто - использовать форум как записную книжку самое то - всегда знаешь где найти :wink: :wink: :wink: :wink: :wink: и запоминать не нужно :wink:

_________________

Софт - RicCRM<<LotsiaPDM(4.40)<<MsSQL(5/8)
Уровень администрирования - Альтернативный



Вернуться к началу
 Профиль  
Ответить с цитатой  
 Заголовок сообщения: Re: Путешествия по дереву (mssql2005)
СообщениеДобавлено: 03 авг 2010, 14:26 
Не в сети
Активный участник
Аватара пользователя

Зарегистрирован: 15 июл 2004, 13:15
Сообщения: 777
Откуда: Тюмень
Благодарил (а): 6 раз.
Поблагодарили: 8 раз.
тоже хотелось бы поделиться..
с рекурсией мы давно сталкивались, и все неудачно - понимаем, что она помогла бы, но реализовать не могли..
а смотря на предложения Александра мне становилось еще страшнее.. потому что вообще непонятно..
и вот недавно раскопали пример в инете, переложили его на структуру Лоции (сотрудница моя делала) - и вот что получилось:
Код:
USE [party]
GO
/****** Object:  StoredProcedure [LSDBO].[custom_proc_get_all_child_ids]    Script Date: 08/03/2010 14:07:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      <Osokina I.>
-- Create date: <29.07.2010,>
-- Description:   <Выдает дочерние объекты, отсортированные по уровню>
-- =============================================
CREATE PROCEDURE [LSDBO].[custom_proc_get_all_child_ids]
   -- Add the parameters for the stored procedure here
   @@parent_id numeric
AS
BEGIN
   with
children (link_id, level1)
   as (select tree.link_id,0
   from LSDBO.Tree_Link_view Tree
   where tree.parent_id= @@parent_id
   and tree.link_type_id = 1
union all
   select tree.link_id, level1+1
   from LSDBO.Tree_Link_view Tree
   inner join children
   on children.link_id=tree.parent_id
   where tree.link_type_id = 1)
select space(level1),link_id
from children
order by level1
END

процедура возвращает ИД дочерних объектов всех уровней в том порядке, в котором они идут в дереве (то есть в порядке вложенности). первая колонка - пробелы, их число равно номеру уровня вложенности. их можно оставить, чтобы анализировать, а можно просто сразу заменить на пустые строки и обрабатывать только ИД.

_________________
лучше день потерять, потом за пять минут долететь!


Вернуться к началу
 Профиль  
Ответить с цитатой  
Показать сообщения за:  Поле сортировки  
Начать новую тему Ответить на тему  [ Сообщений: 3 ] 

Часовой пояс: UTC + 3 часа


Кто сейчас на конференции

Сейчас этот форум просматривают: нет зарегистрированных пользователей и гости: 6


Вы не можете начинать темы
Вы не можете отвечать на сообщения
Вы не можете редактировать свои сообщения
Вы не можете удалять свои сообщения
Вы не можете добавлять вложения

Найти:
Перейти:  
cron
Создано на основе phpBB® Forum Software © phpBB Group
Русская поддержка phpBB