Загрузка объемных данных в рамках одной транзакции

← →
MsGuns © (2006-07-14 16:18) [0]

Требуется сделать следующее:
1. Создать в БД таблицу, если ее нет, или очистить от записей, если есть
2. Создать ХП вставки в таблицу
3. С помощью этой ХП из текстового файла загрузить в таблицу данные (порядок - от сотни тысяч до миллионов)
4. ХП удалить из БД
5. Транзакцию подтвердить.
6. В случае ошибки создания (очистки) таблицы или создания ХП транзакцию откатить, оставив таблицу в прежнем состоянии

Сейчас получается все, кроме отката, поэтому даже в случае прерывания процесса, в БД остается и ХП, и новые данные в таблице.

Посоветуйте что-нибудь в этом направлении. В BOL, к сожалению, не описаны подобные ситуации или я их не нашел, хотя искал две недели.

Спасибо за подсказки и советы

ЗЫ. MS SQL Server 8 + ADO (OLE DB)

← →
Desdechado © (2006-07-14 16:24) [1]

Не знаю как в скуле, а в оракле каждый оператор DDL выполняется в своей транзакции...

← →
Johnmen © (2006-07-14 16:26) [2]

В случае прерывания процесса убивать ХП, независимо от её существования, и зачищать таблицу.

← →
Stanislav © (2006-07-14 16:32) [3]

А может ХП не создавать? а прямо запросом заливать данные?

← →
MsGuns © (2006-07-14 16:44) [4]

>Johnmen © (14.07.06 16:26) [2]
В случае прерывания процесса убивать ХП, независимо от её существования, и зачищать таблицу.

Не выйдет. Проблема еще и в том, что сервер может быть "неустойчивый", т.е. коннект с ним может обрваться в любой момент (медленная линия и старй коммутатор)

>Stanislav © (14.07.06 16:32) [3]
>А может ХП не создавать? а прямо запросом заливать данные?

Загрузка данных через ХП ускоряет процесс в десятки раз.

← →
Johnmen © (2006-07-14 16:48) [5]

> MsGuns © (14.07.06 16:44) [4]
> Не выйдет. Проблема еще и в том, что сервер может быть "неустойчивый",
> т.е. коннект с ним может обрваться в любой момент (медленная
> линия и старй коммутатор)

Как всё плохо...
М.б. тогда ХП не трогать вообще? Пусть живёт?
Или же пересоздавать её.

← →
Val © (2006-07-14 17:09) [6]

предлагаю, все-таки, разделить dml и ddl:
1. попытка создать таблицу
2. попытка создать процедуру
3. в одной транзакции вся попытка закачки
4. если очень нужно - попытка удалить процедуру.

← →
MsGuns © (2006-07-14 17:18) [7]

>Val © (14.07.06 17:09) [6]
>3. в одной транзакции вся попытка закачки

Как миллион запросов INSERT "всунуть" в одну транзакцию ?

← →
Val © (2006-07-14 17:21) [8]

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

← →
Desdechado © (2006-07-14 17:22) [9]

> Как миллион запросов INSERT "всунуть" в одну транзакцию ?
А что, у скуля проблемы с размером буфера отката?

← →
MsGuns © (2006-07-14 17:30) [10]

>Val © (14.07.06 17:21) [8]
>? ты писал, что у тебя все получилось, кроме... ты миллион запросов всунул в транзакцию?

Получается все записать и явно закоммитить ВСЕ вставки на "быстром" сервере. На "медленном" после обрыва в таблице остаются успевшие добавиться записи. А мне навдо чтоб сервер сам откатывал (как в ИБ, например)

← →
Val © (2006-07-14 17:37) [11]

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

← →
Fay © (2006-07-14 21:32) [12]

2 MsGuns © (14.07.06 16:44)
> Посоветуйте что-нибудь в этом направлении
SET IMPLICIT_TRANSACTIONS { ON | OFF }

> Загрузка данных через ХП ускоряет процесс в десятки раз.
Бред

← →
tupoy (2006-07-17 17:14) [13]

bulk insert

← →
tupoy (2006-07-17 17:18) [14]

все сделает за Вас. почитайте BOL

← →
Fay © (2006-07-17 17:28) [15]

2 tupoy (17.07.06 17:18) [14]
Индексы тоже уберёт/создаст?

← →
tupoy (2006-07-17 17:40) [16]

Вы тоже почитайте

← →
MsGuns © (2006-07-18 09:20) [17]

>Val © (14.07.06 17:37) [11]
>Делай явный старт транзакции(без него, судя по всему у тебя каждый инсерт идет в своей транзакции, поэтому и остаются), инсерты, коммит.

1. Connection.BeginTrans
2. В цикле TADOCommand(cmdStoredProc).Execute. Объект "завернут" на Connection
3. Connection.Commit - Все записи остаются в таблице
3. Connection.Rollback - Иногда часть добавленных записей остается

>Fay © (14.07.06 21:32) [12]
>SET IMPLICIT_TRANSACTIONS { ON | OFF }

Делали. Не помогает.

>> Загрузка данных через ХП ускоряет процесс в десятки раз.
>Бред

Бред - это по поводу и без говорить "бред".
При записи простым "клиентским" INSERT INTO 1,150 000 записей занимает часы, через ХП - 20 мин. Проверенот неоднократно

>tupoy (17.07.06 17:14) [13]
>bulk insert

Не тот случай. Еслы внимательно читали ветку - поймете почему

>tupoy (17.07.06 17:18) [14]
>все сделает за Вас. почитайте BOL

Ничего и ничто ни за кого не делает. Если бы можно было воспользоваться этой фичей, то воспользовался бы.
BOL - настольная "книга". Как, впрочем, и MSDN (2006)

Прежде чем давать "вумные" советы, неплохо бы читать всю ветку. Внимательно.

← →
Медведъ (2006-07-18 09:30) [18]

>MsGuns © (18.07.06 09:20) [17]
упаковываем на клиенте записи в xml пакет, передаем его в процедуру в которой открываем транзакцию и insert into from openxml

← →
Медведъ (2006-07-18 09:31) [19]

>MsGuns © (18.07.06 09:20) [17]
а решения вида "В цикле TADOCommand(cmdStoredProc).Execute. Объект "завернут" на Connection" это просто очень плохо, даже хуже чем мой ник, извините

← →
evvcom © (2006-07-18 10:24) [20]

> При записи простым "клиентским" INSERT INTO 1,150 000 записей
> занимает часы, через ХП - 20 мин

А Insert в этом случае с параметрами? Если без, то естественно. ХП и запрос поставлены в неравные условия.
А нафиг вообще грохать ХП? Зачем мешать dml и ddl?

← →
Desdechado © (2006-07-18 10:51) [21]

как я понимаю, используется некий аналог IB-шных external table?

← →
Fay © (2006-07-18 11:19) [22]

2 MsGuns © (18.07.06 9:20) [17]
> При записи простым "клиентским" INSERT INTO 1,150 000 записей занимает
> часы, через ХП - 20 мин. Проверенот неоднократно

Без примера это просто слова. Проверенот неоднократно

← →
tupoy (2006-07-18 12:26) [23]

MsGuns © (18.07.06 09:20) [17]

перечитал ветку, но не увидел, чем Вас не устраивает bcp, ведь он для этих случаев и создан. Тут вам и прямая загрузка из файлов, и настраиваемая длина батчей, и максимально возможная производительность.
Впрочем, я не претендую на "вумность" cоветов, решайте сами.

← →
Fay © (2006-07-18 13:38) [24]

2 Медведъ (18.07.06 9:30) [18]
> упаковываем на клиенте записи в xml пакет
Приходилось ли самому мег по 100 так отправлять ?

← →
MsGuns © (2006-07-18 13:45) [25]

>Медведъ (18.07.06 09:30) [18]
>упаковываем на клиенте записи в xml пакет, передаем его в процедуру в которой открываем транзакцию и insert into from openxml

К сожалению, не знаком с этой технологией.

>evvcom © (18.07.06 10:24) [20]
>Fay © (18.07.06 11:19) [22]
>Без примера это просто слова. Проверенот неоднократно

Ну как я дам пример ? Есть БД на ЕС ЭВМ. Периодически надо из нее перетаскивать целостные фрагменты (по задачам АСУП) в MS SQL Server. При этом серверов несколько, а баз вообще десятки.
Таблицы сначала выгружаются из файлов ЕС БД в тескстовые файлы, затем конвертором заливаются на PC, после чего их надо записать в указанную конечным пользователем БД на указанном сервере. Выгрузка и конвертор написаны во времена царя Гороха - текстовые файлы без разделителей и с кучей ошибок - поэтому пакетная текстовая заливка (bcp) не "катит" (надо по спец.разработанным дескрипторам записи-строки "подправлять" до корректного формата данных.

Необходимость создания и убивания ХП обусловлена тем, что SQL БД множество и они могут плодиться как грибы - в каждую ХП не занесешь, тем более, что они разные по составу таблиц.

При записи Insert`ми делали и с параметрами, и без - разница почти никакая, эффект был получен лишь с заменой инсертов на ХП. Все поля в таблицах символьные - ключей, индексов и других ограничений нет. В SQL лишь добавлено одно поле во все таблицы - автоинкремент, который и является PK.

← →
Fay © (2006-07-18 13:52) [26]

2 MsGuns © (18.07.06 13:45) [25]
> При записи Insert`ми делали и с параметрами, и без - разница почти никакая
Это очень странно. С другой стороны, у MSSQL странностей хватает.

← →
Fay © (2006-07-18 13:53) [27]

2 MsGuns © (18.07.06 13:45) [25]
Какая скорость (Hz) при insert-ах с параметрами?

← →
Stanislav © (2006-07-18 13:55) [28]

Сделать Job, который должен запустится через 3 часа и удалить все что ненужно. :-)

← →
Stanislav © (2006-07-18 13:58) [29]

При записи простым "клиентским" INSERT INTO 1,150 000 записей занимает часы, через ХП - 20 мин. Проверенот неоднократно
Даже если клиент запущен на сервере?

← →
MsGuns © (2006-07-18 14:02) [30]

>Fay © (18.07.06 13:53) [27]
>Какая скорость (Hz) при insert-ах с параметрами?

Причем тут герцы ?
Сервера находятся на разных ПК, начиная от 500 и заканчивая двухпроцессорной системой. Базы тоже разные, сетки тоже (есть шустрые, а есть 10мегабитные). Сравнивали, ессно, на одной и той же процедуре.
Самый здоровый файл (больше лимона записей) загружался инсертами более 3 часов (при этом инсерты были параметрические и без - разница была несущественная.
Когда задействовали ХП, состоящую всего лишь из одного инсерта, время загрузки сократилось до получаса.
Это на быстрой сети, но с "медленным" сервером.
Пропорции, однако, сохраняются при загрузке на другие сервера. при этом общая нагрузка на сервер практически не влияла.
Да, и еще.. Сервера еще и разных версий. Есть парочка 7-х

← →
Fay © (2006-07-18 14:45) [31]

MsGuns © (18.07.06 14:02) [30]
Я имел ввиду "инсёртов в секунду"

← →
sniknik © (2006-07-18 15:32) [32]

> Самый здоровый файл (больше лимона записей) загружался инсертами
> Когда задействовали ХП, состоящую всего лишь из одного инсерта,
ничего не смущает? сравниваете более милиона вызовов пусть и параметрических с одним...
нет уж, вы либо в запросе аналогичный, один инсерт поставьте либо в процедуре на позаписную вставку (по записи на инсерт) разбейте. либо вообще не говорите что у вас было какоелибо "сравнение"...

команды обрабатывающие данные пакетами всегда быстрее, так уж исторически сложилось...

← →
Val (from Kiev) (2006-07-19 12:05) [33]

>MsGuns © (18.07.06 14:02) [30]
как читаются данные для вставки - в том же цикле, по строчке?
код вставки данных на клиенте д.б. таким:
start_transaction;
try
do_all_inserts;
commit;
except
rollback;
end;
на сервере отменить обертку каждого оператора в свою транзакцию.нужные ddl выполнять до и после приведенного псевдокода. иметь в виду, что ddl, в обычной практике автоматически, коммитит транзакцию.
>sniknik © (18.07.06 15:32) [32]
не с одним. насколько я понял автора в [17], автор просто обернул одиночный инсерт в процедуру и вызывает ее в цикле нужное количество раз. Не знаю как и что сравнивалось, но в теории параметрический инсерт в сравнении с такой процедурой(где тот же запрос) должен выполняться одинаково, если не быстрее.... не знаю особенностей мс скл, к сожалению, если автор говорит - процедура работает быстрее - это меня удивляет.

← →
Slym © (2006-07-19 12:10) [34]

MsGuns © (18.07.06 13:45) [25]
При записи Insert`ми делали и с параметрами, и без - разница почти никакая,
Insert.Prepared:=true;?

← →
Slym © (2006-07-19 12:11) [35]

И обязательно с параметрами

← →
Медведъ (2006-07-19 12:49) [36]

>MsGuns © (18.07.06 13:45) [25]
>К сожалению, не знаком с этой технологией.
что непонятно то, на клиенте в цикле пробешаемся по всем записям и форимруем строку типа
<PACKET>
<ROW GOODID="123" QTY="45.67" />
<ROW GOODID="124" QTY="3.1415" />
</PACKET>
передаем эту строку в процедуру с NTEXT параметром, далее читаем BOL OPENXML

CREATE PROCEDURE ImportOrder @AccId INT, @ImportMode INT, @Xml NTEXT AS
SET NOCOUNT ON
DECLARE @Tree INT
EXEC sp_xml_preparedocument @Tree OUTPUT, @Xml
INSERT INTO ...
SELECT T.GOOD, T.QTY FROM OPENXML(@Tree,"//ROW",2) WITH (GOOD INT, QTY INT) T
EXEC sp_xml_removedocument @Tree

а делать 10^6 запросов к серверу и ожидать при этом приемлемой производительности глупо

← →
Медведъ (2006-07-19 13:01) [37]

>Fay © (18.07.06 13:38) [24]
>Приходилось ли самому мег по 100 так отправлять ?
по 100 мег не приходилось, можно разбивать на блоки меньшего размера
будет на порядки быстрее чем "В цикле TADOCommand(cmdStoredProc).Execute"
впрочем я не настаиваю, пойду покушаю, у нас сегодня окрошка

так а если:
1 создать ХП
2 очистить табл ---
3 внести данные ---
4 в любом случае удалять ХП

2 и 3 это можно реализовать в одной транзакции в случае отката должна остаться старая таблица

← →
AlexAlex (2006-07-20 18:36) [39]

1. В Informix есть оператор Load для загрузки данных из текстовых файлов. Найдите аналог в MSSQL и будет вам скорость.
2. Так же может сущетвовать скоростной загрузчик, поищите у Microsoft
3. Попробуйте спросить на SQL.ru - там есть профильный форум

> поэтому пакетная текстовая заливка (bcp) не "катит"

Не "поэтому", а потому что кто-то BOL ленится читать.

См. раздел
Performing Bulk Copy Operations

BCP для таких вещей -- самое то что надо. Данные заливаются со скоростью молнии, смазанной салом.

Если кому интересно -- стукнитесь в почту или асю...
Вышлю дельфишные интерфейсный модули для ODBC и BCP, или примерчики какие-нибудь.

> Медведъ (19.07.06 13:01) [37]

XML ну никак не предназначен для заливки больших объёмов...

MsGuns © (14.07.06 16:18)
Требуется сделать следующее:
1. Создать в БД таблицу, если ее нет, или очистить от записей, если есть
2. Создать ХП вставки в таблицу
3. С помощью этой ХП из текстового файла загрузить в таблицу данные (порядок - от сотни тысяч до миллионов)
4. ХП удалить из БД
5. Транзакцию подтвердить.
6. В случае ошибки создания (очистки) таблицы или создания ХП транзакцию откатить, оставив таблицу в прежнем состоянии

1. Создать в БД таблицу вне зависимости есть данные или нет. Это быстрее, чем удалять записи
2.
3.
4. ХП удалить из БД вне зависимости от состояния
5.
6. при откате транзакции ничего делать не надо, кроме удаления ХП
поэтому операцию удаления ХП выносим за рамки пунктов 1-6

Все это можно сделать с помощью сгенерированого скрипта, в EM и после небольшой заточки использовать его.
Заливку даннных делать с помощью BCP из ранее подготовленных данных.
Возможны варианты.

DiamondShark © (23.07.06 10:28) [41]
Это точно, проверено на практике. Есть одна задача импорт данных из старой программы на основе DBF в MS SQL.
Ее можно выполнить двумя путями, через импорт с помощью XML или с помощью прямого импорта из DBF таблиц. Суммарный размер базы порядка 500 мегабайт.
Первый путь требует свыше 7 дней и гигабайты памяти, второй три часа и ничтожно мало памяти. Повальное увлечение новомодными технологиями до добра не доводит.

AlexAlex (20.07.06 18:36) [39]
В MS SQL есть несколько аналогов, например заливка с помощью DTS, скорость несколько десятков тысяц в секунду.

← →
Медведъ (2006-07-23 17:06) [45]

>Anatoly Podgoretsky
насчет повального увлечения - автор вроде ничего не говорил про 500 мегабайтные dbf-таблицы, к тому же
3. С помощью этой ХП из текстового файла загрузить в таблицу данные (порядок - от сотни тысяч до миллионов)

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

← →
Медведъ (2006-07-23 17:09) [46]

и вообще я привел програмное решение, если мне просто нужен импорт я замечательно пользую DTS Wizard

Медведъ (23.07.06 17:06) [45]
Ты потерял нить обсуждение, речь шла про повальное увлечение XML а не DBF и про то, что XML для таких объемов не подходят.

Медведъ (23.07.06 17:09) [46]
Вот и мы про тоже
В MS SQL есть несколько аналогов, например заливка с помощью DTS, скорость несколько десятков тысяц в секунду.

← →
Медведъ (2006-07-23 17:23) [49]

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

← →
Медведъ (2006-07-23 17:24) [50]

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

Загрузка объемных данных в рамках одной транзакции Найти похожие ветки