Не устраивает скорость работы INSERTов в ADO :-(

← →
BugMaker (2004-02-27 11:36) [0]

Добрый день. Тут возникла необходимость программно перетаскивать данные из Oracle в Access. Причем большое кол- во записей, около 3 миллионов. Так вот, какие- то 10 000 записей (всего три столбца) вставляются минуту (!!!) на P4, 256Мб ( при помощи TADOCommand.Execute и запросе INSERT). Это, друзья мои, как понимать, и вообще, может я чего- то делаю не так? Может есть побыстрее способ... Использование TADOTable.InsertRecord только усугубляет ситуацию, делая это еще в полтора раза медленнее... А с BDE связываться почему- то совсем не хочется :-). Хотя он должен оказаться раза в полтора быстрее...

← →
Sergey13 © (2004-02-27 11:40) [1]

2BugMaker (27.02.04 11:36)
А почему именно в аксес?

← →
Vlad © (2004-02-27 11:40) [2]

как данные перетаскиваешь ? Небось цикл по датасету делаешь while not eof ... ?
Есть хороший способ - подлинкуй в базе Аксесс таблицу Oracle, и одним запросом insert into .... select... from... перекинь данные.

← →
BugMaker (2004-02-27 11:51) [3]

2 Sergey13: В Access, потому что юзверя пошли шибко умные, в аксесе запросики делать научились, а может, и просто хотят ковыряться в миллионах строк %)).
2 Vlad: Да, цикл... Пока пробовал даже без оракла, просто генерятся инсерты с произвольными данными, пока для проверки скорости работы. А мысль хорошая, но не устраивает такой вариант. Непеменно нужны данные из датасета, к примеру.... Хотя остается еще вариант послать всех нафиг, и сказать, что это невозможно :-))))

← →
Vlad © (2004-02-27 11:58) [4]

> Непеменно нужны данные из датасета, к примеру....

Интересно, чем это вызвано ?
Ну, надо значит надо.
Если в таблице, куда инсерты делаешь, есть индексы, то лучше отключи их на время выполнения загрузки, так быстрее будет.

← →
VLAD-MAL (2004-02-27 12:04) [5]

Ну, в Access не обязательно через ADO...
Подсказка:

Он и текстовые форматы понимает.

Читаем все из Oracle, в формируем бо-о-льшой текстовый (например, CSV - формата) файл. Все. Если очень хочется, запускаем Access и, (к примеру, через OLE), заставляем его сделать импорт .CSV а затем - экспорт в твой любимый формат.

Удачи.

← →
BugMaker (2004-02-27 12:11) [6]

Индексов нет, есть только одно лишнее поле- счетчик. Его удаление дало 1 сек выигрыша (вместо 1 мин. 6 сек получлось 1м 5с :-)). Интересно, это ADO такой тормозной, или все они так не любят много инсертов? Так значит, других вариантов нет? Может там данные как- нть блоками писать можно? Вряд ли, конечно, но надежда умирает последней :-). Кстати, прямой импорт через ODBC занимает не более 3 сек :-(.

← →
BugMaker (2004-02-27 12:16) [7]

2 VLAD-MAL: Спасибо за идею, надо рассмотреть твой вариант :-). Попробуем :-). Это могло бы быть выходом, хотя, конечно, неприятно, что опять надо чинить авто через выхлопную трубу :-).

← →
VLAD-MAL (2004-02-27 13:07) [8]

Ну, либо красиво, но медленно, либо быстро, но оч-чень красиво!

← →
Карелин Артем © (2004-02-27 13:25) [9]

DisableControl делаем?
А однонаправленный набор данных?
Вот поэтому и тормозит. Особенно при отсутствии транзакции на время массового инсерта.

← →
sniknik © (2004-02-27 13:26) [10]

еще неизвестно как ты инсерт делаеш, на 10 000 тоже не минуту должно обрабатыватся.
select into ... из подсоедененного источника данных конечно быстрее, но может на подобном обьеме и "заклинить" от нехватки памяти. (перегонял 7миллионов, и полей побольше, так и произошло, кстати и стандартный импорт тоже загнулся. пришлось поделить на блоки по 500 тыс. и так загонять)

← →
Некто интересующийся (2004-02-27 13:46) [11]

3 миллиона записей для ACCESS много, если есть выбор, то лучше прейди на MSSQL

← →
sniknik © (2004-02-27 13:57) [12]

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

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

← →
BugMaker (2004-02-27 14:04) [13]

2 Карелин Артем: DisableControls имелся в виду? Никаких визуальных DBControl"ов не используется, BeginTrans и CommitTrans имеются перед циклом и после него, и что имеется в виду под однонаправленным набором данных, используются только TADOConnection и TADOCommand, а данные пока заливаются произвольные, то есть простой INSERT c одними и теми же данными в цикле (пока просто для проверки)?

2 sniknik: Поясни, что значит select into из подсоединенного источника данных? Как этот источник данных можно подсоединить к ADO (Oracle в данном случае)? Я не вижу вариантов, кроме как из датасета перекачивать %))

← →
Карелин Артем © (2004-02-27 14:14) [14]

BugMaker (27.02.04 14:04) [13]
DisableControls даже при отсутствии визуальных контролов увеличивает скорость. Правда не так сильно ;)

← →
BugMaker (2004-02-27 14:22) [15]

В общем, делается все примерно так, пристегните ремни %)))
////Типа модуль со всяким барахлом для работы с аксесом//// unit Access; interface uses ADODB, SysUtils, ComObj, OleDB; Type TAccess= class(TObject) Constructor Create; Destructor Destroy; override; Procedure CreateDataBase(FileName:String); Procedure Execute(Command:String); Procedure ConnectToBase(FileName:String); Procedure Disconnect; public private fConnection:TADOConnection; fCommand: TADOCommand; published Property Connection:TADOConnection Read fConnection write fConnection; Property Command:TADOCommand Read fCommand Write fCommand; end; implementation Constructor TAccess.Create; begin Inherited Create; fConnection:=TAdoConnection.Create(nil); fCommand:=TAdoCommand.Create(nil); fConnection.Provider := "Microsoft.Jet.OLEDB.4.0"; fConnection.LoginPrompt:=False; fCommand.Connection := fConnection; fCommand.CommandType:=cmdText; end; Destructor TAccess.Destroy; begin Disconnect; fCommand.Free; fConnection.Free; Inherited Destroy; end; Procedure TAccess.CreateDataBase(FileName:String); const dbLangGeneral = ";LANGID=0x0409;CP=1252;COUNTRY=0"; dbVersion30 = 32; var DBEngine, Workspace: Variant; begin Try DBEngine := CreateOleObject("DAO.DBEngine.36"); except try DBEngine := CreateOleObject("DAO.DBEngine.35"); except try DBEngine := CreateOleObject("DAO.DBEngine"); except raise; end; end; end; Workspace := DBEngine.Workspaces[0]; DeleteFile(FileName); try Workspace.CreateDatabase(FileName, dbLangGeneral, dbVersion30); except end; end; Procedure TAccess.Execute(Command:String); begin fCommand.CommandText:=Command; fCommand.Execute; end; Procedure TAccess.ConnectToBase(FileName:String); begin Disconnect; fConnection.ConnectionString:="Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+FileName+";Persist Security Info=False"; fConnection.Connected:=True; end; Procedure TAccess.Disconnect; begin fConnection.Connected:=False; end; end. ////А тут мучаем модуль выше//// procedure TForm1.Button1Click(Sender: TObject); var ac:TAccess; Index:Integer; BegTime,EndTime:TDateTime; Test:String; begin Ac:=TAccess.Create; Ac.CreateDataBase("C:\test.mdb"); Ac.ConnectToBase("C:\test.mdb"); Ac.Execute("CREATE TABLE BRED"+ "([name]String (150) WITH COMPRESSION,"+ "[context]String (150) WITH COMPRESSION,"+ "[datetimereg]datetime"+ ")"); ProgressBar1.Max:=10000; BegTime:=Now; Ac.Connection.BeginTrans; For index:=0 to ProgressBar1.Max do begin Ac.Execute("INSERT INTO Beda (name,context,datetimereg) VALUES (""Test Ж-)"","""+IntToStr(Random(1000000))+""","""+DateTimeToStr(Now)+""")"); ProgressBar1.Position:=index; end; Ac.Connection.CommitTrans; EndTime:=Now; ShowMessage(DateTimeToStr(EndTime-BegTime)); Ac.Free; end;
Написано кривовато, ногами не бейте :-)

← →
BugMaker (2004-02-27 14:26) [16]

Блин, имена таблиц BEDA и BRED прошу считать равнозначными, обшибся при корректировке неприличных названий :-)

← →
sniknik © (2004-02-27 14:30) [17]

> то есть простой INSERT c одними и теми же данными в цикле (пока просто для проверки)?
лутше бы показал как, чем заверять что все правильно. (он к примеру такой простой что ты его в цикле формируеш? или всетаки с параметрами, это на скорость оччччень влияет)

> Как этот источник данных можно подсоединить к ADO (Oracle в данном случае)?
c Oracle не работал поэтому тонкостей не скажу, только в общем (то что должно работать уверен на 98.7% т.к. пока что не встречал баз которые не смог подключить, разве что самодельные у которых драйверов просто нет)

у Oracle наверняка есть ODBC драйвер, так? значит можно сделать DSN который легко (в одно действие) подключить к аксесной базе (в аксессе файл->внешние данные->связь с таблицей выбираеш ODCB->созданный DSN). все в принципе операция insert into ... стандартная sql команда, выполняй где хочеш.
если у тебя есть OLEDB драйвер, еще проще даже DSN создавать не нужно сразу выбираеш.
и в принципе это же можно реализовать програмно, если подобные действия частые, можно и без DSN только с драйвером, прямо в запрос подключение вставить. (второй этап, если первый пройдет)

← →
sniknik © (2004-02-27 14:38) [18]

BugMaker (27.02.04 14:22) [15]
понятно.... :(

способы ускорения навскидку.
создай отдельно command внеси (за пределами цикла) запрос с параметрами, в цикле меняй только параметры и выполняй. параметры в строку не переводи, как есть число/дата так и оставляй.
+ очень крутое ускорение
ProgressBar1.Position:=index; делать не на каждую итерацию а на каждую 100ю или даже реже. глаз все одно не воспринимает так часто, а скорость очень подрастет.

еще созданная база получается формата офис 97?
dbVersion30 (Default) Creates a database that uses the Microsoft Jet database engine version 3.0 file format (compatible with version 3.5).

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

← →
BugMaker (2004-02-27 14:55) [20]

2 sniknik: К сожалению, все эти оптимизации - капля в море. Закомментировал прогресс бар, ни секундой меньше не стало, да и передача параметров- это столь же ничтожное ускорение будет. Даже если переписать на ассемблере, быстрее не будет (ну ладно, пять секунд можем выиграть, и то вряд ли :-)). Ну и потом по поводу ODBC ты видимо все же имел в виду импорт в аксес непосредственно? Подключиться- то можно при помощи ADO, но это будет другой Connection. А запросы вроде INSERT INTO... SELECT ... FROM.... можно выполнить только применительно к одному коннекту... И подскажи константку вместо dbVersion30, поскольку даже на MSDN мне не удалось найти версию старше (????!!!)

> Ну и потом по поводу ODBC ты видимо все же имел в виду импорт в аксес непосредственно?
не совсем но рядом, смотри там же link table

> да и передача параметров- это столь же ничтожное ускорение будет.
> Даже если переписать на ассемблере, быстрее не будет
зачем теоретизировать? замерь, переделай и снова замерь (10 тыс возможно и не заметно но 3млн. будет совсем другое дело. даже пустой цикл с приращением только одного прогресса) про параметры аналогично, думаеш перераспределение памяти когда строку формируеш бесследно проходит? а смысл их использовать в основном для того чтобы формирование запроса из цикла выкинуть. (и всех этому сопутствующих операций)

> И подскажи константку вместо dbVersion30
кончаются они на ней у дао, для ADOX выдели тут то что к делу относится

function TDMod.CreateMDBBase(BaseName: string): boolean; var AdoEngine: Variant; begin result:= false; if Length(BaseName)=0 then begin DoIfError("Не определено имя базы"); exit; end; try if Copy(UpperCase(BaseName), Length(BaseName)-3, 4) <> ".MDB" then BaseName:= BaseName + ".MDB"; AdoEngine:= CreateOleObject("ADOX.Catalog"); AdoEngine.Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+Trim(BaseName)+";Jet OLEDB:Engine Type=5;"); AdoEngine.ActiveConnection.Close; AdoEngine:= Null; result:= true; except on E: Exception do DoIfError(E.Message); end; end;

← →
BugMaker (2004-02-27 15:54) [22]

2 sniknik: Ух, спасибо :-). Теперь нормальная версия базы, но работает с новым форматом еще на 11 сек дольше %))). Минута и 17 секунд. Куда катится этот мир?! Ладно, буду ковырять свежеимпортированный модуль ADOX, мож наковыряю чего- нть полезное... Кстати, на 10тыс. работа со строками и прогресс бар совсем не отягощают процессор по сравнению с Jet"ом :-). Печально.

Насколько Вы хотите ускорить прцесс?
Если на какие-то проценты, то можно пробовать убивать индексы и прочие чеки.
Но в разы улучшить результат не удастся.
Даже оракл создает 10000 записей за 20 секунд.
(опробовано на компонентах SQLDirect + ODBC)
Чего хотеть от аккеса?

← →
BugMaker (2004-02-27 16:13) [24]

2 Курдль: Для локальной базы, где не нужно гонять пакеты по сети, как мне кажется, это просто нонсенс... Ну и потом импорт в аксесе, как я уже говорил, делает примерно то же самое менее 3 секунд. Индексов нет, см. выше. Затык явно в тормозном ADO. Ладно, как я понимаю, все тщетно, всем спасибо :-).

2 BugMaker
Иметь оракл в виде локальной базы - это даже не нонсенс, а волюнтаризЬм! :)
А на самом деле преклоняюсь перед Вашим упорством.
Мы юзерам, заявившим о миллионах записей автоматом впариваем оракл :)

Оппа... Это оно тормозит, будучи запущено из среды DELPHI. Если запускать отдельно приложение- 14 сек. (вместо 1 мин. 17 секунд в IDE). Вот это круто 8-().

Не устраивает скорость работы INSERTов в ADO :-( Найти похожие ветки