Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Геоиндекс  [new]
Замглавврача
Member [заблокирован]

Откуда: Психиатрическая клиника имени Джуджа
Сообщений: 4017
Господа, если поле location с типом geography, есть параметр типа geography. Нужно найти все близлежащие объекты из таблицы, которые находятся на расстоянии 10 метров.

Пишу такой запрос

SELECT *
FROM exf
where exf.location.STDistance(@loc) < 10


Проблема запроса в том, что индекс на поле отсутствует и скан по 21 миллиону записей выполняется несколько часов.

Что бы ускорить поиск, решил создать пространственный индекс CREATE SPATIAL INDEX IX_exf ON exf (location)
Проблема данного способа в том, что индекс создается несколько часов, при этом множество процедур, которые используют табличку, оказываются заблокированными. С таким запросом меня на прод не пустят.

Вопрос в следующем - можно ли как-нибудь заставить выполняться CREATE SPATIAL INDEX в "фоновом режиме" без блокировки процедур, которые используют таблицу?
7 июл 14, 18:09    [16271230]     Ответить | Цитировать Сообщить модератору
 Re: Геоиндекс  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37068
create index ... with (online = on)

Если нельзя, то тогда придется делать окно.

Сообщение было отредактировано: 7 июл 14, 18:22
7 июл 14, 18:22    [16271341]     Ответить | Цитировать Сообщить модератору
 Re: Геоиндекс  [new]
хмхмхм
Guest
Замглавврача,

при создании индекса будет блокировка изменения схемы
http://technet.microsoft.com/ru-ru/library/ms186396(v=sql.105).aspx

Могу предложить вариант с созданием таблицы-двойника с индексом и переносом данных туда (порционно или bulk insert), затем удалением и переименованием таблицы-двойника.
7 июл 14, 18:27    [16271387]     Ответить | Цитировать Сообщить модератору
 Re: Геоиндекс  [new]
Замглавврача
Member [заблокирован]

Откуда: Психиатрическая клиника имени Джуджа
Сообщений: 4017
хмхмхм
Замглавврача,

при создании индекса будет блокировка изменения схемы
http://technet.microsoft.com/ru-ru/library/ms186396(v=sql.105).aspx

Могу предложить вариант с созданием таблицы-двойника с индексом и переносом данных туда (порционно или bulk insert), затем удалением и переименованием таблицы-двойника.

Гавриленко Сергей Алексеевич
create index ... with (online = on)

Если нельзя, то тогда придется делать окно.


Сообщение 153, уровень 15, состояние 3, строка 4
Недопустимое использование параметра online в инструкции CREATE SPATIAL INDEX.
7 июл 14, 18:39    [16271460]     Ответить | Цитировать Сообщить модератору
 Re: Геоиндекс  [new]
Замглавврача
Member [заблокирован]

Откуда: Психиатрическая клиника имени Джуджа
Сообщений: 4017
Сергей,

про окно можно подробнее?
7 июл 14, 18:40    [16271467]     Ответить | Цитировать Сообщить модератору
 Re: Геоиндекс  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37068
Замглавврача
Сергей,

про окно можно подробнее?
Сервисное окно - это такоей время, когда с базой никто не работает, чтобы там можно было делать всякие разные вещи, например, накатывать индексы, менять структуру и т.д. и т.п.

Есть еще вариант делать полную копию таблицы, но с индексом, переливать туда все записи частями, и все последющие изменения, а потом таблицы переименовывать. Но во-первых, есть ньюансы, а во-вторых, просто сделать индекс в разы проще.
7 июл 14, 18:47    [16271494]     Ответить | Цитировать Сообщить модератору
 Re: Геоиндекс  [new]
gandjustas
Member

Откуда:
Сообщений: 857
Блог
Замглавврача,

select * into exfNew
go
create index ... on exfNew(...)
go
alter table exfNew reneme to exf
go
alter table exf reneme to exfOld


Как-то так
7 июл 14, 18:53    [16271519]     Ответить | Цитировать Сообщить модератору
 Re: Геоиндекс  [new]
Relic Hunter
Member

Откуда: AB
Сообщений: 7377
gandjustas
Замглавврача,

select * into exfNew
go
create index ... on exfNew(...)
go
alter table exfNew reneme to exf
go
alter table exf reneme to exfOld


Как-то так
Гы...А то, что там наинсертится за время создания индекса - похерим?
7 июл 14, 18:58    [16271531]     Ответить | Цитировать Сообщить модератору
 Re: Геоиндекс  [new]
Замглавврача
Member [заблокирован]

Откуда: Психиатрическая клиника имени Джуджа
Сообщений: 4017
Relic Hunter
gandjustas
Замглавврача,

select * into exfNew
go
create index ... on exfNew(...)
go
alter table exfNew reneme to exf
go
alter table exf reneme to exfOld



Как-то так
Гы...А то, что там наинсертится за время создания индекса - похерим?


Синхронизировать под конец можно наверное. Добавить сначала rowversion, а после перезаливки таблицы залочить схему, долить изменения, переименовать табличку. Геморно конечно, но сервисного окна нет вообще, система 365*24
7 июл 14, 19:08    [16271571]     Ответить | Цитировать Сообщить модератору
 Re: Геоиндекс  [new]
gandjustas
Member

Откуда:
Сообщений: 857
Блог
Relic Hunter,

После переименования сделать
insert select
из старой таблицы по всем ID больше максимального в новой таблице.
7 июл 14, 19:12    [16271586]     Ответить | Цитировать Сообщить модератору
 Re: Геоиндекс  [new]
Relic Hunter
Member

Откуда: AB
Сообщений: 7377
gandjustas,

Как на счет "delete", "update"?
7 июл 14, 19:29    [16271633]     Ответить | Цитировать Сообщить модератору
 Re: Геоиндекс  [new]
Замглавврача
Member [заблокирован]

Откуда: Психиатрическая клиника имени Джуджа
Сообщений: 4017
Relic Hunter
gandjustas,

Как на счет "delete", "update"?


я добавил rowversion, удалений из таблицы нет.
8 июл 14, 10:36    [16273488]     Ответить | Цитировать Сообщить модератору
 Re: Геоиндекс  [new]
Замглавврача
Member [заблокирован]

Откуда: Психиатрическая клиника имени Джуджа
Сообщений: 4017
Relic Hunter
gandjustas,

Как на счет "delete", "update"?


при чем update не критичен - можно сделать уже после переименования таблицы
8 июл 14, 10:38    [16273498]     Ответить | Цитировать Сообщить модератору
 Re: Геоиндекс  [new]
gandjustas
Member

Откуда:
Сообщений: 857
Блог
Relic Hunter
gandjustas,

Как на счет "delete", "update"?


Есть оператор merge.
8 июл 14, 11:50    [16274076]     Ответить | Цитировать Сообщить модератору
 Re: Геоиндекс  [new]
rwrqerverq
Guest
gandjustas
Relic Hunter
gandjustas,

Как на счет "delete", "update"?


Есть оператор merge.


и что? в read committed - shared lock будет удерживаться на момент чтения строки. прочитали строк бросили блокировку. пока merge дойдет до конца таблицы я успею поднасрать в начале таблицы апдейтами и делитами. дальше что?

в других уровнях изоляции "... С таким запросом меня на прод не пустят...."
8 июл 14, 12:25    [16274382]     Ответить | Цитировать Сообщить модератору
 Re: Геоиндекс  [new]
gandjustas
Member

Откуда:
Сообщений: 857
Блог
rwrqerverq
gandjustas
пропущено...


Есть оператор merge.


и что? в read committed - shared lock будет удерживаться на момент чтения строки. прочитали строк бросили блокировку. пока merge дойдет до конца таблицы я успею поднасрать в начале таблицы апдейтами и делитами. дальше что?

в других уровнях изоляции "... С таким запросом меня на прод не пустят...."


Мерж делать после переименования, а не до. "Поднасрать" уже в новую таблицу даже хорошо.
8 июл 14, 13:50    [16275098]     Ответить | Цитировать Сообщить модератору
 Re: Геоиндекс  [new]
Замглавврача
Member [заблокирован]

Откуда: Психиатрическая клиника имени Джуджа
Сообщений: 4017
Как выяснилось, delete и update отслеживать не нужно - строки только добавляются. Так что все просто, создаю новую табличку, индекс на нее, потом заливаю ее данными с хинтом nolock, что занимает около двух часов, затем начинаю транзакцию, в которой доливаю все записи, у которых id больше чем максимальный в новой таблице, и переименовываю таблицы. Как-то так.

set nocount, xact_abort on
create table exfNew ...
create spatial index ...
set identity_insert exfnew on
insert exfNew ... select ... from exf
set identity_insert exfnew off
begin transaction
set identity_insert exfnew on
insert exfNew ... select ... from exf where id > (select max(id) from exfNew)
set identity_insert exfnew off
sp_rename 'exf', 'exfold'
sp_rename 'exfnew', 'exf'
commit
8 июл 14, 13:57    [16275177]     Ответить | Цитировать Сообщить модератору
 Re: Геоиндекс  [new]
Замглавврача
Member [заблокирован]

Откуда: Психиатрическая клиника имени Джуджа
Сообщений: 4017
Замглавврача
Как выяснилось, delete и update отслеживать не нужно - строки только добавляются. Так что все просто, создаю новую табличку, индекс на нее, потом заливаю ее данными с хинтом nolock, что занимает около двух часов, затем начинаю транзакцию, в которой доливаю все записи, у которых id больше чем максимальный в новой таблице, и переименовываю таблицы. Как-то так.

set nocount, xact_abort on
create table exfNew ...
create spatial index ...
set identity_insert exfnew on
insert exfNew ... select ... from exf with (nolock)
set identity_insert exfnew off
begin transaction
set identity_insert exfnew on
insert exfNew ... select ... from exf where id > (select max(id) from exfNew)
set identity_insert exfnew off
sp_rename 'exf', 'exfold'
sp_rename 'exfnew', 'exf'
commit
8 июл 14, 13:58    [16275188]     Ответить | Цитировать Сообщить модератору
 Re: Геоиндекс  [new]
dqwdqwdqw
Guest
gandjustas
rwrqerverq
пропущено...


и что? в read committed - shared lock будет удерживаться на момент чтения строки. прочитали строк бросили блокировку. пока merge дойдет до конца таблицы я успею поднасрать в начале таблицы апдейтами и делитами. дальше что?

в других уровнях изоляции "... С таким запросом меня на прод не пустят...."


Мерж делать после переименования, а не до. "Поднасрать" уже в новую таблицу даже хорошо.


это если логика приложения такие финты позволяет делать. данные в табличке будут какое-то время не согласованными.
9 июл 14, 09:40    [16279160]     Ответить | Цитировать Сообщить модератору
 Re: Геоиндекс  [new]
Замглавврача
Member [заблокирован]

Откуда: Психиатрическая клиника имени Джуджа
Сообщений: 4017
dqwdqwdqw
gandjustas
пропущено...


Мерж делать после переименования, а не до. "Поднасрать" уже в новую таблицу даже хорошо.


это если логика приложения такие финты позволяет делать. данные в табличке будут какое-то время не согласованными.


Там пол-таблички под уничтожение пойдет. Именно для этого нужен индекс - дубли искать.
9 июл 14, 14:10    [16281066]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить