Путешествия по дереву (mssql2005)

Обсуждение технических вопросов работы с системами управления базами данных (СУБД), работе с языком SQL и скриптовыми языками.
Ответить
Аватара пользователя
Александр
Активный участник
Сообщения: 1652
Зарегистрирован: 24 авг 2006, 08:06
Используемое ПО: Lotsia PDM PLUS
Откуда: 55.745578,37.665825

Путешествия по дереву (mssql2005)

Сообщение Александр »

Впервые столкнувшись с рекурсией 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 ... ?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)
Уровень администрирования - Альтернативный

Аватара пользователя
Александр
Активный участник
Сообщения: 1652
Зарегистрирован: 24 авг 2006, 08:06
Используемое ПО: Lotsia PDM PLUS
Откуда: 55.745578,37.665825

Re: Путешествия по дереву (mssql2005)

Сообщение Александр »

работающий код здесь 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)
Уровень администрирования - Альтернативный

Аватара пользователя
Anderyt
Активный участник
Сообщения: 777
Зарегистрирован: 15 июл 2004, 13:15
Используемое ПО: Lotsia PDM PLUS
Откуда: Тюмень
Контактная информация:

Re: Путешествия по дереву (mssql2005)

Сообщение Anderyt »

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

Код: Выделить всё

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
процедура возвращает ИД дочерних объектов всех уровней в том порядке, в котором они идут в дереве (то есть в порядке вложенности). первая колонка - пробелы, их число равно номеру уровня вложенности. их можно оставить, чтобы анализировать, а можно просто сразу заменить на пустые строки и обрабатывать только ИД.
лучше день потерять, потом за пять минут долететь!
Ответить