Batch inserts

Yesterday I wrote how it took 3 hours and 13 minutes to migrate a table with slightly over 2M records. I then tried my hand at the 'times' table; I saw that this table contained slightly more than 14 million records and so the migration should take around 21 hours - which it did. At least the program didn't crash. Obviously I had to find a better method - and I did: batch inserts. 

Basically, an array of 2048 records is prepared and then is inserted at one go. This saves multiple commits thus saving time. A 'commit'  permanently saves all changes made during a transaction, making them visible to other users and ensuring data integrity. Naturally this takes a certain amount of time - even if it's a few milliseconds, that adds up when it has to be done 14M times. My batch code needed to do only about 6,900 commits that should save a great deal of time.

I wasn't prepared for how fast migrating a table of 14M would be: four minutes!! That's 315 times faster than the naive method of copying row by row.

The batch inserts were possible in this database because the three large tables are composed solely of integers and there's no need to faff around with codepage conversions.

Here's the code

const
 batchsize = 2048;

procedure Tq400IntMigrate.Button1Click(Sender: TObject);
var
 i, j, k, fieldcount, position: integer;
 tablename, s: string;

begin
 edit1.Text:= timetostr (time);
 button1.enabled:= false;
 for i:= 0 to componentcount - 1 do
  if components[i] is TCheckBox then
   if tCheckBox (components[i]).checked then
    begin
     tablename:= uppercase (tCheckBox (components[i]).caption);
     qCount.sql[1]:= tablename;
     qCount.open;
     pb.max:= qCount.fields[0].asinteger div batchsize;
     position:= 0;
     qDelete.SQL[1]:= tablename;
     qDelete.Connection:= newcon;
     qDelete.ExecSQL();
     sleep (5000);
     qBase.sql[1]:= tablename;
     qBase.open;
     fieldcount:= qBase.fields.count - 1;
     s:= 'select ';
     for j := 0 to fieldcount - 1 do
      s:= s + qbase.fields[j].fieldname + ', ';
     s:= s + qBase.fields[fieldcount].fieldname + ' from ' + tablename;
     SrcQuery.sql.text:= s;
     srcQuery.open;

     s:= 'insert into ' + tablename + ' values (';
     for j := 0 to fieldcount - 1 do
       s:= s + ':p' + inttostr (j) + ', ';
     s:= s + ':p' + inttostr (fieldcount) + ')';
     dstquery.sql.text:= s;

     dstQuery.params.ArraySize:= batchsize;
     dstQuery.prepare;

     k:= 0;
     while not srcquery.eof do
      begin
       for j := 0 to fieldcount do
        case srcquery.fields[j].datatype of
         ftInteger: dstquery.params[j].asintegers[k]:= srcQuery.fields[j].asinteger;
         ftLargeInt: dstquery.params[j].aslargeints[k]:= srcQuery.fields[j].asinteger;
         else dstquery.params[j].asintegers[k]:= srcQuery.fields[j].asinteger;
        end;
        inc (k);

       if k = batchsize then
        begin
         dstquery.execute (batchsize);
         dstquery.connection.Commit;
         k:= 0;
         inc (position);
         pb.position:= position
        end;
       srcquery.next
      end;

     if k > 0 then
      begin
       dstquery.execute (k);
       dstquery.connection.Commit;
      end;
    end;
 edit2.Text:= timetostr (time);
end;

Internal links
[1] 2104

---

This day in blog history:

Blog #	Date	Title	Tags
349	13/04/2011	Advanced SQL for me - NULLIF	Programming, SQL
696	13/04/2014	Fifteen minute meals	Cooking
827	13/04/2015	Vinyl log 3 - 13 April	DCI Banks, Richard Thompson, Vinyl log
1021	13/04/2017	April thesis update	DBA
1603	13/04/2023	Rain, rain, rain	Weather
1742	13/04/2024	Jasmine Myra - Knowingness	Jasmine Myra