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

Wait states

I migrated another database to unicode over the weekend. Updating the migrator was fairly straight-forward, although there was one problematic table with something like 35,000 that was defined as WIN1251 (Cyrillic) although the characters were actually encoded in WIN1255 (Hebrew). That table took some time to define its migration route. Although this database has fewer tables than the first database that I converted and most of those tables contain only numbers with much less text, it took much longer to migrate the database - because some of the tables are huge (relatively speaking). There are a few tables with over two million rows and one with over ten million rows. 

Let us say that I had problems migrating those tables because of their size. I started the migration program yesterday evening only to discover that it had crashed whilst handling one of those humungous tables. I restarted the program from the table where it had crashed, but I could see that soon it too would crash. So what could I do?

I thought that the crashing might be due to the migration program overwhelming the database manager who simply could not keep up with the pace at which records were being entered. 30 years ago, this would be called 'adding wait states': a wait state is a deliberate, one-or-more clock cycle delay introduced by a processor to synchronize with slower external components, such as RAM or I/O devices. It prevents the CPU from processing data before it is ready, ensuring data integrity at the cost of overall system performance (thank you Chrome AI). 

The first thing that the migrator does for each table is delete any data that may exist in the table; I added the command 'sleep (15000)' after the deletion to allow the data to be deleted; this command stops the migrator for 15 seconds. I then added a command that there would be 10 second breaks after each 2048 records have been read. This was initially 15 seconds and 1024 records, but I could see that this would be too slow. For one table, with just over 2 million records with the sleep time was 10 seconds every 2048 records; it took 3 hours and 13 minutes to migrate the table.

I'm now migrating the really large table and don't expect it to finish within the next few hours - but I do expect it to complete.

---

This day in blog history:

Blog #	Date	Title	Tags
826	12/04/2015	How not to display data in graphs	Statistics
1121	12/04/2018	Apology to Israel Railways	Trains
1602	12/04/2023	More bells and whistles for the 'Blog' program	Programming, Delphi, Blog manager program
1741	12/04/2024	Update on our spring performance	Musical group
1921	12/04/2025	Passover begins tonight	Jewish holidays, Song writing