Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 Ускорение работы запросов или рекурсивный вызов функций  [new]
mkliver
Member

Откуда:
Сообщений: 41
Привет.
Беда приключилась - я столкнулся с 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-тую дорогу. Скорость меня очень не устраиват. Прикрепляю к вопросу план выполнения, правда не знаю насколько он информативен.
И я хочу спросить это вообще верный путь делать такой запрос или есть более оптимальный вариант? Буду блпгодарен за совет да помощь.

К сообщению приложен файл (plan.sqlplan - 68Kb) cкачать
23 фев 13, 22:37    [13971598]     Ответить | Цитировать Сообщить модератору
 Re: Ускорение работы запросов или рекурсивный вызов функций  [new]
Гость333
Member

Откуда:
Сообщений: 3683
mkliver,

Первое, что приходит в голову — выгрузить результат выражения by_segment во временную таблицу, и в выражении roads_by_segment работать уже с этой таблицей. По плану запроса видно, что by_segment у вас вычисляется 77 раз — хотя достаточно одного раза.
25 фев 13, 11:00    [13975131]     Ответить | Цитировать Сообщить модератору
 Re: Ускорение работы запросов или рекурсивный вызов функций  [new]
mkliver
Member

Откуда:
Сообщений: 41
Гость333,

А не подскажите как это сделать? Я сам не силен в sql.
25 фев 13, 11:25    [13975263]     Ответить | Цитировать Сообщить модератору
 Re: Ускорение работы запросов или рекурсивный вызов функций  [new]
mkliver
Member

Откуда:
Сообщений: 41
Гость333,

Я имею в виду. Создам я #временную таблицу вставлю туда строки из by_segment. Но как ее использовать дальше?
25 фев 13, 11:32    [13975294]     Ответить | Цитировать Сообщить модератору
 Re: Ускорение работы запросов или рекурсивный вызов функций  [new]
Glory
Member

Откуда:
Сообщений: 104760
mkliver
Но как ее использовать дальше?

Так же, как и любую другую таблицу
25 фев 13, 11:33    [13975304]     Ответить | Цитировать Сообщить модератору
 Re: Ускорение работы запросов или рекурсивный вызов функций  [new]
mkliver
Member

Откуда:
Сообщений: 41
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
),
25 фев 13, 11:37    [13975321]     Ответить | Цитировать Сообщить модератору
 Re: Ускорение работы запросов или рекурсивный вызов функций  [new]
Glory
Member

Откуда:
Сообщений: 104760
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 ....
25 фев 13, 11:39    [13975335]     Ответить | Цитировать Сообщить модератору
 Re: Ускорение работы запросов или рекурсивный вызов функций  [new]
Гость333
Member

Откуда:
Сообщений: 3683
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;
25 фев 13, 11:41    [13975344]     Ответить | Цитировать Сообщить модератору
 Re: Ускорение работы запросов или рекурсивный вызов функций  [new]
mkliver
Member

Откуда:
Сообщений: 41
Glory,

И уже в остальном запросе использовать #temptable?
25 фев 13, 11:41    [13975350]     Ответить | Цитировать Сообщить модератору
 Re: Ускорение работы запросов или рекурсивный вызов функций  [new]
Мистер Хенки
Member

Откуда: канализация
Сообщений: 6615
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
25 фев 13, 11:41    [13975351]     Ответить | Цитировать Сообщить модератору
 Re: Ускорение работы запросов или рекурсивный вызов функций  [new]
mkliver
Member

Откуда:
Сообщений: 41
Мистер Хенки,

Спасибо за такой ответ.
Правда у меня ошибка возника вот здесь

автор
(rn,roadId)


Строка 6 неправильный синтаксис возле "("
25 фев 13, 11:46    [13975379]     Ответить | Цитировать Сообщить модератору
 Re: Ускорение работы запросов или рекурсивный вызов функций  [new]
mkliver
Member

Откуда:
Сообщений: 41
mkliver,

Так ну эту ошибку устранил.

Но появилась другая в строке

автор
roads_by_segment as (


Строка 22 .Неправильный ситкакситс
25 фев 13, 11:53    [13975447]     Ответить | Цитировать Сообщить модератору
 Re: Ускорение работы запросов или рекурсивный вызов функций  [new]
mkliver
Member

Откуда:
Сообщений: 41
Гость333,

Круто! То, что нужно!
А тогда вопрос такой. Смогу ли я сделать из этого запроса представление или таблицу дописав сверху CREATE VIEW Road_View AS (...)
? Или тут сиснтаксис будет похитрее?
25 фев 13, 11:55    [13975462]     Ответить | Цитировать Сообщить модератору
 Re: Ускорение работы запросов или рекурсивный вызов функций  [new]
Glory
Member

Откуда:
Сообщений: 104760
mkliver
Смогу ли я сделать из этого запроса представление или таблицу дописав сверху CREATE VIEW Road_View AS (...)
?

Нет

mkliver
Или тут сиснтаксис будет похитрее?

Достаточно открыть синтаксис CREATE VIEW в хелпе, чтобы узнать все его возможности
25 фев 13, 11:57    [13975479]     Ответить | Цитировать Сообщить модератору
 Re: Ускорение работы запросов или рекурсивный вызов функций  [new]
mkliver
Member

Откуда:
Сообщений: 41
mkliver,
Ни как не могу найти кнопки редактирования сообщений(

Как оказалось представления так не сделать. Ругается на использование временных таблиц в запросе. А возможно ли создать тогда таблицу из результатов этого запроса?
25 фев 13, 11:58    [13975488]     Ответить | Цитировать Сообщить модератору
 Re: Ускорение работы запросов или рекурсивный вызов функций  [new]
Glory
Member

Откуда:
Сообщений: 104760
mkliver
А возможно ли создать тогда таблицу из результатов этого запроса?

Ну так вы уже создали

mkliver
Ни как не могу найти кнопки редактирования сообщений(

Ее нет.
25 фев 13, 12:01    [13975507]     Ответить | Цитировать Сообщить модератору
 Re: Ускорение работы запросов или рекурсивный вызов функций  [new]
mkliver
Member

Откуда:
Сообщений: 41
Glory,

автор
Ну так вы уже создали


Ну тогда я немного запутался. В запросе создается временная таблица, которая живет только во время запроса, или я ошибаюсь. Беда в том, что я могу использовать только представления и обычные таблицы в стороннем ПО.
25 фев 13, 12:06    [13975550]     Ответить | Цитировать Сообщить модератору
 Re: Ускорение работы запросов или рекурсивный вызов функций  [new]
Glory
Member

Откуда:
Сообщений: 104760
mkliver
Ну тогда я немного запутался. В запросе создается временная таблица, которая живет только во время запроса, или я ошибаюсь.

От типа таблицы синтаксис не меняется
25 фев 13, 12:07    [13975559]     Ответить | Цитировать Сообщить модератору
 Re: Ускорение работы запросов или рекурсивный вызов функций  [new]
Гость333
Member

Откуда:
Сообщений: 3683
mkliver
Ни как не могу найти кнопки редактирования сообщений(

Нам, простолюдинам, такой кнопки тут не положено, она есть только у модераторов ;-)


mkliver
А возможно ли создать тогда таблицу из результатов этого запроса?

Можно, либо так:
with roads_by_segment as (
...
)
select * into #Таблица from roads_by_segment;

Либо так:
create table #Таблица ( ... );

with roads_by_segment as (
...
)
insert #Таблица (road_id, Name, the_geom, rn, segment_id)
select road_id, Name, the_geom, rn, segment_id
from roads_by_segment;
25 фев 13, 12:08    [13975570]     Ответить | Цитировать Сообщить модератору
 Re: Ускорение работы запросов или рекурсивный вызов функций  [new]
Гость333
Member

Откуда:
Сообщений: 3683
mkliver
временная таблица, которая живет только во время запроса, или я ошибаюсь

Она живёт либо до выполнения DROP TABLE, либо до закрытия коннекции, в которой была создана.

Если нужна постоянная таблица, то тогда можно складывать результаты всех запросов в одну таблицу, в которой будет отдельный столбец с идентификатором запроса. Сторонее ПО может выполнять запросы вида "select * from ResultTable where Request_ID = ..."?
25 фев 13, 12:15    [13975623]     Ответить | Цитировать Сообщить модератору
 Re: Ускорение работы запросов или рекурсивный вызов функций  [new]
mkliver
Member

Откуда:
Сообщений: 41
Гость333,

Я избрал второй путь и вот что вышло.

автор
CREATE table RoadTezt(
road_id character varying(100),
name character varying(100),
the_geom geometry,
rn int,
segment_id character varying(100)
);
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
)
Insert RoadTezt(road_id,name,the_geom,rn,segment_id)
select road_id,name,the_geom,rn,segment_id from roads_by_segment;


И выдает ошибку

(строк обработано 147)
Сообщение 8152, уровень 16, состояние 14, строка 26
Символьные или бвоичные данные могут быть усечены
Выполнение данной инструкции было прервано.

Я опять что то не так сделал?(
25 фев 13, 12:18    [13975647]     Ответить | Цитировать Сообщить модератору
 Re: Ускорение работы запросов или рекурсивный вызов функций  [new]
Glory
Member

Откуда:
Сообщений: 104760
mkliver
Я опять что то не так сделал?(

Пытаетесь добавить в поле(я) больше данных, чем оно может вместить
25 фев 13, 12:25    [13975704]     Ответить | Цитировать Сообщить модератору
 Re: Ускорение работы запросов или рекурсивный вызов функций  [new]
mkliver
Member

Откуда:
Сообщений: 41
Гость333,

Да, простые запросы оно делает, если что то под сторонним ПО понимается Gеoserver.
Но проблема, что как раз сложить то в таблицу я ничего и не могу ошибки вылазят.
25 фев 13, 12:26    [13975716]     Ответить | Цитировать Сообщить модератору
 Re: Ускорение работы запросов или рекурсивный вызов функций  [new]
mkliver
Member

Откуда:
Сообщений: 41
Glory,

Расширил текстовые поля до 250 символов - не помогло(
25 фев 13, 12:27    [13975728]     Ответить | Цитировать Сообщить модератору
 Re: Ускорение работы запросов или рекурсивный вызов функций  [new]
Гость333
Member

Откуда:
Сообщений: 3683
mkliver
У меня есть база:

автор
Track

Segment_ID(character),
Track (geometry)

Segment_ID

Segment_ID(character),
Road_ID(character)

Road

Road_ID(character),
Name(character)

Вот эти "character" можно расписать подробнее — по сколько символов отведено на каждый?
25 фев 13, 12:29    [13975743]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить