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

Откуда: Republic of Belarus
Сообщений: 451
Здравствуйте уважаемые форумчане.
Помогите найти решение вот такой вот задачи:

СУБД MS SQL Server 2005

Исходные данные:
Именються несколько таблиц
- Navigations (в ней в режиме реального времени поступают навигационные данные о месте нахождения и движении объекта)
- Zones (в ней храняться данные о зонах, на которые разбит город, и их точках с координамами )

Данные в таблице Navigations поступают каждые 10 сек от каждого объекта. Объектов около 300.

Зачача:
Необходимо в режиме реального времени получать следующие данные:
- какой объект находиться в какой зоне;
- в какое время объект переместился с одной зоны в другую.

Соответственно, все должно работать быстро.

Я раньше с "задачами в реальном времени" не сталкивался и опыта в этом у меня мало. Поиск смотрел, но ничего похожего не нашел (может не знал где искать :) ).
Варианта решения я нашел пока 2.

Вариант 1:
Реализовать решение с помошью хранимой процедуры, которая будет вызываться планировщиком задач например раз в 5 минут.

Вариант 2:
Реализовать решение с помошью тригера AFTER INSERT в таблице Navigations.

Однако, есть некоторые нюансы. Например, данные могут приходить с опазданием, т.е. пакет №1 со старыми навигационными данными пришел позже пакета №2. В связи с этим, вариант 2 будет работать не корректно. Варитант 1 был бы более лучше, т.к. данные можно обрабатывать сразу пачками и в отсортированном виде, однако, тут мы приобретаем несвоевременное предоставление информацию пользователю. Я думаю, что скорее всего информацию пользователю надо будет предоставлять ежеминутно. В связи с этим, будет идти постоянная нагрузка на сервер.

Подскажите плиз, как быть :)
10 июл 09, 12:56    [7401503]     Ответить | Цитировать Сообщить модератору
 Re: Помогите найти решение  [new]
LexMinsk
Member

Откуда: Republic of Belarus
Сообщений: 451
Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) Nov 24 2008 13:01:59 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
10 июл 09, 15:19    [7402542]     Ответить | Цитировать Сообщить модератору
 Re: Помогите найти решение  [new]
vino
Member

Откуда:
Сообщений: 1191
LexMinsk,
Каков объем записи, вcтавляемой в таблицу Navigations каждые 10 сек от каждого объекта?
Имеем 1800 независимых (?) вставок в минуту. Это уже может стать немалой нагрузкой при параллельных запросах, но ее, естественно, можно оптимизировать индексами, а в зависимости от частот и глубин выборки может понадобится и партиционирование разрастающейся Navigations.

У вас, как я понял. в реальном времени нужно только последнее состояние и его логично реализовать тригером, обновляющим в отдельной маленькой таблице примерно 300 записей (пусть Positions). Когда поступает опоздавший пакет №1, который обязан идентифицироваться по монотонному ключу, он просто отбрасывается, так как в Positions уже указано более новое состояние. А лог Navigations остается для истории
Соответственно, все будет работать быстро, когда таблица Positions лочится строго по записи каждого объекта, а количество объектов (записей) относительно постоянно.

A вот это к реальному времени не имеет отношения и будет грузить машину из-за постоянного ковыряния в огромной (со временем) и под INSERT-нагрузкой таблице Navigations:
LexMinsk
Вариант 1:
Реализовать решение с помошью хранимой процедуры, которая будет вызываться планировщиком задач например раз в 5 минут.

С другой стороны
LexMinsk
...скорее всего информацию пользователю надо будет предоставлять ежеминутно.
когда идет речь о таком временном лаге, то проблем быть не должно даже в Варианте1 (с соответствующими оговорками)
10 июл 09, 15:25    [7402578]     Ответить | Цитировать Сообщить модератору
 Re: Помогите найти решение  [new]
Критик
Member

Откуда: Москва / Калуга
Сообщений: 33338
Блог
1. индексы с Navigations убрать, тут хранить скажем координаты только за день
2. текущее положение - обновление триггером отдельной таблички
3. в какое время объект переместился с одной зоны в другую - делать в конце дня, импортировав накопленные данные в Navigations_историю (расчеты вести над этой табличкой)

как то так, впрочем могут быть какие-то тонкости, которые знает только автор)
10 июл 09, 15:38    [7402665]     Ответить | Цитировать Сообщить модератору
 Re: Помогите найти решение  [new]
Crimean
Member

Откуда:
Сообщений: 13148
рассмотреть 2008 - там есть новые типы данных для хранения и ОБРАБОТКИ координат
может существенно помочь
10 июл 09, 17:49    [7403432]     Ответить | Цитировать Сообщить модератору
 Re: Помогите найти решение  [new]
LexMinsk
Member

Откуда: Republic of Belarus
Сообщений: 451
Все вы верно ребята сказали, спс.
Есть у меня таблица Navigation_buffer, где и идет обработка поступающих данных навигации. После обработки данные удаляються, а Navigations типа архива.
Меня вот что смушало. Будет ли все нормально работать, если каждые делать секунд обрабатывать 300-500 записей из таблицы Navigations_buffer?

Navigations_buffer
ID_Navigations BigInt PK
ID_Car int FK
Lat real
Lon real
Speed real
Course real
Height real
Recv_Date real
Dictance real
Power real
Lat_Rad real
Lon_Rad real
11 июл 09, 00:30    [7404533]     Ответить | Цитировать Сообщить модератору
 Re: Помогите найти решение  [new]
aleks2
Guest
Вариант 3.

Постоянно работающая процедура обработки. И джоб, перезапускающий оную, ежели сдохла.

Обрабатывать пачками не очень большого размера. С закрытием транзакции после обработки пачки. Приоритет обработки - последние поступившие - первыми.

Для уменьшения нагрузки - можно изредка притормаживать в цикле обработки WAITFOR.
11 июл 09, 18:52    [7405146]     Ответить | Цитировать Сообщить модератору
 Re: Помогите найти решение  [new]
LexMinsk
Member

Откуда: Republic of Belarus
Сообщений: 451
Народ еще такой вопрос. Реализовал я решение по поиску объекта, т.е. в какой зоне находиться объект в определенное время. Есть три вида зоны 1 - точка с радиусом, 2 - многоугольник, 3- ломаная линия, определенной толщины. На каждый тип зоны я сделал отлельную функцию проверки.
Получилось типа того:
Таблица ZONES (зоны)
ID_ZONE PK
NAME_ZONE
SIZE
...
Таблица REGIONS (регионы зон с координатами)
ID_REG PK
ID_ZONE FK
LAT
LON
NUM
...
Таблица JOBS (задания маршрута движения объекта (автомашины))
ID_JOB PK
ID_CAR
ID_ZONE FK
START_DATE
END_DATE
...
Основной запрос:
SET STATISTICS TIME ON
GO
SET STATISTICS PROFILE ON
GO
DECLARE @X REAL
DECLARE @Y REAL
SET @X = 0.9412705
SET @Y = 0.4793433
SELECT T1.NAMEZONE, 
       T1.ID_ZONE, 
       T1.TYPEZONE, 
       T1.SIZE, 
       T2.ID_REG, 
       T2.LAT, 
       T2.LON, 
       T3.ID_JOB, 
       T3.ID_CAR,
       T3.START_DATE,
       T3.END_DATE,
       T3.TIMEOUT,
       T3.OUT_DATE,
       'RESULT' = CASE WHEN T1.TYPEZONE = 1 THEN dbo.DistanceAB (@X, @Y, T2.LAT, T2.LON, T1.SIZE)
                       WHEN T1.TYPEZONE = 2 THEN dbo.AInPoly (@X, @Y, T1.ID_ZONE)
                       WHEN T1.TYPEZONE = 3 THEN dbo.DistanceA2piece (@X, @Y, T1.ID_ZONE, T1.SIZE)
                  END
FROM   ZONES AS T1, 
       REGIONS AS T2, 
       JOBS AS T3
WHERE  T1.ID_ZONE = T2.ID_ZONE AND 
       T1.ID_ZONE = T3.ID_ZONE  

GO
SET STATISTICS TIME OFF
GO
SET STATISTICS PROFILE OFF
План выполнения:
31;1;SELECT T1.NAMEZONE,
T1.ID_ZONE,
T1.TYPEZONE,
T1.SIZE,
T2.ID_REG,
T2.LAT,
T2.LON,
T3.ID_JOB,
T3.ID_CAR,
T3.START_DATE,
T3.END_DATE,
T3.TIMEOUT,
T3.OUT_DATE,
'RESULT' = CASE WHEN T1.TYPEZONE = 1 THEN dbo.DistanceAB (@X, @Y, T2.LAT, T2.LON, T1.SIZE)
WHEN T1.TYPEZONE = 2 THEN dbo.AInPoly (@X, @Y, T1.ID_ZONE)
WHEN T1.TYPEZONE = 3 THEN dbo.DistanceA2piece (@X, @Y, T1.ID_ZONE, T1.SIZE)
END
FROM ZONES AS T1,
REGIONS AS T2,
JOBS AS T3
WHERE T1.ID_ZONE = T2.ID_ZONE AND
T1.ID_ZONE = T3.ID_ZONE;301;1;0;NULL;NULL;NULL;NULL;1;NULL;NULL;NULL;0,02811405;NULL;NULL;SELECT;0;NULL
31;1; |--Compute Scalar(DEFINE:([Expr1006]=CASE WHEN [Navigation].[dbo].[ZONES].[TYPEZONE] as [T1].[TYPEZONE]=(1) THEN [Navigation].[dbo].[DistanceAB]([@X],[@Y],[Navigation].[dbo].[REGIONS].[LAT] as [T2].[LAT],[Navigation].[dbo].[REGIONS].[LON] as [T2].[LON],CONVERT_IMPLICIT(real(24),[Navigation].[dbo].[ZONES].[SIZE] as [T1].[SIZE],0)) ELSE CASE WHEN [Navigation].[dbo].[ZONES].[TYPEZONE] as [T1].[TYPEZONE]=(2) THEN CONVERT_IMPLICIT(real(24),[Navigation].[dbo].[AInPoly]([@X],[@Y],[Navigation].[dbo].[ZONES].[ID_ZONE] as [T1].[ID_ZONE]),0) ELSE CASE WHEN [Navigation].[dbo].[ZONES].[TYPEZONE] as [T1].[TYPEZONE]=(3) THEN [Navigation].[dbo].[DistanceA2piece]([@X],[@Y],[Navigation].[dbo].[ZONES].[ID_ZONE] as [T1].[ID_ZONE],CONVERT_IMPLICIT(real(24),[Navigation].[dbo].[ZONES].[SIZE] as [T1].[SIZE],0)) ELSE NULL END END END));301;2;1;Compute Scalar;Compute Scalar;DEFINE:([Expr1006]=CASE WHEN [Navigation].[dbo].[ZONES].[TYPEZONE] as [T1].[TYPEZONE]=(1) THEN [Navigation].[dbo].[DistanceAB]([@X],[@Y],[Navigation].[dbo].[REGIONS].[LAT] as [T2].[LAT],[Navigation].[dbo].[REGIONS].[LON] as [T2].[LON],CONVERT_IMPLICIT(real(24),[Navigation].[dbo].[ZONES].[SIZE] as [T1].[SIZE],0)) ELSE CASE WHEN [Navigation].[dbo].[ZONES].[TYPEZONE] as [T1].[TYPEZONE]=(2) THEN CONVERT_IMPLICIT(real(24),[Navigation].[dbo].[AInPoly]([@X],[@Y],[Navigation].[dbo].[ZONES].[ID_ZONE] as [T1].[ID_ZONE]),0) ELSE CASE WHEN [Navigation].[dbo].[ZONES].[TYPEZONE] as [T1].[TYPEZONE]=(3) THEN [Navigation].[dbo].[DistanceA2piece]([@X],[@Y],[Navigation].[dbo].[ZONES].[ID_ZONE] as [T1].[ID_ZONE],CONVERT_IMPLICIT(real(24),[Navigation].[dbo].[ZONES].[SIZE] as [T1].[SIZE],0)) ELSE NULL END END END);[Expr1006]=CASE WHEN [Navigation].[dbo].[ZONES].[TYPEZONE] as [T1].[TYPEZONE]=(1) THEN [Navigation].[dbo].[DistanceAB]([@X],[@Y],[Navigation].[dbo].[REGIONS].[LAT] as [T2].[LAT],[Navigation].[dbo].[REGIONS].[LON] as [T2].[LON],CONVERT_IMPLICIT(real(24),[Navigation].[dbo].[ZONES].[SIZE] as [T1].[SIZE],0)) ELSE CASE WHEN [Navigation].[dbo].[ZONES].[TYPEZONE] as [T1].[TYPEZONE]=(2) THEN CONVERT_IMPLICIT(real(24),[Navigation].[dbo].[AInPoly]([@X],[@Y],[Navigation].[dbo].[ZONES].[ID_ZONE] as [T1].[ID_ZONE]),0) ELSE CASE WHEN [Navigation].[dbo].[ZONES].[TYPEZONE] as [T1].[TYPEZONE]=(3) THEN [Navigation].[dbo].[DistanceA2piece]([@X],[@Y],[Navigation].[dbo].[ZONES].[ID_ZONE] as [T1].[ID_ZONE],CONVERT_IMPLICIT(real(24),[Navigation].[dbo].[ZONES].[SIZE] as [T1].[SIZE],0)) ELSE NULL END END END;1;0;1E-07;103;0,02811405;[T1].[ID_ZONE], [T1].[NAMEZONE], [T1].[TYPEZONE], [T1].[SIZE], [T2].[ID_REG], [T2].[LAT], [T2].[LON], [T3].[ID_JOB], [T3].[ID_CAR], [T3].[OUT_DATE], [T3].[TIMEOUT], [T3].[START_DATE], [T3].[END_DATE], [Expr1006];NULL;PLAN_ROW;0;1
31;1; |--Nested Loops(Inner Join, OUTER REFERENCES:([T3].[ID_ZONE]));301;3;2;Nested Loops;Inner Join;OUTER REFERENCES:([T3].[ID_ZONE]);NULL;1;0;4,18E-06;99;0,02811395;[T1].[ID_ZONE], [T1].[NAMEZONE], [T1].[TYPEZONE], [T1].[SIZE], [T2].[ID_REG], [T2].[LAT], [T2].[LON], [T3].[ID_JOB], [T3].[ID_CAR], [T3].[OUT_DATE], [T3].[TIMEOUT], [T3].[START_DATE], [T3].[END_DATE];NULL;PLAN_ROW;0;1
31;1; |--Hash Match(Inner Join, HASH:([T3].[ID_ZONE])=([T2].[ID_ZONE]));301;4;3;Hash Match;Inner Join;HASH:([T3].[ID_ZONE])=([T2].[ID_ZONE]);NULL;1;0;0,01817497;64;0,02482667;[T2].[ID_REG], [T2].[LAT], [T2].[LON], [T3].[ID_JOB], [T3].[ID_CAR], [T3].[ID_ZONE], [T3].[OUT_DATE], [T3].[TIMEOUT], [T3].[START_DATE], [T3].[END_DATE];NULL;PLAN_ROW;0;1
3;1; | |--Clustered Index Scan(OBJECT:([Navigation].[dbo].[JOBS].[PK_JOB] AS [T3]));301;5;4;Clustered Index Scan;Clustered Index Scan;OBJECT:([Navigation].[dbo].[JOBS].[PK_JOB] AS [T3]);[T3].[ID_JOB], [T3].[ID_CAR], [T3].[ID_ZONE], [T3].[OUT_DATE], [T3].[TIMEOUT], [T3].[START_DATE], [T3].[END_DATE];3;0,003125;0,0001603;51;0,0032853;[T3].[ID_JOB], [T3].[ID_CAR], [T3].[ID_ZONE], [T3].[OUT_DATE], [T3].[TIMEOUT], [T3].[START_DATE], [T3].[END_DATE];NULL;PLAN_ROW;0;1
74;1; | |--Clustered Index Scan(OBJECT:([Navigation].[dbo].[REGIONS].[PK_REG] AS [T2]));301;6;4;Clustered Index Scan;Clustered Index Scan;OBJECT:([Navigation].[dbo].[REGIONS].[PK_REG] AS [T2]);[T2].[ID_REG], [T2].[ID_ZONE], [T2].[LAT], [T2].[LON];74;0,003125;0,0002384;23;0,0033634;[T2].[ID_REG], [T2].[ID_ZONE], [T2].[LAT], [T2].[LON];NULL;PLAN_ROW;0;1
31;31; |--Clustered Index Seek(OBJECT:([Navigation].[dbo].[ZONES].[PK_ZONES] AS [T1]), SEEK:([T1].[ID_ZONE]=[Navigation].[dbo].[JOBS].[ID_ZONE] as [T3].[ID_ZONE]) ORDERED FORWARD);301;8;3;Clustered Index Seek;Clustered Index Seek;OBJECT:([Navigation].[dbo].[ZONES].[PK_ZONES] AS [T1]), SEEK:([T1].[ID_ZONE]=[Navigation].[dbo].[JOBS].[ID_ZONE] as [T3].[ID_ZONE]) ORDERED FORWARD;[T1].[ID_ZONE], [T1].[NAMEZONE], [T1].[TYPEZONE], [T1].[SIZE];1;0,003125;0,0001581;46;0,0032831;[T1].[ID_ZONE], [T1].[NAMEZONE], [T1].[TYPEZONE], [T1].[SIZE];NULL;PLAN_ROW;0;1

Функции:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[DistanceAB] (@X1 REAL,  
                                    @Y1 REAL, 
                                    @X2 REAL, 
                                    @Y2 REAL,
                                    @ZONESIZE REAL)
RETURNS REAL
AS
BEGIN
-- АЛГОРИТМ ВЫЧИСЛЯЕТ РАССТОЯНИЕ МЕЖДУ ДВУМЯ ТОЧКАМИ A(X1, Y1), B(X2, Y2) НА ПЛОСКОСТИ
-- L = ((x2-x1)^2 + (y2-y1^))^(1/2)
-- ВОЗВРАЩАЕТ 1 ЕСЛИ ТОЧКА НАХОДИТЬСЯ В ЗОНЕ, И 0 В ПРОТИВНОМ СЛУЧАЕ
DECLARE @RESULT REAL
 SET @RESULT = SQRT(POWER((@X2-@X1), 2) + POWER((@Y2-@Y1), 2))
 IF @RESULT <= @ZONESIZE
  SET @RESULT = 1
 ELSE
  SET @RESULT = 0
RETURN @RESULT
END -- [dbo].[DistanceAB]

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [dbo].[DistanceA2piece] (@X REAL, 
                                         @Y REAL,
                                         @ID_ZONE INT,
                                         @ZONESIZE REAL)
RETURNS REAL
AS
BEGIN
-- АЛГОРИТМ ВЫЧИСЛЯЕТ РАССТОЯНИЕ МЕЖДУ ТОЧКОЙ A(X, Y) И ОТРЕЗКОМ P(X1, Y1; X2, Y2)
-- хз как работает, но работает, проверено :) ЧТО ТО С ВЕКТОРНЫМИ И СКАЛЯРНЫМИ ПРОИЗВЕДЕНИЯМИ
-- ВОЗВРАЩАЕТ 1 ЕСЛИ ТОЧКА НАХОДИТЬСЯ В ЗОНЕ И 0 В ПРОТИВНОМ СЛУЧАЕ
DECLARE @RESULT REAL
DECLARE @EPS REAL 
DECLARE @T REAL
DECLARE @W REAL
SET @EPS = 0.0000000000000000000000000000000000001
DECLARE CRS_ZONE CURSOR FOR SELECT T1.LAT, T1.LON, T2.LAT, T2.LON
                            FROM REGIONS AS T1 JOIN 
                                 REGIONS AS T2 ON (T2.NUM = (SELECT TOP 1 T3.NUM 
                                                             FROM REGIONS AS T3 
                                                             WHERE (T1.NUM < T3.NUM) AND 
                                                                   (T1.ID_ZONE = T3.ID_ZONE)
                                                             ORDER BY T3.NUM)) AND 
                                                  (T1.ID_ZONE = T2.ID_ZONE)
                            WHERE T1.ID_ZONE = @ID_ZONE
