Привет, all!

Собственно, обычная система, собирающая данные о положении с подвижных объектов с использованием GPS. Существует таблица, куда складываются координаты. Сервер в эту таблицу только пишет, клиенты только читают. Задачи — получить текущие координаты устройств (то есть, последние) и историю. Если с историей все понятно, то с текущими коодинатами есть вопрос. Понятно, что есть куча вариантов как это сделать, и хочется выбрать наилучший с точки зрения производительности и масштабируемости.

Вариант 1 — одна таблица с историей, текущие данные получаются при помощи select top 1 * from... order by. При наличии индексов работает нормально. Из плюсов — мало возможностей получить дедлок, так как при чтении и записи по индексу блокироваться будет только одна запись и они будут разными для клиента и для сервера.

Вариант 2 — отдельная таблица с текущими координатами. При добавлении новой записи в историю сервер обновляет соответствующую запись в текущей таблице. Смущает, что в этом случае и сервер и клиент могут одновременно обратиться к одной и той же записи — потенциальная проблема с производительностью из-за блокировок.


Вариант 3 — аналогично 2-му, но с версионностью — то есть в случае, если сервер и клиент одновременно обратились к одной и той же записи, то клиент получает предыдущую версию, а сервер пишет в новую. Честно говоря, не уверен что это пройдет с MSSQl даже на уровне изоляции транзакций SNAPSHOT.

Итак, как лучше? может есть другой вариант?

Здравствуйте, Sshur, Вы писали:

Да, забыл сказать что сервер 2005, а если клиент получит "грязные" данные о текущем положении — не беда.

Здравствуйте, Sshur, Вы писали:
S>Собственно, обычная система, собирающая данные о положении с подвижных объектов с использованием GPS. Существует таблица, куда складываются координаты. Сервер в эту таблицу только пишет, клиенты только читают. Задачи — получить текущие координаты устройств (то есть, последние) и историю. Если с историей все понятно, то с текущими коодинатами есть вопрос. Понятно, что есть куча вариантов как это сделать, и хочется выбрать наилучший с точки зрения производительности и масштабируемости.
Собственно кучи вариантов и не видно.
S>Вариант 1 — одна таблица с историей, текущие данные получаются при помощи select top 1 * from... order by. При наличии индексов работает нормально. Из плюсов — мало возможностей получить дедлок, так как при чтении и записи по индексу блокироваться будет только одна запись и они будут разными для клиента и для сервера.
Вот это и есть единственный вариант.
Если детализировать, то структура получается примерно такая:
POSITION (ID, DEVICE_ID, GPS_PHRASE, TIMESTAMP, LONGITUDE, LATITUDE)
Пишем сразу GPS фразу, при записи из GPS фразы вычленяем время, долготу и широту (триггером на вставку, например).
Выборка уже описана.
S>Итак, как лучше? может есть другой вариант?
Никак специализировать текущие координаты не надо.

Здравствуйте, VVP, Вы писали:

VVP>Вот это и есть единственный вариант.
VVP>Если детализировать, то структура получается примерно такая:
VVP>POSITION (ID, DEVICE_ID, GPS_PHRASE, TIMESTAMP, LONGITUDE, LATITUDE)
VVP>Пишем сразу GPS фразу, при записи из GPS фразы вычленяем время, долготу и широту (триггером на вставку, например).
VVP>Выборка уже описана.


Я пока сделал с отдельной таблицей с текущими координатами

Мне не нравится, что если выбирать последнюю запись из общей таблицы, то одним запросом будет возвращаться только одно устройство. Придумать, как вернуть одним запросом сразу все, и чтобы запрос был "красивый" — я не смог.

Здравствуйте, Sshur, Вы писали:
VVP>>POSITION (ID, DEVICE_ID, GPS_PHRASE, TIMESTAMP, LONGITUDE, LATITUDE)
S>Я пока сделал с отдельной таблицей с текущими координатами
Мне такое решение не нравится.
S>Мне не нравится, что если выбирать последнюю запись из общей таблицы, то одним запросом будет возвращаться только одно устройство. Придумать, как вернуть одним запросом сразу все, и чтобы запрос был "красивый" — я не смог.
Подразумевая приведенную выше структуру, полагая, что TIMESTAMP(DEVICE_ID) монотонно возрастает, имеем:

select P.ID, P.DEVICE_ID, P.TIMESTAMP, P.LONGITUDE, P.LATITUDE
from (
  select DEVICE_ID, max(TIMESTAMP) as TIMESTAMP
  from POSITION
  group by DEVICE_ID
) as P0, POSITION as P
where P0.DEVICE_ID=P.DEVICE_ID and P0.TIMESTAMP=P.TIMESTAMP

При индексации по DEVICE_ID и TIMESTAMP все будет работать влет на очень большом количестве данных.
Блокировок не предвидится.

Здравствуйте, VVP, Вы писали:

VVP>Здравствуйте, Sshur, Вы писали:
VVP>Блокировок не предвидится.

А вот еще "красивый" вариант

select  top 1 with ties p.*  
from #position p
order by row_number() over (partition by DEVICE_ID order by TIMESTAMP desc,id desc)

Здравствуйте, VVP, Вы писали:

VVP>select P.ID, P.DEVICE_ID, P.TIMESTAMP, P.LONGITUDE, P.LATITUDE
VVP>from (
VVP>  select DEVICE_ID, max(TIMESTAMP) as TIMESTAMP
VVP>  from POSITION
VVP>  group by DEVICE_ID
VVP>) as P0, POSITION as P
VVP>where P0.DEVICE_ID=P.DEVICE_ID and P0.TIMESTAMP=P.TIMESTAMP

Да, этот вариант я отмел как "некрасивый". Надо план проверить на больших данных, так как изначально мне показалось, что это будет работать долго — тут лишний join и выборка по индексу. Но все равно спасибо

Здравствуйте, Goodkov, Вы писали:

G>Здравствуйте, VVP, Вы писали:

VVP>>Здравствуйте, Sshur, Вы писали:
VVP>>Блокировок не предвидится.

G>А вот еще "красивый" вариант

G>

G>select  top 1 with ties p.*  
G>from #position p
G>order by row_number() over (partition by DEVICE_ID order by TIMESTAMP desc,id desc)
G>

G>

Да, это "красивый" вариант. Надо еще производительность померять

Здравствуйте, Sshur, Вы писали:

S>Да, это "красивый" вариант. Надо еще производительность померять
А вот производительностью как раз он не блещет По крайней мере мне не удалось добиться приемлемых результатов
даже на 1 миллионе записей
вот в таком виде :

;with s
as
(
  select  p.*, row_number() over (partition by DEVICE_ID order by ondate desc) as rnk
  from #position p
)
select * from s where rnk = 1

еще чего-то можно добиться, с помощью индексов но он все равно проигрывает 20мс(610 против 590 на 1 млн. записей) варианту VVP.
В общем красота не главное

Здравствуйте, Goodkov, Вы писали:
S>>Да, это "красивый" вариант. Надо еще производительность померять
G>А вот производительностью как раз он не блещет По крайней мере мне не удалось добиться приемлемых результатов
G>даже на 1 миллионе записей
G>вот в таком виде :
[skipped]
G> еще чего-то можно добиться, с помощью индексов но он все равно проигрывает 20мс(610 против 590 на 1 млн. записей) варианту VVP.
G>В общем красота не главное
Интересно. Я в общем-то подозревал аналогичные результаты, но не проверял.
У нас на нескольких миллионах работают варианты, аналогичные моему.
В свое время аналитические функции по подмножествам (типа row_number) не показали адекватных результатов, а тратить время на доводку не хотелось. Имеющаяся производительность полностью устраивает.