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

Откуда:
Сообщений: 24
Добрый день.
В рамках задачи необходимо реализовать синхронизацию данных между центральной БД (MS Sql Server 2008R2 Standart) и клиентской БД (MS SQL SERVER 2008 Express). Реализую с использованием Lincked Server'а на стороне клиента.
Вставка (Insert) данных проходит нормально.
Размер таблицы на принимающей стороне достаточно большой (около 3 млн. записей). Команды пишу в формате:
Update db2.Table1
set ...
from db1.Table1 db1_t1 inner join db2.Table1 db2_t1 on (...)

Пользователь от имени которого создается подключение к Удаленной БД является членом роли db_owner.
Возникла проблема с командами Update и Delete. Очень долго выполняются (100 записей больше 20 минут).
Подскажите, как правильно решить эту проблему.
Заранее спасибо.
21 фев 14, 10:55    [15603592]     Ответить | Цитировать Сообщить модератору
 Re: Долгий Update в распределенной БД  [new]
Glory
Member

Откуда:
Сообщений: 104760
Roman39
Подскажите, как правильно решить эту проблему.

Смотреть для начала план выполнения.
21 фев 14, 10:58    [15603605]     Ответить | Цитировать Сообщить модератору
 Re: Долгий Update в распределенной БД  [new]
Roman39
Member

Откуда:
Сообщений: 24
Glory
Roman39
Подскажите, как правильно решить эту проблему.

Смотреть для начала план выполнения.

Судя по плану выполнения удаленный сервер пытается передать !все! записи из целевой таблицы, а это около 30 МБ. И только потом на стороне клиента делает join. Remote join почему то ничего не меняет.
Вот пример.

К сообщению приложен файл. Размер - 46Kb
21 фев 14, 11:25    [15603791]     Ответить | Цитировать Сообщить модератору
 Re: Долгий Update в распределенной БД  [new]
VFl
Member

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

у меня была похожая проблема, помогло использование табличных функций в UPDATE. Т.е. делаете на сервере функцию, которая возвращает только нужные записи с использованием переданных паметров, и используете ее при UPDATE на стороне клиента.

Функция выполняется на стороне сервера, и поэтому передаются только те записи, которые должны изменяться.
23 фев 14, 20:32    [15615695]     Ответить | Цитировать Сообщить модератору
 Re: Долгий Update в распределенной БД  [new]
Roman39
Member

Откуда:
Сообщений: 24
VFl
Roman39,

у меня была похожая проблема, помогло использование табличных функций в UPDATE. Т.е. делаете на сервере функцию, которая возвращает только нужные записи с использованием переданных паметров, и используете ее при UPDATE на стороне клиента.

Функция выполняется на стороне сервера, и поэтому передаются только те записи, которые должны изменяться.


Была такая идея, но мне нужно возвращать (изменять) записи с сервера по данным клиента, т.е. я не могу четко определить входные параметры для этой функции (если только передавать строку с набором ключей, тех записей, которые надо вернуть)...
24 фев 14, 10:48    [15617739]     Ответить | Цитировать Сообщить модератору
 Re: Долгий Update в распределенной БД  [new]
StarikNavy
Member

Откуда: Москва
Сообщений: 2396
Roman39,
>> только передавать строку с
например обернуть в процедуру, с входной таблицей
24 фев 14, 11:47    [15618181]     Ответить | Цитировать Сообщить модератору
 Re: Долгий Update в распределенной БД  [new]
Roman39
Member

Откуда:
Сообщений: 24
StarikNavy
Roman39,
>> только передавать строку с
например обернуть в процедуру, с входной таблицей

Подскажите, как передать таблицу в ХП?
24 фев 14, 11:55    [15618260]     Ответить | Цитировать Сообщить модератору
 Re: Долгий Update в распределенной БД  [new]
BuKTaP
Member

Откуда:
Сообщений: 132
нужно определять свой тип и от него плясать, использовать табличную переменную

здесь пример есть
http://outcoldman.com/ru/blog/show/204
Решение 3. Table-Valued Parameters (Database Engine)
24 фев 14, 13:30    [15619167]     Ответить | Цитировать Сообщить модератору
 Re: Долгий Update в распределенной БД  [new]
Roman39
Member

Откуда:
Сообщений: 24
BuKTaP
нужно определять свой тип и от него плясать, использовать табличную переменную

здесь пример есть
http://outcoldman.com/ru/blog/show/204
Решение 3. Table-Valued Parameters (Database Engine)


Хорошее решение! Пригодится. Спасибо! Вот еще не надо было бы в типе описывать конкретную структуру таблицы... а то у меня около 30-ти таблиц и на каждую надо будет иметь свой тип + ХП.
24 фев 14, 16:45    [15620902]     Ответить | Цитировать Сообщить модератору
 Re: Долгий Update в распределенной БД  [new]
BuKTaP
Member

Откуда:
Сообщений: 132
Полагаю, что можно сделать один табличный тип, содержащий по много полей нужных типов. Выберите по максимуму количества полей из всех 30-ти таблиц и сделайте такой тип, в который влезут любые данные. Неудобно, конечно, потом будет выбирать их оттуда (да и заполнять тоже), но думаю, что такое решение можно рассмотреть. Хотя я бы делал тип для каждой таблицы.
24 фев 14, 17:02    [15621049]     Ответить | Цитировать Сообщить модератору
 Re: Долгий Update в распределенной БД  [new]
VFl
Member

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

Roman39
мне нужно возвращать (изменять) записи с сервера по данным клиента


Можно еще сделать таблицу, в которую заносятся ID записей для изменения. С 2 полями - process_id и toupdate_id. Если данные изменяются на сервере, и несколько клиентов, то еще и client_id. process_id генерируется на стороне, где выполняется синхронизация и передается в функцию, а та уже возвращает записи по [client_id и] toupdate_id. Но вообще да, надо смотреть, по каким критериям происходит UPDATE, то, что у вас как многоточие, оно в этом случае важно.
inner join db2.Table1 db2_t1 on (...)


У меня было последний timestamp с предыдущей синхронизации и актуальный timestamp, который перед этим вытаскивался через SELECT max(timestamp), они и передавались в функцию.

И если изменяются данные на сервере, то подход один, если на клиенте, то другой.
24 фев 14, 17:06    [15621077]     Ответить | Цитировать Сообщить модератору
 Re: Долгий Update в распределенной БД  [new]
Roman39
Member

Откуда:
Сообщений: 24
BuKTaP, нет, так будет плохо... таблицы большие полей много...
24 фев 14, 17:12    [15621116]     Ответить | Цитировать Сообщить модератору
 Re: Долгий Update в распределенной БД  [new]
Roman39
Member

Откуда:
Сообщений: 24
VFl, сервер "не знает" о клиентах, поэтому он не может вытянуть из них данные для Update'а. Я такой подход использовал для Delete'ов. Т.е. создал таблицу на сервере в которую передавал ID записи, которую надо удалить и потом сервер сам запускал обработчик и удалял эти записи. Для Update'ов этого не достаточно т.к. нужны еще данные для обновления.
24 фев 14, 17:17    [15621153]     Ответить | Цитировать Сообщить модератору
 Re: Долгий Update в распределенной БД  [new]
VFl
Member

Откуда:
Сообщений: 126
Roman39
нужны еще данные для обновления.


Эти ID будут нужны для функции на сервере, которая по ним передаст на клиент все данные для обновления. Получив как параметр @PROCESS_ID. Так, например:
SELECT Table1.* 
FROM Table1, Table_id_for_update 
WHERE Table1.ID = Table_id_for_update.toupdate_id 
AND Table_ID_for_update.process_id = @PROCESS_ID
24 фев 14, 17:27    [15621229]     Ответить | Цитировать Сообщить модератору
 Re: Долгий Update в распределенной БД  [new]
