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

Откуда:
Сообщений: 311
Господа, образовалась непонятная ситуация.
Есть два sql server'а. оба залинкованы друг на друга. на них есть две одинаковые по структуре таблицы.
если я вызываю на одном сервере (server1)
insert into table
select * from server2.db.dbo.table

то запрос выполняется за приемлемое время - 1-3 сек (всего несколько десятков тысяч записей)
если же я начинаю со второго сервера вставлять данные в таблицу на первом
insert into server1.db.dbo.table
select * from table

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

как понять, на каком этапе возникает проблема, и почему толкать не получается, а тянуть получается?

обе таблицы простые кучи, без индексов, ограничений и тд.
версии серверов 10.50.4000.0 (X64) и 11.0.6020.0 (X64)
8 апр 16, 14:53    [19033192]     Ответить | Цитировать Сообщить модератору
 Re: Вставка в таблицу на ликованном сервере  [new]
Glory
Member

Откуда:
Сообщений: 104760
bacalavr
как понять, на каком этапе возникает проблема, и почему толкать не получается, а тянуть получается?

потому, что толкаете вы по одной записи
Вы смотрели Профайлером, что происходит на линкед сервере ?
8 апр 16, 15:00    [19033247]     Ответить | Цитировать Сообщить модератору
 Re: Вставка в таблицу на ликованном сервере  [new]
bacalavr
Member

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

да , действительно, вставка происходит по одной записи. это бы объяснило проблему, если бы бы не:

если заменить server2 (11.0.6020.0) на server3 (версия 10.50.4000.0 (X64)).
то "тяни-толкаи" между server1(10.50.4000.0 (X64)) и server3 происходят практически мгновенно, разница между
insert into table
select * from server3.db.dbo.table

и
insert into server1.db.dbo.table
select * from table

незначительна

может ли так влиять версия сервера?
сам я склоняюсь к проблеме с инфраструктурой, но как подсказать, администраторам, что подкрутить - не знаю(
8 апр 16, 16:23    [19034090]     Ответить | Цитировать Сообщить модератору
 Re: Вставка в таблицу на ликованном сервере  [new]
Pavel1211
Member

Откуда: Екатеринбург
Сообщений: 205
bacalavr,

https://www.sql.ru/articles/mssql/2007/051803pushandpullinmicrosoftsqlserverlinkedservers.shtml
8 апр 16, 21:06    [19034999]     Ответить | Цитировать Сообщить модератору
 Re: Вставка в таблицу на ликованном сервере  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31779
bacalavr
если заменить server2 (11.0.6020.0) на server3 (версия 10.50.4000.0 (X64)).
А в этом случае тоже по одной записи?

bacalavr
сам я склоняюсь к проблеме с инфраструктурой, но как подсказать, администраторам, что подкрутить - не знаю(
Ну, при вставке по одной записи критична латентность. посмотрите пинг, сколько между серверами в разных вариантах?
8 апр 16, 22:34    [19035227]     Ответить | Цитировать Сообщить модератору
 Re: Вставка в таблицу на ликованном сервере  [new]
Сид
Member

Откуда: Москва
Сообщений: 305
bacalavr,

если нет возможности тянуть, а обязательно нужно толкать, можно попробовать следующее (актуально по крайней мере для небольших таблиц):

declare @xml xml, @cml varchar(max)

set @xml=(
select col1,col1,col3
from table
for xml path('tbl')
)

set @cml=CONVERT(varchar(max),@xml) -- ибо sp_executesql не принимает тип xml

exec server2.mydb.dbo.sp_executesql N'declare @xml xml
set @xml=@cml

INSERT INTO table(col1,col2,col3)
select t.col.value(''col1[1]'',''varchar(100)'') as col1,
	t.col.value(''col2[1]'',''int'') as col2,
	t.col.value(''col3[1]'',''datetime'') as col3
from @xml.nodes(''/tbl'') t(col)
',N'@cml varchar(max)',@cml


В своё время тоже столкнулся с проблемой медленного заталкивания на другой сервер. Вариант с генерацией одного большого XML и его заталкиванием на целевой сервер с последующим парсингом уже на целевой стороне выиграл по времени примерно в 10 раз по сравнению с обычным вариантом с insert select.
Попробуйте сделать то же самое, интересно, какой получится эффект.
9 апр 16, 19:01    [19036905]     Ответить | Цитировать Сообщить модератору
 Re: Вставка в таблицу на ликованном сервере  [new]
bacalavr
Member

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

да, спасибо за статью, этот момент я уже усек
11 апр 16, 12:56    [19042096]     Ответить | Цитировать Сообщить модератору
 Re: Вставка в таблицу на ликованном сервере  [new]
bacalavr
Member

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

да, по одной записи.

отклики между серверами не показали значительных различий(
11 апр 16, 12:59    [19042117]     Ответить | Цитировать Сообщить модератору
 Re: Вставка в таблицу на ликованном сервере  [new]
Gobzo Kobler
Member

Откуда: у людей такая фантазия?
Сообщений: 387
вообще при обращении к линкованным серверам лучше не пользоваться 4-компонентными именами, а обращаться через openrowset.
при обращении server.db.schema.object все записи засасываются на локальный сервер и там фильтруются и группируются.
openroset выполняется на прилинкованном сервере и поставляет уже отфильтрованный и сгрупированный набор записей.

поэтому тянуть данные следует так:

insert into table
select * from openrowset(server, 'select a, sum(b) from schema.table where field = '1' group by a')


а толкать большие объемы можно также исполняя динамический SQL, состоящий из блоков
insert into ... select...
штук по 100-1000 в зависимости от ширины записи.
11 апр 16, 20:35    [19044602]     Ответить | Цитировать Сообщить модератору
 Re: Вставка в таблицу на ликованном сервере  [new]
Сид
Member

Откуда: Москва
Сообщений: 305
Gobzo Kobler,

у ТС всего лишь несколько десятков тыс записей. Так что вариант с заталкиванием XML за один проход мне кажется более изящным.
Хотя, возможно, N пачек по M insert'ов могут оказаться быстрее. Интересно, попробует ли ТС оба этих варианта, чтобы рассказать, что у него работает лучше (быстрее).

С тем, чтобы тянуть, проблем вроде не было. Лично мне тоже удобнее использовать 4-компонентные имена, и производительность вполне удовлетворяет. Openrowset использую только если нужно дёрнуть ХП на линкованном сервере и затянуть к себе результат.
11 апр 16, 21:25    [19044764]     Ответить | Цитировать Сообщить модератору
 Re: Вставка в таблицу на ликованном сервере  [new]
invm
Member

Откуда: Москва
Сообщений: 9633
Gobzo Kobler
при обращении server.db.schema.object все записи засасываются на локальный сервер и там фильтруются и группируются.
Не обязательно.
Смотрите какой итератор используется в плане: Remote Scan или Remote Query.
11 апр 16, 21:28    [19044770]     Ответить | Цитировать Сообщить модератору
 Re: Вставка в таблицу на ликованном сервере  [new]
bacalavr
Member

Откуда:
Сообщений: 311
Сид
Gobzo Kobler,

у ТС всего лишь несколько десятков тыс записей. Так что вариант с заталкиванием XML за один проход мне кажется более изящным.
Хотя, возможно, N пачек по M insert'ов могут оказаться быстрее. Интересно, попробует ли ТС оба этих варианта, чтобы рассказать, что у него работает лучше (быстрее).

ТС попробовал)

результат обертки в xml оказался несколько хуже затягивания записей.
около 15 секунд занимает все время, при этом, на удаленном сервере парсинг и вставка - около 7 секунд, остальное, судя по всему, - обертка таблицы в xml.

вставка пачками по 1-30% занимает еще больше времени - от минуты до мнооого.

прямой запрос на затягивание данных - 2-3 секунды.
12 апр 16, 12:19    [19046516]     Ответить | Цитировать Сообщить модератору
 Re: Вставка в таблицу на ликованном сервере  [new]
Сид
Member

Откуда: Москва
Сообщений: 305
bacalavr,

Вы всё-таки затягиваете или заталкиваете? Затягивать к себе, естественно, будет гораздо быстрее, чем заталкивать на другой сервер.
Интересует сравнение между:
1) insert into server1.db.dbo.table select * from table
2) XML
3) толкать пачками (динамика на целевом сервере)

Самый быстрый и изначально очевидный вариант - затягивать удалённым сервером с сервера-источника (они же залинкованы друг на друга). Остальные предложения - на тот случай, если тянуть невозможно, а можно только толкать.
12 апр 16, 16:20    [19048235]     Ответить | Цитировать Сообщить модератору
 Re: Вставка в таблицу на ликованном сервере  [new]
Сид
Member

Откуда: Москва
Сообщений: 305
bacalavr,

ещё идея, как поступить, если инициатором должен выступать сервер-источник:
1) на целевом сервере делаем ХП, которая затягивает данные c сервера-источника
2) на сервере-источнике дёргаем эту ХП: exec server1.db.dbo.nekaya_stored_procedure

Если инициатором может выступать целевой сервер (например, выполнение по расписанию), то просто тянем, радуемся жизни и больше ни о чём не думаем))
12 апр 16, 16:25    [19048264]     Ответить | Цитировать Сообщить модератору
 Re: Вставка в таблицу на ликованном сервере  [new]
Glory
Member

Откуда:
Сообщений: 104760
Драйвер то какой использовался для server2 (11.0.6020.0) и для server3 (версия 10.50.4000.0 (X64)) ?
12 апр 16, 16:26    [19048269]     Ответить | Цитировать Сообщить модератору
 Re: Вставка в таблицу на ликованном сервере  [new]
StarikNavy
Member

Откуда: Москва
Сообщений: 2396
Сид,

тоже в итоге для оптимазции обменов, процедурки писать пришлось, чтобы дергали
12 апр 16, 17:56    [19048836]     Ответить | Цитировать Сообщить модератору
 Re: Вставка в таблицу на ликованном сервере  [new]
bacalavr
Member

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

вариант с прямой вставкой в удаленный сервер - в стартовом сообщении, полчаса
xml - 15-20 секунд
вставка пачками - от минуты до получаса

xml близок к идеалу, но при увеличении количества записей очень резко плохеет ему.

вариант с запуском удаленной процедуры, которая бы затягивала сейчас и реализован, но это как то неэтично, что ли)
12 апр 16, 23:05    [19049763]     Ответить | Цитировать Сообщить модератору
 Re: Вставка в таблицу на ликованном сервере  [new]
Сид
Member

Откуда: Москва
Сообщений: 305
bacalavr
Сид,

вариант с прямой вставкой в удаленный сервер - в стартовом сообщении, полчаса
xml - 15-20 секунд
вставка пачками - от минуты до получаса

xml близок к идеалу, но при увеличении количества записей очень резко плохеет ему.


Да, это ожидаемо, поэтому XML лучше использовать до 100 тыс записей примерно, а вообще сильно зависит от ширины. Это просто единственный вариант (из тех, что я нашёл), как затолкать на другой сервер сразу всё. Если записей миллионы, тут надо будет смотреть, где пределы. По моему опыту SQL Server (у меня 2014) один большой XML парсит гораздо быстрее, чем множество маленьких.
С затягиванием (идеалом) это сравнивать нет смысла из-за накладных расходов, о которых написано выше.

bacalavr
вариант с запуском удаленной процедуры, которая бы затягивала сейчас и реализован, но это как то неэтично, что ли)

Это очень даже этично. Совсем не этично - бесконтрольно заталкивать данные на другой сервер, не зная, что он в этот момент может с ними делать. А если это рабочая таблица, а не какая-то буферная, то можно и до катастрофы довести.
13 апр 16, 06:56    [19050195]     Ответить | Цитировать Сообщить модератору
 Re: Вставка в таблицу на ликованном сервере  [new]
Glory
Member

Откуда:
Сообщений: 104760
bacalavr
вариант с запуском удаленной процедуры, которая бы затягивала сейчас и реализован, но это как то неэтично, что ли)

bcp out
bcp in
правда прав надо больше
13 апр 16, 08:21    [19050274]     Ответить | Цитировать Сообщить модератору
 Re: Вставка в таблицу на ликованном сервере  [new]
bacalavr
Member

Откуда:
Сообщений: 311
Glory
Драйвер то какой использовался для server2 (11.0.6020.0) и для server3 (версия 10.50.4000.0 (X64)) ?


настроены сервера по умолчанию: Server Type = sql server. oledb провайдер получается SQLNCLI10?
13 апр 16, 10:35    [19050839]     Ответить | Цитировать Сообщить модератору
 Re: Вставка в таблицу на ликованном сервере  [new]
Glory
Member

Откуда:
Сообщений: 104760
bacalavr
Server Type = sql server. oledb провайдер получается SQLNCLI10?

Т.е. между 10.50.4000.0 и 10.50.4000.0 по SQLNCLI10 - быстро
а между 10.50.4000.0 и 11.0.6020.0 по SQLNCLI10 - медленно ?
13 апр 16, 10:39    [19050861]     Ответить | Цитировать Сообщить модератору
 Re: Вставка в таблицу на ликованном сервере  [new]
bacalavr
Member

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

да
но чувствуется какой то подвох в обычной вашей манере научить уму разуму.
на сервере версии 2012 нет провайдера "SQLNCLI10", но есть "SQLNCLI11".
13 апр 16, 10:48    [19050927]     Ответить | Цитировать Сообщить модератору
 Re: Вставка в таблицу на ликованном сервере  [new]
Glory
Member

Откуда:
Сообщений: 104760
bacalavr
но чувствуется какой то подвох в обычной вашей манере научить уму разуму.

Я только выдвигаю предположения о том, почему череда одинаковых серверных команд(они ведь одинаковые ?) приходит на удаленный сервер с разными промежутками по времени. Именно так выглядит же картина ?
Если вы думаете, что я протестировал все комбинации существующих версий/редакций серверов со всеми драйверами, то вы ошибаетесь
13 апр 16, 10:54    [19050958]     Ответить | Цитировать Сообщить модератору
 Re: Вставка в таблицу на ликованном сервере  [new]
bacalavr
Member

Откуда:
Сообщений: 311
Glory
bacalavr
но чувствуется какой то подвох в обычной вашей манере научить уму разуму.

Я только выдвигаю предположения о том, почему череда одинаковых серверных команд(они ведь одинаковые ?) приходит на удаленный сервер с разными промежутками по времени. Именно так выглядит же картина ?
Если вы думаете, что я протестировал все комбинации существующих версий/редакций серверов со всеми драйверами, то вы ошибаетесь


да, команды одинаковые)

можно ли теоретически установить провайдер SQLNCLI10 на машину с 2012 сервером?
13 апр 16, 10:57    [19050969]     Ответить | Цитировать Сообщить модератору
 Re: Вставка в таблицу на ликованном сервере  [new]
Glory
Member

Откуда:
Сообщений: 104760
bacalavr
можно ли теоретически установить провайдер SQLNCLI10 на машину с 2012 сервером?

Можно. Но драйвер - он на клиентской строне нужен, не на серверной.
13 апр 16, 11:02    [19050995]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить