Путешествия по дереву (mssql2005)
Добавлено: 24 фев 2010, 14:17
Впервые столкнувшись с рекурсией http://www.lplm.ru/phpBB2/viewtopic.php?f=18&t=612 мы активно полюбили это дело и используем в базе в качестве основы для отчетов и форм на отчетах, весь вопрос в быстродействии - может кто из специалистов подскажет как ускориться?
есть дерево по типу связи 1 (дерево) есть список Мнемо объектов по которым нужно пробежать (например строка вида 'деп,подч,плрб,req')
есть базовая функцияесть функция для секции отбора in - преобразование строки в таблицу (взята отсюда http://www.itshop.ru/Massivy-i-Spiski-v ... ?print=yes) выглядит так
проблема в следующем - разбор строки Мнемо для удобства/скорости работы
Вариант 1й
передать строку напрямую в виде переменных для секции in т.е. в виде вроде по скорости in раскладывается на or и все более менее - но непонятно какой глубины рекурсию мы захотим получить 2 уровня или 25 - тогда переменных не напасешся, да и пустых значений '' для мелких уровней может быть много...
Вариант 2й
преобразовать строку Мнемо в массив и работать в секции in с ним как сейчас
со всем массивом
или с текущим по рекурсии элементом массива
тут все вроде круто - но функция преобразования часто вызывается, кроме того при таком подходе мы можем сразу выбрать фильтр для результата по последнему значению Мнемо... т.е. типа еще пару вызовов плюс
никто не подскажет как оптимизировать данные вызовы преобразования строки в таблицу - чтобы один раз и для всей функции рекурсии?
другими словами что лучше
- сделать функцию по преобразовании строки в таблицу в теле самой базовой функции, тогда как лучше - временной таблицей или переменной типа таблица?
или
- сделать в теле функции - временную таблицу или переменную типа таблица и заполнять ее этой функцией один раз наверху?
есть дерево по типу связи 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
Код: Выделить всё
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'))
Вариант 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)))
другими словами что лучше
- сделать функцию по преобразовании строки в таблицу в теле самой базовой функции, тогда как лучше - временной таблицей или переменной типа таблица?
или
- сделать в теле функции - временную таблицу или переменную типа таблица и заполнять ее этой функцией один раз наверху?