Roman39
Member

Откуда:
Сообщений: 24
VFl
Roman39
нужны еще данные для обновления.


Эти ID будут нужны для функции на сервере, которая по ним передаст на клиент все данные для обновления.

У меня как раз обратный случай. Клиент передает свои данные на сервер.
24 фев 14, 17:32    [15621260]     Ответить | Цитировать Сообщить модератору
 Re: Долгий Update в распределенной БД  [new]
VFl
Member

Откуда:
Сообщений: 126
Roman39
Клиент передает свои данные на сервер.


Тогда плохо, что сервер ничего не знает о клиенте. На ум приходит только дополнительная таблица на сервере со всеми полями, что в Table1, в которую через INSERT клиент заносит данные для UPDATE. А та через триггер делает после INSERT уже UPDATE на Table1.
24 фев 14, 17:43    [15621332]     Ответить | Цитировать Сообщить модератору
 Re: Долгий Update в распределенной БД  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
Roman39
Update db2.Table1
set ...
from db1.Table1 db1_t1 inner join db2.Table1 db2_t1 on (...)

DELETE db2.Table1
from db1.Table1 db1_t1 inner join db2.Table1 db2_t1 on (...)
Привыкайте писать правильно и однозначно.
UPDATE	db2_t1 -- Алиас
SET	...
FROM 		db1.Table1 AS db1_t1
INNER JOIN	db2.Table1 AS db2_t1 on (...)

DELETE	db2_t1 -- Алиас
FROM		db1.Table1 AS db1_t1
INNER JOIN	db2.Table1 AS db2_t1 on (...)
Ибо можете нарваться на двойное соединение, в других местах.

http://technet.microsoft.com/ru-ru/library/ms173815.aspx
REMOTE

Задает, что операция соединения проводится на сайте в таблице, расположенной справа. Данный аргумент удобно использовать в случае, когда таблица, расположенная слева, является локальной, а справа располагается удаленная таблица. Аргумент REMOTE может использоваться в случае, когда в таблице слева содержится меньшее количество строк, чем в таблице справа.

Если таблица, расположенная справа, является локальной, то операция соединения также проводится локально. Если обе таблицы являются удаленными, но расположены в различных источниках данных, то при задании аргумента REMOTE операция соединения проводится на сайте в таблице, расположенной справа. Если обе таблицы являются удаленными таблицами в одном источнике данных, то аргумент REMOTE не требуется.

Аргумент REMOTE не может быть использован для сравнения в предикате соединения значений, одно из которых приведено к другим параметрам сортировки с помощью предложения COLLATE.

Аргумент REMOTE может быть использован только при операциях INNER JOIN.
По ходу вы всё учли.

Меня всегда разачаровывало что MS ничего не хочет делать в случае распределённых запросов. Да и типы данных не все поддерживает.

Roman39
сервер "не знает" о клиентах, поэтому он не может вытянуть из них данные для Update'а.
А можно всётаки сообщать эту инфу?
Т.е. вызываете с клиента запрос который вызывает запрос (уже с сервера на клиент).
И делать всё "там" через OpenDataSource.
UPDATE	R
SET	[Col1]	= L.[Col1] -- ...
FROM	OpenDataSource('SQLNCLI','Data Source=<server>\<Instance>;Integrated Security=SSPI').
	DB.Schema.Table	L
JOIN	DB.Schema.Table	R ON R.ID = L.ID
Имена сервера и интанса можно подставлять через @@ServerName.
А вызвать этот код через EXEC () AT [RemoteServer], к примеру.

А вообще меня удивляет что клиентов много, они пишут на сервер без условно - принудительно, что говорит что чкорее данные при этом не пересекаются. Значит есть критерий какие данные "принадлежат" клиенту. Так?
И при этом не используются никакие стандартные средства аля репликация и т.п.

И ещё, INSERT/UPDATE/DELETE может команда MERGE? Чтобы 3 раза не тянуть ... ИМХО, просто, вдруг.
А ещё можно сменить стратегию - не ждать пока куча соберётся, а сразу отправлять по кусочкам. Да хоть через тот же Service Brocker
Всё, молчу, молчу ...
25 фев 14, 03:14    [15623360]     Ответить | Цитировать Сообщить модератору
 Re: Долгий Update в распределенной БД  [new]
Roman39
Member

Откуда:
Сообщений: 24
Mnior
Ибо можете нарваться на двойное соединение, в других местах.

Roman39
сервер "не знает" о клиентах, поэтому он не может вытянуть из них данные для Update'а.
А можно всётаки сообщать эту инфу?
Т.е. вызываете с клиента запрос который вызывает запрос (уже с сервера на клиент).
И делать всё "там" через OpenDataSource.
UPDATE	R
SET	[Col1]	= L.[Col1] -- ...
FROM	OpenDataSource('SQLNCLI','Data Source=<server>\<Instance>;Integrated Security=SSPI').
	DB.Schema.Table	L
JOIN	DB.Schema.Table	R ON R.ID = L.ID
Имена сервера и интанса можно подставлять через @@ServerName.
А вызвать этот код через EXEC () AT [RemoteServer], к примеру.


Нет, так к сожалению не получится т.к. сервера не в локальной сети и @@ServerName вероятно будет не достаточно. Хотя можно попробовать клиент ведь сам будет прописывать строку подключения "к себе". А OpenDataSource даст себя вызвать через конструкцию EXEC () AT [RemoteServer]? надо попробовать... Спасибо за идею.

Mnior
А вообще меня удивляет что клиентов много, они пишут на сервер без условно - принудительно, что говорит что чкорее данные при этом не пересекаются. Значит есть критерий какие данные "принадлежат" клиенту. Так?
И при этом не используются никакие стандартные средства аля репликация и т.п.


Да, есть критерий для идентификации данных определенного клиента. Стандартные средства (вроде репликации) не используются, потому что на клиентах стоит Express версия. Она не поддерживает репликации (публикации).

Mnior
И ещё, INSERT/UPDATE/DELETE может команда MERGE? Чтобы 3 раза не тянуть ... ИМХО, просто, вдруг.
А ещё можно сменить стратегию - не ждать пока куча соберётся, а сразу отправлять по кусочкам. Да хоть через тот же Service Brocker
Всё, молчу, молчу ...


С MERGE никогда не работал. Сейчас у меня хотя бы Insert'ы работают, а MERGE может вообще все "повесить". Так получается, что данные приходят не "по кусочкам", а сразу "много". Поэтому и отправлять надо сразу все, что пришли
25 фев 14, 10:38    [15624017]     Ответить | Цитировать Сообщить модератору
 Re: Долгий Update в распределенной БД  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
Roman39
Так получается, что данные приходят не "по кусочкам", а сразу "много". Поэтому и отправлять надо сразу все, что пришли
Не могу согласовать "приходят" и update/delete.
Если новые, то ведь только Insert.

Хотя другой вопрос, те данные что приходят они все что-то делают (Inser/Update/Delete) ? Или только часть из них?

Если все, то я не очень понимаю процесс, всё равно надо все данные отправить на центральный сервер и произвести Merge. (Merge просто сделает в один присест, а так неважно, при этом лучше чтоб был Merge-Join в соединении).

Т.е. роль клиентской базы не очень понятна. Она работает или просто источник данных (подымается и потом сливается) ? Если просто источник - то вообще непонятно зачем всё это.
Если допустим связь временная (клиенты на месте), то может ну его - просто отправляйте все 30 таблиц (неважно как), а далее наладте процесс синхронизации. Если это всё будет в одном потоке последовательно - даже лучше, ибо если два клиента одновременно - то может и дедлок и нагрузка сильно повысится, а так послал данные и забил, они асинхронно применятся.

А посылать можно хоть файлами, выгрузил - отправил, а на центральном загружаешь поток файлов в цикле не спеша.

Кароче, лучше думать об аккуратности и надёжности процессов, чем на быструю руку сфорганить фиг знает что. Ну это вам решать.
25 фев 14, 19:24    [15628522]     Ответить | Цитировать Сообщить модератору
 Re: Долгий Update в распределенной БД  [new]
Roman39
Member

Откуда:
Сообщений: 24
Mnior
Не могу согласовать "приходят" и update/delete.
Если новые, то ведь только Insert.

Приходят данные "пакетами" (раз в день, неделю, месяц). У каждой записи есть отметка, что с ней необходимо сделать в центральной базе (Обновить, Удалить, Добавить).

Mnior
Хотя другой вопрос, те данные что приходят они все что-то делают (Inser/Update/Delete) ? Или только часть из них?


Нет, есть "старые" данные, которые уже переданы в центральную базу

Mnior
Т.е. роль клиентской базы не очень понятна. Она работает или просто источник данных (подымается и потом сливается) ? Если просто источник - то вообще непонятно зачем всё это.
Если допустим связь временная (клиенты на месте), то может ну его - просто отправляйте все 30 таблиц (неважно как), а далее наладте процесс синхронизации. Если это всё будет в одном потоке последовательно - даже лучше, ибо если два клиента одновременно - то может и дедлок и нагрузка сильно повысится, а так послал данные и забил, они асинхронно применятся.

А посылать можно хоть файлами, выгрузил - отправил, а на центральном загружаешь поток файлов в цикле не спеша.

Кароче, лучше думать об аккуратности и надёжности процессов, чем на быструю руку сфорганить фиг знает что. Ну это вам решать.


Связь может быть не постоянна, т.е. могут быть организованы сеансы связи в которые необходимо передать обновления в центральную базу. Что значит "просто отправляйте все 30 таблиц (неважно как)"? От работы с файлами хотели уйти, но возможно стоит рассмотреть и такой вариант.
26 фев 14, 11:03    [15630884]     Ответить | Цитировать Сообщить модератору
 Re: Долгий Update в распределенной БД  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
Roman39
Нет, есть "старые" данные, которые уже переданы в центральную базу
У каждой записи есть отметка, что с ней необходимо сделать в центральной базе (Обновить, Удалить, Добавить).
Т.е. можно выделить какие записи уже не надо передавать, а какие надо. Т.е. получается вы можете передать только те и все те данные которые надо "применить" на централе?

Просто стандартный механизм RowVersion/TimeStamp (TS) как раз и сделан для такого.

А пользоваться им топорно. Когда фиксируется очередная порция синхронизации (или новая строка в таблице "синхры" или обновление одной строки в случае без истории) и фиксируется текущий TS, собирается вся инфа по всем нужным таблам от предыдущего TS до текущего. Передаётся в центр.
Центр может применять. Синхронно или нет - не важно.

Ещё тут нюансы надо учесть.
1. Пока делается выгрузка ("применение" в вашем случае), данные могут меняться параллельно в других таблах -> Несогласованность данных. Поэтому есть понятие "закрытый период".
2. Связи объектов. Если данные не удаляются, то меньше проблем, если удаляются, то порядок применения изменений сложен и важен. Если конечно не отключать констреинты.

Roman39
От работы с файлами хотели уйти, но возможно стоит рассмотреть и такой вариант.
Файлы просто дают гибкость в системе связи, неустойчивость, непрямая видимость (легче админам). Но это не принципиально, можно вытягивать и так как было предложено выше.
26 фев 14, 21:32    [15635879]     Ответить | Цитировать Сообщить модератору
 Re: Долгий Update в распределенной БД  [new]
Roman39
Member

Откуда:
Сообщений: 24
[quot Mnior]Т.е. можно выделить какие записи уже не надо передавать, а какие надо. Т.е. получается вы можете передать только те и все те данные которые надо "применить" на централе?
[/quot Mnior]

Да, все верно

[quot Mnior]
Просто стандартный механизм RowVersion/TimeStamp (TS) как раз и сделан для такого.

А пользоваться им топорно. Когда фиксируется очередная порция синхронизации (или новая строка в таблице "синхры" или обновление одной строки в случае без истории) и фиксируется текущий TS, собирается вся инфа по всем нужным таблам от предыдущего TS до текущего. Передаётся в центр.
Центр может применять. Синхронно или нет - не важно.
[/quot Mnior]

Собственно почти так и происходит, только порядок обновления важен. Как лучше по вашему "Передать в центр"?

[quot Mnior]
Ещё тут нюансы надо учесть.
1. Пока делается выгрузка ("применение" в вашем случае), данные могут меняться параллельно в других таблах -> Несогласованность данных. Поэтому есть понятие "закрытый период".
2. Связи объектов. Если данные не удаляются, то меньше проблем, если удаляются, то порядок применения изменений сложен и важен. Если конечно не отключать констреинты.
[/quot Mnior]

Констреинты не отключаются, поэтому порядок обновления важен.

Mnior
Файлы просто дают гибкость в системе связи, неустойчивость, непрямая видимость (легче админам). Но это не принципиально, можно вытягивать и так как было предложено выше.


Для файлов надо "прикручивать" какой то механизм их передачи на удаленных сервер.
27 фев 14, 10:36    [15637879]     Ответить | Цитировать Сообщить модератору
 Re: Долгий Update в распределенной БД  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
Репликация данных в SQL Server Express
Подскажите решение для синхронизации баз (SQL Server 2008 Express)

Roman39
Для файлов надо "прикручивать" какой то механизм их передачи на удаленных сервер
Это зависит от архитектуры сети/взаимодействия.
Если у вас итак было решено делать через linked server, значит компы имеют сквозной доступ (VPN?), следовательно даже банальный шариг папки заработает.
Иначе можно хоть топорным BitTorrent Sync пользоваться.

Опять же, админы должны знать все виды решений и иметь на это политику.

Массовый импорт и экспорт данных (SQL Server)
Хоть обычным bcp, хоть заскриптовать в PowerShell или тупой .cmd
Остальное пару строк в TSQL

1. Посмотрите в сторону репликации.
2. Попробуйте вариант описанный выше (linked + OpenDataSource)
3. Иначе вариант с файлами (если offline и ассинхронно предпочтительнее)

У всех есть недостатки и приемущества.
Просто прямая связь (linked и др) требуют стабильную сеть. Если будет сбоить - будут обидно отваливаться (отвлекать может), а данных у вас много. Поэтому в асинхронной передаче (к примеру файлов) просто нет такой проблемы, а далее более гибко можно наладить процесс синхронизации.
Но только вы знаете окружение и обстановку чтобы сделать выбор решения. А также влияет компетентность админов и широту их взглядов и технологий.

К примеру, для меня намного проще написать TSQL скрипт, чем иметь геморрой с SSIS (SQL Server Integration Services). Мышкодрочество и море багов в нём - просто не выгодно.
Но если не знаешь MS SQL и TSQL - то выбор резко меняется.

Вы думали что будет если будет сбой в той или иной команде. 30 таблиц, много данных, а отвалится может в любой момент в любой команде. И где вы хотите это контролить?
Если инициация и её контроль будеит идти на удалённых серверах (не центр), то как будет решаться нештатная ситуация? И как будет удобнее.
Если спецы в центре, то на локале легче устранять и менять процесс. И если "там" удалённо процессы топорные - то и проблем меньше.
Про коробочное решение вообще молчу, там перестраховка и контроль на каждом уровне и выбор архитектуры просто очень ответственное решение. Тут надо знать буквально всё.

А вообще тут на форуме много по тематике пишут.
Жаль что нет фака по списку вариантов.

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

Надеюсь я вас не сильно нагрузил.
27 фев 14, 14:58    [15640170]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить