batch insert

[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!

[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.

[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;

-----------------------------------

[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;

[allegro]

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

[CristianP]

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]

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.

[allegro]

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

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?

[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.

[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');

[DemetrionQ]

Hello.

I answered to you at the topic http://forums.devart.com/viewtopic.php?t=27394