-- ПЕРЕМЕННЫЕ-КООРДИНАТЫ НАЧАЛА И КОНЦА РЕБРА
DECLARE @T1LAT REAL
DECLARE @T1LON REAL
DECLARE @T2LAT REAL
DECLARE @T2LON REAL
OPEN CRS_ZONE
-- ЧИТАЕМ ПЕРВУЮ ЗАПИСЬ, 1-Я ЗАПИСЬ БУДЕТ ВСЕГДА, Т.КЮ В СЛУЧАЕ 0 Ф-ЦИЯ НЕ ВЫЗОВЕТСЯ
FETCH NEXT
FROM CRS_ZONE
INTO @T1LAT, @T1LON, @T2LAT, @T2LON
WHILE (@@FETCH_STATUS = 0)
 BEGIN
  SET @RESULT = 0
  IF (((@X-@T1LAT)*(@T2LAT-@T1LAT) + (@Y-@T1LON)*(@T2LON-@T1LON))*((@X-@T2LAT)*(@T2LAT-@T1LAT) + (@Y-@T2LON)*(@T2LON-@T1LON)) > -@EPS)
   BEGIN
    SET @T = POWER((@X-@T1LAT), 2) + POWER((@Y-@T1LON), 2)
    SET @W = POWER((@X-@T2LAT), 2) + POWER((@Y-@T2LON), 2)
    IF (@W < @T)
     SET @T = @W
    END
    ELSE
     SET @T = POWER((@X-@T1LAT)*(@T2LON-@T1LON)-(@Y-@T1LON)*(@T2LAT-@T1LAT), 2)/(POWER((@T2LAT-@T1LAT), 2) + POWER((@T2LON-@T1LON), 2))
  SET @RESULT = SQRT(@T)
  IF @RESULT <= @ZONESIZE
   BEGIN
    SET @RESULT = 1
    BREAK
   END
  ELSE
   SET @RESULT = 0
  -- СЛЕДУЮЩАЯ ИТЕРАЦИЯ
  FETCH NEXT
  FROM CRS_ZONE
  INTO @T1LAT, @T1LON, @T2LAT, @T2LON
 END -- {WHILE (@@FETCH_STATUS = 0)}
CLOSE CRS_ZONE
DEALLOCATE CRS_ZONE
RETURN @RESULT
END -- [dbo].[LengthAB]

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [dbo].[AInPoly] (@X REAL,
                                 @Y REAL,
                                 @ID_ZONE INT
                                 )
RETURNS INT
AS
-- АЛГОРИТМ ВЫЧИСЛЯЕТ ПРИНАДЛЕЖНОСТЬ ТОЧКИ МНОГОУГОЛЬНИКУ - ЗОНЕ
-- ВОЗВРАЩАЕТ 1 ЕСЛИ ТОЧКА НАХОДИТЬСЯ В ЗОНЕ И 0 В ПРОТИВНОМ СЛУЧАЕ
BEGIN
DECLARE @RESULT INT -- РЕЗУЛЬТАТ
DECLARE @EPS REAL -- МАЛАЯ КОНСТАНТА
SET @EPS = 0.0000000000000000000000000000000000001
DECLARE @C INT -- КОЛИЧЕСТВО ПЕРЕСЕЧЕНИЙ
SET @C = 0
DECLARE CRS_ZONE CURSOR FOR SELECT T1.LAT, T1.LON, T2.LAT, T2.LON
                           FROM REGIONS AS T1 JOIN 
                                REGIONS AS T2 ON (T2.NUM = (SELECT TOP 1 T3.NUM 
                                                            FROM REGIONS AS T3 
                                                            WHERE (T1.NUM < T3.NUM) AND 
                                                                  (T1.ID_ZONE = T3.ID_ZONE)
                                                            ORDER BY T3.NUM)) AND 
                                                 (T1.ID_ZONE = T2.ID_ZONE)
                           WHERE T1.ID_ZONE = @ID_ZONE
                           UNION ALL
                           -- СОЕДИНЯЕТ ПЕРВУЮ ТОЧКУ С ПОСЛЕДНЕЙ
                           SELECT T1.LAT, T1.LON, T2.LAT, T2.LON
                           FROM REGIONS AS T1 JOIN 
                                REGIONS AS T2 ON (T1.NUM = (SELECT MIN(NUM) FROM REGIONS)) AND
                                                 (T2.NUM = (SELECT MAX(NUM) FROM REGIONS)) AND 
                                                 (T1.ID_ZONE = T2.ID_ZONE)
                           WHERE T1.ID_ZONE = @ID_ZONE
-- ПЕРЕМЕННЫЕ-КООРДИНАТЫ НАЧАЛА И КОНЦА РЕБРА МНОГОУГОЛЬНИКА
DECLARE @T1LAT REAL
DECLARE @T1LON REAL
DECLARE @T2LAT REAL
DECLARE @T2LON REAL
-- ОТКРЫВАЕМ КУРСОР
OPEN CRS_ZONE
-- ЧИТАЕМ ПЕРВУЮ ЗАПИСЬ, 1-Я ЗАПИСЬ БУДЕТ ВСЕГДА, Т.КЮ В СЛУЧАЕ 0 Ф-ЦИЯ НЕ ВЫЗОВЕТСЯ
FETCH NEXT
FROM CRS_ZONE
INTO @T1LAT, @T1LON, @T2LAT, @T2LON
WHILE (@@FETCH_STATUS = 0)
 BEGIN
  -- ОБРАБАТЫВАЕМ ДАННЫЕ
  -- ЕСЛИ РЕБРО ГОРИЗОНТАЛЬНО, ТО ЕГО ПРОПУСКАЕМ И ПЕРЕХОДИМ К СЛЕДУЮЩЕЙ ИТЕРАЦИИ
  IF (@X = @T1LAT) AND (@X = @T2LAT) 
   BEGIN
    FETCH NEXT
    FROM CRS_ZONE
    INTO @T1LAT, @T1LON, @T2LAT, @T2LON
    CONTINUE  
   END
  -- ЕСЛИ ПЕРЕСЕКАЕТ ЛУЧ L ИЛИ ИЛИ КАСАЕТЬСЯ НИЖНИМ КОНЦОМ СЛЕВА ОТ ТОЧКИ
  IF ((((@T1LON <= @Y) AND (@Y < @T2LON)) OR ((@T2LON <= @Y) AND (@Y < @T1LON))) AND 
      (@X > (@T2LAT - @T1LAT)*(@Y - @T2LON)/(@T2LON - @T1LON) + @T1LAT))
   SET @C = @C + 1
  -- СЛЕДУЮЩАЯ ИТЕРАЦИЯ
  FETCH NEXT
  FROM CRS_ZONE
  INTO @T1LAT, @T1LON, @T2LAT, @T2LON
 END -- {WHILE (@@FETCH_STATUS = 0)}
CLOSE CRS_ZONE
DEALLOCATE CRS_ZONE
-- ФОРМИРУЕМ РЕЗУЛЬТАТ
SET @RESULT = @C & 1
RETURN @RESULT
END -- [dbo].[AInPoly]

Добрые люди, подскажите, как тут сделать чтобы все побыстрее работало. А то вчера прочитал исследование Александра на счет скалярных функций в запросе, так и расстроился весь :).
С MS SQL Server работаю около месяца, так что сильно не пинать :).
17 июл 09, 10:16    [7427534]     Ответить | Цитировать Сообщить модератору
 Re: Помогите найти решение  [new]
LexMinsk
Member

Откуда: Republic of Belarus
Сообщений: 451
План:
хз, как его тут выкладывать :)
[FIX]
31;1;SELECT T1.NAMEZONE,
T1.ID_ZONE,
T1.TYPEZONE,
T1.SIZE,
T2.ID_REG,
T2.LAT,
T2.LON,
T3.ID_JOB,
T3.ID_CAR,
T3.START_DATE,
T3.END_DATE,
T3.TIMEOUT,
T3.OUT_DATE,
'RESULT' = CASE WHEN T1.TYPEZONE = 1 THEN dbo.DistanceAB (@X, @Y, T2.LAT, T2.LON, T1.SIZE)
WHEN T1.TYPEZONE = 2 THEN dbo.AInPoly (@X, @Y, T1.ID_ZONE)
WHEN T1.TYPEZONE = 3 THEN dbo.DistanceA2piece (@X, @Y, T1.ID_ZONE, T1.SIZE)
END
FROM ZONES AS T1,
REGIONS AS T2,
JOBS AS T3
WHERE T1.ID_ZONE = T2.ID_ZONE AND
T1.ID_ZONE = T3.ID_ZONE;301;1;0;NULL;NULL;NULL;NULL;1;NULL;NULL;NULL;0,02811405;NULL;NULL;SELECT;0;NULL
31;1; |--Compute Scalar(DEFINE:([Expr1006]=CASE WHEN [Navigation].[dbo].[ZONES].[TYPEZONE] as [T1].[TYPEZONE]=(1) THEN [Navigation].[dbo].[DistanceAB]([@X],[@Y],[Navigation].[dbo].[REGIONS].[LAT] as [T2].[LAT],[Navigation].[dbo].[REGIONS].[LON] as [T2].[LON],CONVERT_IMPLICIT(real(24),[Navigation].[dbo].[ZONES].[SIZE] as [T1].[SIZE],0)) ELSE CASE WHEN [Navigation].[dbo].[ZONES].[TYPEZONE] as [T1].[TYPEZONE]=(2) THEN CONVERT_IMPLICIT(real(24),[Navigation].[dbo].[AInPoly]([@X],[@Y],[Navigation].[dbo].[ZONES].[ID_ZONE] as [T1].[ID_ZONE]),0) ELSE CASE WHEN [Navigation].[dbo].[ZONES].[TYPEZONE] as [T1].[TYPEZONE]=(3) THEN [Navigation].[dbo].[DistanceA2piece]([@X],[@Y],[Navigation].[dbo].[ZONES].[ID_ZONE] as [T1].[ID_ZONE],CONVERT_IMPLICIT(real(24),[Navigation].[dbo].[ZONES].[SIZE] as [T1].[SIZE],0)) ELSE NULL END END END));301;2;1;Compute Scalar;Compute Scalar;DEFINE:([Expr1006]=CASE WHEN [Navigation].[dbo].[ZONES].[TYPEZONE] as [T1].[TYPEZONE]=(1) THEN [Navigation].[dbo].[DistanceAB]([@X],[@Y],[Navigation].[dbo].[REGIONS].[LAT] as [T2].[LAT],[Navigation].[dbo].[REGIONS].[LON] as [T2].[LON],CONVERT_IMPLICIT(real(24),[Navigation].[dbo].[ZONES].[SIZE] as [T1].[SIZE],0)) ELSE CASE WHEN [Navigation].[dbo].[ZONES].[TYPEZONE] as [T1].[TYPEZONE]=(2) THEN CONVERT_IMPLICIT(real(24),[Navigation].[dbo].[AInPoly]([@X],[@Y],[Navigation].[dbo].[ZONES].[ID_ZONE] as [T1].[ID_ZONE]),0) ELSE CASE WHEN [Navigation].[dbo].[ZONES].[TYPEZONE] as [T1].[TYPEZONE]=(3) THEN [Navigation].[dbo].[DistanceA2piece]([@X],[@Y],[Navigation].[dbo].[ZONES].[ID_ZONE] as [T1].[ID_ZONE],CONVERT_IMPLICIT(real(24),[Navigation].[dbo].[ZONES].[SIZE] as [T1].[SIZE],0)) ELSE NULL END END END);[Expr1006]=CASE WHEN [Navigation].[dbo].[ZONES].[TYPEZONE] as [T1].[TYPEZONE]=(1) THEN [Navigation].[dbo].[DistanceAB]([@X],[@Y],[Navigation].[dbo].[REGIONS].[LAT] as [T2].[LAT],[Navigation].[dbo].[REGIONS].[LON] as [T2].[LON],CONVERT_IMPLICIT(real(24),[Navigation].[dbo].[ZONES].[SIZE] as [T1].[SIZE],0)) ELSE CASE WHEN [Navigation].[dbo].[ZONES].[TYPEZONE] as [T1].[TYPEZONE]=(2) THEN CONVERT_IMPLICIT(real(24),[Navigation].[dbo].[AInPoly]([@X],[@Y],[Navigation].[dbo].[ZONES].[ID_ZONE] as [T1].[ID_ZONE]),0) ELSE CASE WHEN [Navigation].[dbo].[ZONES].[TYPEZONE] as [T1].[TYPEZONE]=(3) THEN [Navigation].[dbo].[DistanceA2piece]([@X],[@Y],[Navigation].[dbo].[ZONES].[ID_ZONE] as [T1].[ID_ZONE],CONVERT_IMPLICIT(real(24),[Navigation].[dbo].[ZONES].[SIZE] as [T1].[SIZE],0)) ELSE NULL END END END;1;0;1E-07;103;0,02811405;[T1].[ID_ZONE], [T1].[NAMEZONE], [T1].[TYPEZONE], [T1].[SIZE], [T2].[ID_REG], [T2].[LAT], [T2].[LON], [T3].[ID_JOB], [T3].[ID_CAR], [T3].[OUT_DATE], [T3].[TIMEOUT], [T3].[START_DATE], [T3].[END_DATE], [Expr1006];NULL;PLAN_ROW;0;1
31;1; |--Nested Loops(Inner Join, OUTER REFERENCES:([T3].[ID_ZONE]));301;3;2;Nested Loops;Inner Join;OUTER REFERENCES:([T3].[ID_ZONE]);NULL;1;0;4,18E-06;99;0,02811395;[T1].[ID_ZONE], [T1].[NAMEZONE], [T1].[TYPEZONE], [T1].[SIZE], [T2].[ID_REG], [T2].[LAT], [T2].[LON], [T3].[ID_JOB], [T3].[ID_CAR], [T3].[OUT_DATE], [T3].[TIMEOUT], [T3].[START_DATE], [T3].[END_DATE];NULL;PLAN_ROW;0;1
31;1; |--Hash Match(Inner Join, HASH:([T3].[ID_ZONE])=([T2].[ID_ZONE]));301;4;3;Hash Match;Inner Join;HASH:([T3].[ID_ZONE])=([T2].[ID_ZONE]);NULL;1;0;0,01817497;64;0,02482667;[T2].[ID_REG], [T2].[LAT], [T2].[LON], [T3].[ID_JOB], [T3].[ID_CAR], [T3].[ID_ZONE], [T3].[OUT_DATE], [T3].[TIMEOUT], [T3].[START_DATE], [T3].[END_DATE];NULL;PLAN_ROW;0;1
3;1; | |--Clustered Index Scan(OBJECT:([Navigation].[dbo].[JOBS].[PK_JOB] AS [T3]));301;5;4;Clustered Index Scan;Clustered Index Scan;OBJECT:([Navigation].[dbo].[JOBS].[PK_JOB] AS [T3]);[T3].[ID_JOB], [T3].[ID_CAR], [T3].[ID_ZONE], [T3].[OUT_DATE], [T3].[TIMEOUT], [T3].[START_DATE], [T3].[END_DATE];3;0,003125;0,0001603;51;0,0032853;[T3].[ID_JOB], [T3].[ID_CAR], [T3].[ID_ZONE], [T3].[OUT_DATE], [T3].[TIMEOUT], [T3].[START_DATE], [T3].[END_DATE];NULL;PLAN_ROW;0;1
74;1; | |--Clustered Index Scan(OBJECT:([Navigation].[dbo].[REGIONS].[PK_REG] AS [T2]));301;6;4;Clustered Index Scan;Clustered Index Scan;OBJECT:([Navigation].[dbo].[REGIONS].[PK_REG] AS [T2]);[T2].[ID_REG], [T2].[ID_ZONE], [T2].[LAT], [T2].[LON];74;0,003125;0,0002384;23;0,0033634;[T2].[ID_REG], [T2].[ID_ZONE], [T2].[LAT], [T2].[LON];NULL;PLAN_ROW;0;1
31;31; |--Clustered Index Seek(OBJECT:([Navigation].[dbo].[ZONES].[PK_ZONES] AS [T1]), SEEK:([T1].[ID_ZONE]=[Navigation].[dbo].[JOBS].[ID_ZONE] as [T3].[ID_ZONE]) ORDERED FORWARD);301;8;3;Clustered Index Seek;Clustered Index Seek;OBJECT:([Navigation].[dbo].[ZONES].[PK_ZONES] AS [T1]), SEEK:([T1].[ID_ZONE]=[Navigation].[dbo].[JOBS].[ID_ZONE] as [T3].[ID_ZONE]) ORDERED FORWARD;[T1].[ID_ZONE], [T1].[NAMEZONE], [T1].[TYPEZONE], [T1].[SIZE];1;0,003125;0,0001581;46;0,0032831;[T1].[ID_ZONE], [T1].[NAMEZONE], [T1].[TYPEZONE], [T1].[SIZE];NULL;PLAN_ROW;0;1

[/FIX]
17 июл 09, 10:24    [7427579]     Ответить | Цитировать Сообщить модератору
 Re: Помогите найти решение  [new]
Алексей2003
Member

Откуда: Москва
Сообщений: 5645
2LexMinsk
напишите запрос, который развернет хоть одну из функций. хотябы пробный.

для спящего время бодрствования равносильно сну
17 июл 09, 10:35    [7427657]     Ответить | Цитировать Сообщить модератору
 Re: Помогите найти решение  [new]
LexMinsk
Member

Откуда: Republic of Belarus
Сообщений: 451
Честно говоря, я хз как в запросе функции разворачивать :)
Немного переделал основной запрос и первую функцию

SET STATISTICS TIME ON
GO
SET STATISTICS PROFILE ON
GO
-- ВЫБОРКА ЗОН ПАТРУЛИРОВАНИЯ ДЛЯ ПЛАНА РАССТАНОВКИ
DECLARE @X REAL
DECLARE @Y REAL
SET @X = 1
SET @Y = 1
SELECT T1.NAMEZONE, 
       T1.ID_ZONE, 
       T1.TYPEZONE, 
       T1.SIZE, 
       T2.ID_JOB, 
       T2.ID_CAR,
       T2.START_DATE,
       T2.END_DATE,
       T2.TIMEOUT,
       T2.OUT_DATE,
       'RESULT' = CASE WHEN T1.TYPEZONE = 1 THEN dbo.DistanceAB (@X, @Y, T1.ID_ZONE, T1.SIZE)
                       WHEN T1.TYPEZONE = 2 THEN dbo.AInPoly (@X, @Y, T1.ID_ZONE)
                       WHEN T1.TYPEZONE = 3 THEN dbo.DistanceA2piece (@X, @Y, T1.ID_ZONE, T1.SIZE)
                  END
FROM   ZONES AS T1, 
       JOBS AS T2
WHERE  T1.ID_ZONE = T2.ID_ZONE
       
GO
SET STATISTICS TIME OFF
GO
SET STATISTICS PROFILE OFF

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[DistanceAB] (@X REAL,  
                                   @Y REAL, 
                                   @ID_ZONE REAL,
                                   @ZONESIZE REAL)
RETURNS REAL
AS
BEGIN
-- АЛГОРИТМ ВЫЧИСЛЯЕТ РАССТОЯНИЕ МЕЖДУ ДВУМЯ ТОЧКАМИ A(X1, Y1), B(X2, Y2) НА ПЛОСКОСТИ
-- L = ((x2-x1)^2 + (y2-y1^))^(1/2)
-- ВОЗВРАЩАЕТ 1 ЕСЛИ ТОЧКА НАХОДИТЬСЯ В ЗОНЕ, И 0 В ПРОТИВНОМ СЛУЧАЕ
DECLARE @RESULT REAL
-- ПЕРЕМЕННЫЕ-КООРДИНАТЫ НАЧАЛА И КОНЦА РЕБРА
DECLARE @T1LAT REAL
DECLARE @T1LON REAL

SET @RESULT = 0
-- ДЕЛАЕМ ВЫБОРКУ ДАННЫХ
SELECT @T1LAT = T1.LAT, 
       @T1LON = T1.LON
FROM   REGIONS AS T1 
WHERE  T1.ID_ZONE = @ID_ZONE 
-- ЕСЛИ ДАННЫЕ ЕСТЬ , ТО 
IF (@T1LAT IS NOT NULL) AND (@T1LON IS NOT NULL)
 BEGIN
  SET @RESULT = SQRT(POWER((@T1LAT-@X), 2) + POWER((@T1LON-@Y), 2))
  IF @RESULT <= @ZONESIZE
   SET @RESULT = 1
  ELSE
   SET @RESULT = 0
 END
RETURN @RESULT
END -- [dbo].[DistanceAB]
17 июл 09, 11:27    [7428069]     Ответить | Цитировать Сообщить модератору
 Re: Помогите найти решение  [new]
Алексей2003
Member

Откуда: Москва
Сообщений: 5645
вот так вот сразу хз?
чтобы вытащить из функции обработку, нужно эту обработку реализовать в запросе.
берем функцию [dbo].[DistanceAB]. в ней легче всего реализовать. никаких циклов. 1 выбор данных, и 1 вычисление по этим данным. попробуйте.

для спящего время бодрствования равносильно сну
17 июл 09, 11:37    [7428154]     Ответить | Цитировать Сообщить модератору
 Re: Помогите найти решение  [new]
LexMinsk
Member

Откуда: Republic of Belarus
Сообщений: 451
ПЕРЕДЕЛАЛ:
SET STATISTICS TIME ON
GO
SET STATISTICS PROFILE ON
GO
-- ВЫБОРКА ЗОН ПАТРУЛИРОВАНИЯ ДЛЯ ПЛАНА РАССТАНОВКИ
DECLARE @X REAL
DECLARE @Y REAL
SET @X = 1
SET @Y = 1
SELECT T1.NAMEZONE, 
       T1.ID_ZONE, 
       T1.TYPEZONE, 
       T1.SIZE, 
       T2.ID_JOB, 
       T2.ID_CAR,
       T2.START_DATE,
       T2.END_DATE,
       T2.TIMEOUT,
       T2.OUT_DATE,
       'RESULT' = CASE WHEN T1.TYPEZONE = 1 THEN CASE WHEN EXISTS (SELECT T3.LAT, T3.LON FROM REGIONS AS T3 WHERE  T3.ID_ZONE = T1.ID_ZONE)
                                                      THEN CASE WHEN SQRT(POWER(((SELECT T3.LAT 
                                                                                  FROM REGIONS AS T3 
                                                                                  WHERE  T3.ID_ZONE = T1.ID_ZONE)-@X), 2) + 
                                                                          POWER(((SELECT T3.LON 
                                                                                  FROM REGIONS AS T3 
                                                                                  WHERE  T3.ID_ZONE = T1.ID_ZONE)-@Y), 2)) <= T1.SIZE
                                                                 THEN 1
                                                                 ELSE 0
                                                            END
                                                  END
                       WHEN T1.TYPEZONE = 2 THEN dbo.AInPoly (@X, @Y, T1.ID_ZONE)
                       WHEN T1.TYPEZONE = 3 THEN dbo.DistanceA2piece (@X, @Y, T1.ID_ZONE, T1.SIZE)
                  END
FROM   ZONES AS T1, 
       JOBS AS T2
WHERE  T1.ID_ZONE = T2.ID_ZONE
       
GO
SET STATISTICS TIME OFF
GO
SET STATISTICS PROFILE OFF

результаты:

Запрос с функциями:
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.

(3 row(s) affected)

(5 row(s) affected)

SQL Server Execution Times:
CPU time = 16 ms, elapsed time = 13 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.

Запрос переделанный без функции:
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.

(3 row(s) affected)

(15 row(s) affected)

SQL Server Execution Times:
CPU time = 16 ms, elapsed time = 14 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.


План запроса без функции:

К сообщению приложен файл (QrZoneCar_NO_FUNC.csv - 21Kb) cкачать
17 июл 09, 12:14    [7428429]     Ответить | Цитировать Сообщить модератору
 Re: Помогите найти решение  [new]
LexMinsk
Member

Откуда: Republic of Belarus
Сообщений: 451
Убрал статистику профайлера, так получаеться что запросы одинаково работают:

SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.

(3 row(s) affected)

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 7 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
17 июл 09, 12:23    [7428496]     Ответить | Цитировать Сообщить модератору
 Re: Помогите найти решение  [new]
Алексей2003
Member

Откуда: Москва
Сообщений: 5645
3 подзапроса.. жесть..
делаем из этого 1 запрос:
FROM   ZONES AS T1
  join JOBS AS T2 on T1.ID_ZONE = T2.ID_ZONE
WHERE  T1.TYPEZONE = 1
попробуйте запрос преобразовать так, чтобы был 1 запрос, подсказка. делаем через join таблицы REGIONS


для спящего время бодрствования равносильно сну
17 июл 09, 12:27    [7428524]     Ответить | Цитировать Сообщить модератору
 Re: Помогите найти решение  [new]
йцу1
Guest
и покажи также STATISTICS IO
17 июл 09, 12:35    [7428570]     Ответить | Цитировать Сообщить модератору
 Re: Помогите найти решение  [new]
LexMinsk
Member

Откуда: Republic of Belarus
Сообщений: 451
У меня раньше во FROM основного запроса использовалась тамблица REGIONS и я из нее брал поля. Однако, я ее убрал, в осномном запросе мне данные из нее не нужны. Таким образом основной запрос мне возвращает меньше строк, а значит и функции меньше вызываться будут.
17 июл 09, 13:15    [7428835]     Ответить | Цитировать Сообщить модератору
 Re: Помогите найти решение  [new]
LexMinsk
Member

Откуда: Republic of Belarus
Сообщений: 451
STATISTICS IO

запрос с функцией
Table 'ZONES'. Scan count 0, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'JOBS'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


запрос без функции

Table 'REGIONS'. Scan count 3, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'ZONES'. Scan count 0, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'JOBS'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
17 июл 09, 13:19    [7428868]     Ответить | Цитировать Сообщить модератору
 Re: Помогите найти решение  [new]
Алексей2003
Member

Откуда: Москва
Сообщений: 5645
2LexMinsk
функции не попадают под статистику. проверять нужно на количествах данных из продакшна. если скорость устраивает, то оставлять. если не устраивает - менять.

для спящего время бодрствования равносильно сну
17 июл 09, 13:21    [7428881]     Ответить | Цитировать Сообщить модератору
 Re: Помогите найти решение  [new]
Алексей2003
Member

Откуда: Москва
Сообщений: 5645
объясняю на пальцах:
SELECT T1.NAMEZONE, 
       T1.ID_ZONE, 
       T1.TYPEZONE, 
       T1.SIZE, 
       T2.ID_JOB, 
       T2.ID_CAR,
       T2.START_DATE,
       T2.END_DATE,
       T2.TIMEOUT,
       T2.OUT_DATE,
       case when t3.LAT is not null and t3.lon is not null
           then case when SQRT SQRT(POWER((t3.LAT-@X), 2) + POWER((t3.LON-@Y), 2)) <= t1.SIZE
                           then 1
                           else 0
                  end
           else 0
       end as result
FROM   ZONES AS T1
  join     JOBS AS T2 ON T1.ID_ZONE = T2.ID_ZONE
  left join REGIONS AS T3 on T1.ID_ZONE = T3.ID_ZONE
