[unit] TMultiInsert - for speeding up multiple transfers

[Gobol]

Hi,

I'd like to share my code, a class TMultiInsert, which descendens from TClientDataSet for simplicity.

I think that example would be the best description.

Let's assume, we're having a table in our database, correctly connected via ZConnection. This table we'd like to fill up with some values... some more values
We need TZQuery or TZTable to obtain FieldDefs structure of given table.

Code: Select all

uses u_multinsert;

var MI: TMultiInsert;

// initialization
  MI:=TMultiInsert.Create(nil);
  MI.DescTable:='documents';   // name of our table
  MI.CreateStructure(ZQuery1.FieldDefs);
  MI.Connection:=ZConnection1;
  MI.CreateDataSet;

  // maximum query size = max_data_packet of server
  // if it is too big, then server will kill our connection
  // because of sending too big packet...
  MI.MaxQuerySize:=1000000; 

// inserting data into MI

while <someloop> do
begin 
  MI.Append;   // or .Insert;
  MI.FieldValues['field1']:=.... sth;
  MI.FieldValues['field2']:=.... sth;
  MI.Post;
end;

// make final query
  MI.MakeSQL;
// execute query, splitting it into smaller parts when
// necessary
  MI.ExecSQL;

// finalization
  MI.Free;

What it does ?

MI as TClientDataSet is initialized with destination's table field definitions. The in-memory temporary dataset is created.
Then MI is filled with variables in common manner : Insert, assign , Post.
Then MI.MakeSQL creates multiple row insert query, formatted as follows :

Code: Select all

INSERT INTO <DestTable> (<FieldNames>) VALUES 
(<values for row 0>),
(<values for row 1>),
(<values for row 2>),
(<values for row n>);

MI.ExecSQL sends that query to internal TZQuery object, splitting query when necessary to avoid crossing MaxQuerySize boundary.

That's basically all.
Class is not tested in all possible conditions.
Feel free to use it.
I know it is not very optimized, but the code is at least 10-100x faster than executing single query for each row.

TESTED ON : MySQL 5.1.39, Delphi7, Zeos 6.6.5.

PS. Sorry for my english...

[mparak]

Sounds like its worth playing with.
Thanks
Mo