Ускорение работы запросов или рекурсивный вызов функций

Привет.
Беда приключилась - я столкнулся с MS SQL Server R2 2008, который до этого ни разу не видел в глаза.

У меня есть база:

автор
Track Segment_ID(character), Track (geometry) Segment_ID Segment_ID(character), Road_ID(character) Road Road_ID(character), Name(character)

И задача такая. Есть дороги, которые хранятся в таблице Road. Дороги состоят из сегментов, которые хранятся в таблице Track. Эти две таблицы соединены посредством таблицы Segment_ID. Дак вот я хочу собрать сегменты одно дороги в единое целое. Я имею в виду, что хочу соеденить геометрии всех сегментов дороги (поле Track.Track) в единую дорогу.
То есть на вывод я хочу получить:
Road.Name
Road.Road_ID
Road (geometry)

Для соединения геомтрии нашел замечательную функцию geom1.STUnion(geom2).
Нашел один из способов сделать это:

автор

with by_segment as ( SELECT Road.Road_ID, GEOMETRY::STGeomFromText(Track.Track.STAsText(),4326) as the_geom, row_number() over (partition by road_id order by Segment_Id) as [rn], count(*) over (partition by road_id) as [c] FROM dbo.Road LEFT JOIN Segment_ID ON Road.Road_ID = Segment_ID.Road_ID LEFT JOIN Track ON Segment_ID.Segment_ID = Track.Segment_ID ), roads_by_segment as ( select road_id, the_geom, [rn], [c] from by_segment where [rn] = 1 union all select [a].road_id, [a].the_geom.STUnion([b].the_geom), [b].[rn], [b].[c] from by_segment as [a] inner join roads_by_segment as [b] on [a].segment_id = b.[segment_id] and [a].[rn] = [b].[rn]+1 ) select * from roads_by_segment

Это вроде как делает то что я хочу. Но работает чертовски медленно. Допустим у меня есть 76 дорог и за первые 5секунд выполнения запроса я получаю 75 дорог и еще через пять минут 76-тую дорогу. Скорость меня очень не устраиват. Прикрепляю к вопросу план выполнения, правда не знаю насколько он информативен.
И я хочу спросить это вообще верный путь делать такой запрос или есть более оптимальный вариант? Буду блпгодарен за совет да помощь.

mkliver,

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

Гость333,

А не подскажите как это сделать? Я сам не силен в sql.

Гость333,

Я имею в виду. Создам я #временную таблицу вставлю туда строки из by_segment. Но как ее использовать дальше?

mkliver
Но как ее использовать дальше?

Так же, как и любую другую таблицу

Glory,

Ну тогда точнее вопрос такой:
Вот эту часть я должен использовать в создании временной таблицы. А как тогда временную таблицу в остальной части использовать?

автор
with by_segment as ( SELECT Road.Road_ID, GEOMETRY::STGeomFromText(Track.Track.STAsText(),4326) as the_geom, row_number() over (partition by road_id order by Segment_Id) as [rn], count(*) over (partition by road_id) as [c] FROM dbo.Road LEFT JOIN Segment_ID ON Road.Road_ID = Segment_ID.Road_ID LEFT JOIN Track ON Segment_ID.Segment_ID = Track.Segment_ID ),

SELECT
Road.Road_ID,
GEOMETRY::STGeomFromText(Track.Track.STAsText(),4326) as the_geom,
row_number() over (partition by road_id order by Segment_Id) as [rn],
count(*) over (partition by road_id) as [c]
INTO #temptable
FROM dbo.Road
LEFT JOIN Segment_ID ON Road.Road_ID = Segment_ID.Road_ID
LEFT JOIN Track ON Segment_ID.Segment_ID = Track.Segment_ID

;WITH ....

mkliver,

Примерно так (запрос взят из приложенного плана, т.к. в первом посте текст запроса вроде бы нерабочий)

IF OBJECT_ID('tempdb..#by_segment') IS NOT NULL
    DROP TABLE #by_segment;

SELECT  
    Road.Road_ID,
    Road.Name,
    Segment_ID.Segment_ID,
    GEOMETRY::STGeomFromText(Track.Track.STAsText(),4326) as the_geom,
    row_number() over (partition by Road.road_id order by Segment_id.Segment_Id) as [rn]
    -- count(*) over (partition by road.road_id) as [c]
INTO #by_segment
FROM dbo.Road
    LEFT JOIN Segment_ID  ON Road.Road_ID = Segment_ID.Road_ID
    LEFT JOIN Track ON Segment_ID.Segment_ID = Track.Segment_ID;

-- Может, надо создать другой индекс, или вообще без индекса будет хорошо
CREATE INDEX i ON #by_segment(rn, segment_id);

with roads_by_segment as (
    select 
        road_id,
        Name,
        the_geom,
        [rn],
        --[c],
        [segment_id]
    from #by_segment
    where [rn] = 1

    union all

    select 
        [a].road_id,
        [a].Name,
        [a].the_geom.STUnion([b].the_geom),
        [b].[rn],
        -- [b].[c],
        [b].[segment_id]
    from #by_segment as [a]
         inner join roads_by_segment as [b]
            on [a].segment_id = [b].[segment_id]
               and [a].[rn] = [b].[rn]+1
)
select * from roads_by_segment;

Glory,

И уже в остальном запросе использовать #temptable?

rom by_segment as [a]
inner join roads_by_segment as [b]
on [a].segment_id = b.[segment_id]

всеж таки наверное по road_id соединение.

IF OBJECT_ID('tempdb..#by_segment') IS NOT NULL
begin
	DROP TABLE #by_segment
end

create #by_segment(roadId int not null, the_geom GEOMETRY, rn int not null,c int not null primary key clustrered(rn,roadId))

insert into #by_segment(
     roadId , 
     the_geom , 
     rn ,
     c)
SELECT
Road.Road_ID,
GEOMETRY::STGeomFromText(Track.Track.STAsText(),4326) as the_geom,
row_number() over (partition by road_id order by Segment_Id) as [rn],
count(*) over (partition by road_id) as [c]
FROM dbo.Road
LEFT JOIN Segment_ID ON Road.Road_ID = Segment_ID.Road_ID
LEFT JOIN Track ON Segment_ID.Segment_ID = Track.Segment_ID


roads_by_segment as (
select
roadid,
the_geom,
[rn],
[c]
from #by_segment
where [rn] = 1

union all

select
[a].roadid,
[a].the_geom.STUnion([b].the_geom),
[b].[rn],
[b].[c]
from #by_segment as [a]
inner join roads_by_segment as [b]
on [a].roadid = b.roadid
and [a].[rn] = [b].[rn]+1
)

select * from roads_by_segment