WHERE  T1.TYPEZONE = 1
делайте также с другими строками.
склеивать будете через UNION ALL по разным типам зон.


для спящего время бодрствования равносильно сну
17 июл 09, 13:25    [7428903]     Ответить | Цитировать Сообщить модератору
 Re: Помогите найти решение  [new]
LexMinsk
Member

Откуда: Republic of Belarus
Сообщений: 451
Уменьшил кол-во подзапросов :)

-- ВЫБОРКА ЗОН ПАТРУЛИРОВАНИЯ ДЛЯ ПЛАНА РАССТАНОВКИ
DECLARE @X REAL
DECLARE @Y REAL
SET @X = 1
SET @Y = 1
SELECT T1.NAMEZONE, 
       T1.ID_ZONE, 
       T1.TYPEZONE, 
       T1.SIZE, 
       T2.ID_JOB, 
       T2.ID_CAR,
       T2.START_DATE,
       T2.END_DATE,
       T2.TIMEOUT,
       T2.OUT_DATE,
       'RESULT' = CASE WHEN T1.TYPEZONE = 1 THEN CASE WHEN EXISTS (SELECT T3.LAT, T3.LON FROM REGIONS AS T3 WHERE  T3.ID_ZONE = T1.ID_ZONE)
                                                      THEN CASE WHEN ((SELECT SQRT(POWER((T3.LAT - @X), 2) + POWER((T3.LON - @Y), 2))
                                                                       FROM REGIONS AS T3
                                                                       WHERE  T3.ID_ZONE = T1.ID_ZONE) <= T1.SIZE)
                                                                 THEN 1
                                                                 ELSE 0
                                                            END
                                                  END
                       WHEN T1.TYPEZONE = 2 THEN dbo.AInPoly (@X, @Y, T1.ID_ZONE)
                       WHEN T1.TYPEZONE = 3 THEN dbo.DistanceA2piece (@X, @Y, T1.ID_ZONE, T1.SIZE)
                  END
FROM   ZONES AS T1 JOIN JOBS AS T2 ON T1.ID_ZONE = T2.ID_ZONE

Table 'REGIONS'. Scan count 2, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'ZONES'. Scan count 0, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'JOBS'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
17 июл 09, 13:41    [7429047]     Ответить | Цитировать Сообщить модератору
 Re: Помогите найти решение  [new]
LexMinsk
Member

Откуда: Republic of Belarus
Сообщений: 451
Алексей2003
объясняю на пальцах:
SELECT T1.NAMEZONE, 
       T1.ID_ZONE, 
       T1.TYPEZONE, 
       T1.SIZE, 
       T2.ID_JOB, 
       T2.ID_CAR,
       T2.START_DATE,
       T2.END_DATE,
       T2.TIMEOUT,
       T2.OUT_DATE,
       case when t3.LAT is not null and t3.lon is not null
           then case when SQRT SQRT(POWER((t3.LAT-@X), 2) + POWER((t3.LON-@Y), 2)) <= t1.SIZE
                           then 1
                           else 0
                  end
           else 0
       end as result
FROM   ZONES AS T1
  join     JOBS AS T2 ON T1.ID_ZONE = T2.ID_ZONE
  left join REGIONS AS T3 on T1.ID_ZONE = T3.ID_ZONE
WHERE  T1.TYPEZONE = 1
делайте также с другими строками.
склеивать будете через UNION ALL по разным типам зон.


для спящего время бодрствования равносильно сну


Это идея, спс за подсказку. Только вот как мне курсоры сюда засунуть из других функций?
17 июл 09, 13:44    [7429071]     Ответить | Цитировать Сообщить модератору
 Re: Помогите найти решение  [new]
Алексей2003
Member

Откуда: Москва
Сообщений: 5645
попробуйте работать со множествами. уверен все получится.

для спящего время бодрствования равносильно сну
17 июл 09, 13:45    [7429082]     Ответить | Цитировать Сообщить модератору
 Re: Помогите найти решение  [new]
Алексей2003
Member

Откуда: Москва
Сообщений: 5645
вот еще 1 функция развернута
DECLARE @X REAL
DECLARE @Y REAL
SET @X = 0.9412705
SET @Y = 0.4793433
SELECT T1.NAMEZONE, 
       T1.ID_ZONE, 
       T1.TYPEZONE, 
       T1.SIZE, 
       T2.ID_JOB, 
       T2.ID_CAR,
       T2.START_DATE,
       T2.END_DATE,
       T2.TIMEOUT,
       T2.OUT_DATE,
       (t3.pr & 1) as result
FROM   ZONES AS T1
  join     JOBS AS T2 ON T1.ID_ZONE = T2.ID_ZONE
  left join 
   (select tt1.ID_ZONE,
     sum(case when (@X = t3.lat) AND (@X = t3.lon)
       then 0
      else
       case when ((((T1LON <= @Y) AND (@Y < T2LON)) OR ((T2LON <= @Y) AND (@Y < T1LON))) AND 
         (@X > (T2LAT - T1LAT)*(@Y - T2LON)/(T2LON - T1LON) + T1LAT))
        then 1
        else 0
       end
      end) pr
     from zones tt1
      join
       (SELECT T1.LAT t1lat, T1.LON t1lon, T2.LAT t2lat, T2.LON t2lon, T1.ID_ZONE
         FROM REGIONS AS T1
          JOIN REGIONS AS T2
           ON
            (T2.NUM =
             (SELECT TOP 1 T3.NUM 
              FROM REGIONS AS T3 
              WHERE (T1.NUM < T3.NUM) AND 
               (T1.ID_ZONE = T3.ID_ZONE)
              ORDER BY T3.NUM
             )
            ) AND 
             (T1.ID_ZONE = T2.ID_ZONE)
        UNION ALL
        SELECT T1.LAT, T1.LON, T2.LAT, T2.LON, T1.ID_ZONE
         FROM REGIONS AS T1
          JOIN REGIONS AS T2
           ON
            (T1.NUM = (SELECT MIN(NUM) FROM REGIONS)) AND
            (T2.NUM = (SELECT MAX(NUM) FROM REGIONS)) AND 
            (T1.ID_ZONE = T2.ID_ZONE)
        ) AS T3
         on TT1.ID_ZONE = T3.ID_ZONE where t1.typezone = 2
   ) t3 on t1.id_zone = t3.id_zone
WHERE  T1.TYPEZONE = 2
последнюю по аналогии разворачивайте..

для спящего время бодрствования равносильно сну
17 июл 09, 14:02    [7429198]     Ответить | Цитировать Сообщить модератору
 Re: Помогите найти решение  [new]
Алексей2003
Member

Откуда: Москва
Сообщений: 5645
on TT1.ID_ZONE = T3.ID_ZONE where tt1.typezone = 2

для спящего время бодрствования равносильно сну
17 июл 09, 14:03    [7429204]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить