Как правильно закачивать в базу большие объемы данных?

← →
Defunct © (2005-08-04 05:14) [0]

обычный insert into (..) жутко медленно работает ~1k записей в сек.. Транзакцию закрываю через каждые 5k записей. В качестве исходных данных - текстовые файлы, объемом всреднем по ~500mb, чувствую если заливаться данные в базу будут как сейчас, то уйдет несколько недель.. Подскажите пож. способы ускорения работы insert"a

БД - IB6.5/FB1.5

← →
ЮЮ © (2005-08-04 06:21) [1]

>обычный insert into (..)

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

← →
Defunct © (2005-08-04 07:01) [2]

> мог бы и показать чуток того, что скрыто за ..
разумеется:

StartTransaction; if not IQuery.Prepared then begin IQuery.SQL.Add("insert into http_proxy(ID, FKEY, IP, MDATE, ANSWER, ASIZE, URL) "); IQuery.SQL.Add("values(:ID, :FKEY, :IP, :MDATE, :ANSWER, :ASIZE, :URL);"); IQuery.Prepare; end; IQuery.ParamByName("ID").AsInteger := id; IQuery.ParamByName("FKEY").AsInteger := ParsedRecord.fKey; IQuery.ParamByName("MDATE").AsFloat := ParsedRecord.Date; IQuery.ParamByName("IP").AsInteger := ParsedRecord.ip; IQuery.ParamByName("ANSWER").AsInteger := ParsedRecord.Ack; IQuery.ParamByName("ASIZE").AsInteger := ParsedRecord.Size; IQuery.ParamByName("URL").AsString := ParsedRecord.Url; IQuery.ExecSQL; Commit( False );
except
...

procedure TDataModule.StartTransaction; begin if Connected and (not DB.InTransaction) then DB.StartTransaction; end; procedure TDataModule.Commit( Forced: boolean ); begin if Connected then begin CS.Acquire; try if DB.InTransaction then begin inc(ChangesCount); if Forced or (ChangesCount > 5000) then begin ChangesCount := 0; DB.Commit; IQuery.UnPrepare; IQuery.SQL.Clear; end end finally CS.Release end end end;

← →
ЮЮ © (2005-08-04 07:51) [3]

1)

IQuery.Params[0].AsInteger := id;
...
IQuery.Params[5].AsString := ParsedRecord.Url;

2)

для каждой записи выполняется много телодвижений в StartTransaction и Commit

← →
Defunct © (2005-08-04 08:38) [4]

ЮЮ © (04.08.05 07:51) [3]

Спасибо, сделаю как Вы говорите!
Но настораживает вот что, CPU usage заливающей программы - 6-8%, в то время как выделенный сервер, куда заливаются данные загружен на 60-80% (такая загрузка появляется именно при исполнении приведенного в [2] кода). Может быть есть кардинально другой способ занесения данных в базу, не так сильно загружающий сервер?

Просто как-то не верится, что select * выполняется за считанные секунды, а insert на четыре порядка дольше.

← →
sniknik © (2005-08-04 08:51) [5]

> Просто как-то не верится, что select * выполняется за считанные секунды, а insert на четыре порядка дольше.
insern всегда дольше, вопрос лиш в том на оправданое время "дольше" или нет.

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

← →
ЮЮ © (2005-08-04 08:52) [6]

1) insert ещё и индексы модифицирует, целостность проверяет

2) select * все=таки одна инструкция серверу :)

выполни
select * ... where id = :id
в таком же цикле и оцени

← →
Digitman © (2005-08-04 09:08) [7]

> Defunct © (04.08.05 05:14)

перед проведением массированных вставок отключай соотв.индексы (если это, конечно, допустимо по логике) - их обновление всякий раз при вставке очер.записи занимает немалое время

ну и оптимизация кода в [2], конечно же, не помешает .. например, оператор WITH минимизирует "лишние телодвижения" в результирующем исп.коде :

вместо

IQuery.ParamByName("ID").AsInteger := ..
IQuery.ParamByName("FKEY").AsInteger := ..
и т.д. ..

следовало бы

with IQuery do
begin
..
Params[0].AsInteger := .. //обращение к параметру по его индексу в списке заведомо быстрее обращения по имени
Params[1].AsInteger := ..
..
Params[..].AsSomething := ..
..
ExecSQL;
..
end

> В качестве исходных данных - текстовые файлы

здесь тоже вполне м.б. "узкое место", и то как ты читаешь из файла - отдельный вопрос ..

← →
ANB © (2005-08-04 10:31) [8]

> например, оператор WITH минимизирует "лишние телодвижения"
> в результирующем исп.коде :

А как он оптимизирует ? И как быть в CBuilder ?
Это не наезд, просто интересно. Был как то давно на форуме сишников, так они делфийский with чморили, мол, толку от него мало и только источник ошибок.

Автору :
1) срубить индексы, триггера (если они не делают чего то очень нужного) и контстрейнты (если уверен в данных).
2) В цикле чтения - записи оставить только присвоение параметров.
3) Если основная загрузка - на сервере, то код у тебя почти оптимален, скорее всего ты не все запостил.

← →
Digitman © (2005-08-04 10:59) [9]

> ANB © (04.08.05 10:31) [8]

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

> они делфийский with чморили, мол, толку от него мало и только
> источник ошибок

если нет четкого понимания логики компилятора и/или руки кривые, то ошибки можно понаделать где угодно - хоть с WITH, хоть без WITH, хоть в Си, хоть ..

← →
ANB © (2005-08-04 11:08) [10]

> Digitman © (04.08.05 10:59) [9]

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

И, это, нарывался я на трудноотлавливаемую ошибку при использовании with. Шло закрытие свойства формы, так вот писал он в один объект, а читал из другого. Я пока вычислил это - запарился.

← →
Андрей Жук © (2005-08-04 11:55) [11]

Вставка через IBQuery???
Ты че?
Если надо очень быструю вставку - то или API (там получишь > 10 тыc. записей в секунду), или IBSQL. Ни в коем случае не IBQuery.

← →
Виталий Панасенко (2005-08-04 12:15) [12]

Примени CashedUpdates - будет быстрее

← →
Ольга (2005-08-04 12:16) [13]

Самое высокое быстродействие дает утилита BCP (Bulc Copy Program).

← →
DiamondShark © (2005-08-04 12:44) [14]

> Самое высокое быстродействие дает утилита BCP (Bulc Copy
> Program).

Для Интербейз?

← →
Андрей Жук © (2005-08-04 12:50) [15]

Еще можно загрущать используя Embedded версию - разработчики говорили, что это очень сильно повышает скорость.

← →
Desdechado © (2005-08-04 13:34) [16]

ANB © (04.08.05 11:08) [10]
> Я думал - компилятору WITH по барабану, это чтобы короче писать
это позволяет использовать регистровую адресацию объектов, что немножко быстрее

← →
Fay © (2005-08-04 15:05) [17]

2 Digitman © (04.08.05 10:59) [9]
>> компилятор в случае с WITH сгенерирует более эффективный код, нежели без WITH
В данном случае это не лимитирующий фактор. Валить лес лобзиком эффективнее.

← →
Fay © (2005-08-04 15:08) [18]

2 Андрей Жук © (04.08.05 12:50) [15]
>> разработчики говорили
Пошутили.

← →
Андрей Жук © (2005-08-04 15:12) [19]

> Пошутили.

?

← →
Fay © (2005-08-04 15:19) [20]

2 Андрей Жук © (04.08.05 15:12) [19]
Буквально. Я наблюдал только спад скорости.

← →
Виталий Панасенко (2005-08-04 15:25) [21]

> Виталий Панасенко (04.08.05 12:15) [12]
> Примени CashedUpdates - будет быстрее

Я не повторяюсь...Я не повторяюсь-2...Я не повторяюсь-3...
Это раельно даказано

← →
Digitman © (2005-08-04 15:36) [22]

> Fay © (04.08.05 15:05) [17]

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

автор показал лишь шматок кода, а вот что у него творится в реальности - это иной вопрос... не исключено что для реального кода уместное применение WITH даст неплохой результат прироста производительности сгенерированного кода

см. [16]

← →
Fay © (2005-08-04 15:45) [23]

2 Digitman © (04.08.05 15:36) [22]
>> применение WITH даст неплохой результат прироста производительности сгенерированного кода
И он (код) вдохновит свои примером СУБД? Не уверен.

← →
Андрей Жук © (2005-08-04 16:08) [24]

>
> автор показал лишь шматок кода, а вот что у него
> творится в реальности - это иной вопрос... не
> исключено что для реального кода уместное применение
> WITH даст неплохой результат прироста
> производительности сгенерированного кода

Ага, в 4%
Использование IB API даст прирост в 1000%.

Оптимизацию тут нужно проводить совсем не в языковых средствах.

← →
Desdechado © (2005-08-04 16:09) [25]

нагрузка клиента будет меньше
если он с сервером на одной машине, то серверу достанется больше ресурсов
вот такое вдохновление

← →
Desdechado © (2005-08-04 16:10) [26]

[24]
может, автор не хочет затачивать под конкретную СУБД

← →
ANB © (2005-08-04 16:19) [27]

Так автор же написал, что у него не клиент жрет ресурсы, а процесс сервера. Когда будет наоборот - надо будет оптимизить клиента.

← →
Defunct © (2005-08-04 23:00) [28]

sniknik © (04.08.05 08:51) [5]
ЮЮ © (04.08.05 08:52) [6]
Digitman © (04.08.05 10:59) [9]

ковырял весь день, ваши советы очень помогли, спасибо!
Отключение индексации в три раза ускорило добавление данных. но все равно медленно. Для сравнения выборка по 3M записей выполняется ~10 сек, добавление 3M записей - 2 часа.

ЮЮ © (04.08.05 08:52) [6]
> 2) select * все=таки одна инструкция серверу :)

Нет ли способа групповой заливки данных? Так чтобы тоже одной командой ;>

>ANB © (04.08.05 10:31) [8]
> 3) Если основная загрузка - на сервере, то код у тебя почти оптимален, скорее всего ты не все запостил.

Запостил все как есть... Я же не враг себе чтобы что-то скрывать.

> Андрей Жук © (04.08.05 11:55) [11]
> Вставка через IBQuery???
Нет просто экземпляр, назвал так IQuery - inserting query.
В программе имеют место еще UQuery для апдейтов, SQuery и VQuery для селекта и визуализации. На момент вставки работает только IQuery.

> Ни в коем случае не IBQuery.
IQuery : TSDSQL;

> Андрей Жук © (04.08.05 16:08) [24]
> Использование IB API даст прирост в 1000%.
Подскажите пожалуйста как это сделать (я никогда не работал с IB API). Очень актуально для решения задачи. Поскольку похоже тормозит здесь TCP.

> Виталий Панасенко (04.08.05 15:25) [21]
Где можно почитать про это?

>Defunct © (04.08.05 23:00) [28]
Использование IBScript будет на порядки быстрее.
Т.е. сначала генерим скрипт, загоняем его в
IBScript и выполняем. Проциями по 1к инсертов вполне нормально должно прокатить.
У меня, к примеру, ~300к записей за 15-20 минут принимаются на PIII,
причем через процедуру, с недецкими вычислениями, в таблицу с множеством индексов и т.п.

Полная логика вставки такова:

while not LogReader.Eof do begin hrec := LogReader.ReadHttpRecord; DataModule.AddHttpRecord( hrec ); end;

Процедура AddHttpRecord описана в 2. Цикл выполняется для 3M записей (~500mb файл) 2 часа (загрузка проца после оптимизации - 5-7%).
Сам по себе парсинг происходит достаточно быстро, если закоментировать
// DataModule.AddHttpRecord( hrec );
то загрузка проца - 100% и парсится за 2 минуты.

P.N.P. © (04.08.05 23:26) [29]

Скрипт - имеется в виду список статических запросов вида
insert ....(field1, field2 и т.п.) value(X, Y и т.п.)?

А если вставлять так:
IQuery.SQL.text:=" begin insert into...; ... insert into...; end;"
?

> Андрей Жук © (04.08.05 16:08) [24]

> Оптимизацию тут нужно проводить совсем не в языковых средствах

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

Я прекрасно понимаю, что львиная доля "тормозов" в дан.случае приходится на неоптимальную и неэффективную логику работы с СУБД, но ведь и оставшуяся малую часть "тормозов" тоже не будет лишним устранить !

Удалено модератором
Примечание: дубль

← →
alex_*** (2005-08-05 17:08) [38]

а смысл... ситуации это не исправит. только потерянное время.

В общем выжал все что можно.
3.5k записей в секунду (при TCP соединении).
IBSQL
SDSQL
параметрические запросы.

Попробовал IBScript - в два с половиной раза медленней - 1.4k записей в секунду.

> Sam Stone © (05.08.05 13:44) [34]
это не будет работать вообще. только IBScript и IBupdateSQL(возможно?) такое позволяют, но работают медленнее чем IBSQL/SDSQL.

> Digitman
Да оптимизировать больше нечего. но и так не плохо, уже не несколько недель заливать придется, а всего несколько дней. Спасибо за совет с отключением индексации.

>Defunct © (05.08.05 21:21) [39]

при TCP соединении

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

Как правильно закачивать в базу большие объемы данных? Найти похожие ветки