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

← →
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 часа и удалить все что ненужно. :-)

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

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

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

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

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

← →
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], автор просто обернул одиночный инсерт в процедуру и вызывает ее в цикле нужное количество раз. Не знаю как и что сравнивалось, но в теории параметрический инсерт в сравнении с такой процедурой(где тот же запрос) должен выполняться одинаково, если не быстрее.... не знаю особенностей мс скл, к сожалению, если автор говорит - процедура работает быстрее - это меня удивляет.

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

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

← →
Медведъ (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, или примерчики какие-нибудь.

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