[unit] TMultiInsert - for speeding up multiple transfers
Posted: 30.10.2009, 18:12
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.
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 :
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...
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;
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>);
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...