Page 1 of 1
batch insert
Posted: Wed 15 May 2013 07:39
by allegro
Using unidac how to implement functionality similar to the addBatch/executeBatch in Java in order to improve the performance of huge amounts of data insert into DB? Best able to provide example code. Thank you!
Re: batch insert
Posted: Wed 15 May 2013 13:04
by DemetrionQ
Hello.
UniDAC has a TUniLoader component designed for fast data loading to a DB. You can find detailed information in the UniDAC documentation. You can also have a look at a TUniLoader usage sample in the UniDACDemo\Loader demo.
Re: batch insert
Posted: Wed 15 May 2013 14:53
by allegro
Here is a piece of code in my app, is not well suited to using uniloader...
-----------------------------------
unqry2.SQL.Text := 'select * from t2';
unqry2.CachedUpdates := True;
unqry2.Options.UpdateBatchSize := 2000;
unqry2.Open;
unqry1.SQL.Clear;
unqry1.SQL.Add('select t1c1, t1c2, t1c3, t1c4, t1c5, t1c6 from t1');
unqry1.Open;
con2.StartTransaction;
for i := 1 to 1999 do
begin
unqry2.Append;
unqry2.FieldByName('t2c1').AsInteger := unqry1.Fields.Fields[1].AsInteger;
unqry2.FieldByName('t2c2').AsInteger := unqry1.Fields.Fields[2].AsInteger;
unqry2.FieldByName('t2c3').AsInteger := unqry1.Fields.Fields[3].AsInteger;
unqry2.FieldByName('t2c4').AsFloat := unqry1.Fields.Fields[4].AsFloat;
unqry2.FieldByName('t2c5').AsFloat := unqry1.Fields.Fields[5].AsFloat;
unqry2.FieldByName('t2c6').AsInteger := unqry1.Fields.Fields[6].AsInteger;
unqry2.Post;
unqry1.Next;
end;
unqry2.ApplyUpdates; // Most of the time consumed in here
con2.Commit;
-----------------------------------
Re: batch insert
Posted: Wed 15 May 2013 20:53
by Tadeus
Have you tried to do an insert like this?
unqry.SQL.Clear;
unqry.SQL.Add('INSERT t2 (t2c1, t2c2, t2c3, t2c4, t2c5, t2c6)');
unqry.SQL.Add('SELECT t1c1, t1c2, t1c3, t1c4, t1c5, t1c6');
unqry.SQL.Add('FROM t1');
unqry.ExecSQL;
Re: batch insert
Posted: Thu 16 May 2013 06:21
by allegro
Tadeus wrote:Have you tried to do an insert like this?
unqry.SQL.Clear;
unqry.SQL.Add('INSERT t2 (t2c1, t2c2, t2c3, t2c4, t2c5, t2c6)');
unqry.SQL.Add('SELECT t1c1, t1c2, t1c3, t1c4, t1c5, t1c6');
unqry.SQL.Add('FROM t1');
unqry.ExecSQL;
I pasted is a pseudo code, the actual data source from a text file, calculate results, network traffic, and so on
Re: batch insert
Posted: Thu 16 May 2013 09:15
by CristianP
allegro wrote:Here is a piece of code in my app, is not well suited to using uniloader...
Hello,
With UniLoader you need this code:
Code: Select all
UniLoader1.TableName := 't1';
UniLoader1.LoadFromDataSet(unqry2);
And if your data in not from TDataSet you need to define UniLoader.Columns, call UniLoader.CreateColumns and write one of the UniLoader.OnPutData or UniLoader.OnGetColumnData event handlers.
Finally call UniLoader.Load
With this you can load data from any source.
Best Regards,
Cristian Peta
Re: batch insert
Posted: Fri 17 May 2013 17:48
by DemetrionQ
Hello.
CristianP described using of TUniLoader correctly. The detailed information about OnPutData and OnGetColumnData event handlers can be found in the UniDAC documentation. If you have any other questions, please contact us again.
Re: batch insert
Posted: Sat 18 May 2013 11:09
by allegro
CristianP wrote:allegro wrote:Here is a piece of code in my app, is not well suited to using uniloader...
Hello,
With UniLoader you need this code:
Code: Select all
UniLoader1.TableName := 't1';
UniLoader1.LoadFromDataSet(unqry2);
And if your data in not from TDataSet you need to define UniLoader.Columns, call UniLoader.CreateColumns and write one of the UniLoader.OnPutData or UniLoader.OnGetColumnData event handlers.
Finally call UniLoader.Load
With this you can load data from any source.
Best Regards,
Cristian Peta
DemetrionQ wrote:Hello.
CristianP described using of TUniLoader correctly. The detailed information about OnPutData and OnGetColumnData event handlers can be found in the UniDAC documentation. If you have any other questions, please contact us again.
Great!! Easy to insert more than 13,000 pieces of records per second. But if the batch update or delete, how to improve the speed?
Re: batch insert
Posted: Mon 20 May 2013 16:10
by DemetrionQ
Hello.
When editing or deleting data, performance can be increased in the following way:
1) Using transactions (e.g., using the TUniConnection.StartTransaction method).
2) Preparing SQL queries (by setting the TUniQuery.Options.AutoPrepare and TUniQuery.Options.PrepareUpdateSQL options to True).
3) Using cached updates for a dataset, having set the TUniQuery.CachedUpdates property to True.
You can find more information about increasing performance in the "Increasing Performance" article of the UniDAC help.
Re: batch insert
Posted: Tue 18 Jun 2013 05:20
by allegro
When I insert record into Oracle with uniloader, How to use auto increase column?
eg, insert tab_a (id, name) value (id.nextval, 'tom');
Re: batch insert
Posted: Fri 21 Jun 2013 10:48
by DemetrionQ