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

The 'fx pedal to end all pedals' has ended its career

 The musical group had a knock-about session last night, playing anything that came into our collective head (eg 'Hotel California', 'Forever young' (Alphaville) and some Israeli songs - almost nothing that I have played before). Before we started, I checked out my pedal board and the fx pedal. I wrote ¹ two months ago that the 'fx pedal to end all pedals'² issues a fair amount of noise so in order to combat this, I set the noise gate to a level that let loud guitar through but not hiss. This caused the guitar to sound rather 'chunky' and lacking dynamics. Thinking about this on the way home, I realised that all the presets that I defined start with compression; maybe it would be better to create some presets with no compression and see what these sound like.

Before setting off for the rehearsal, I connected my phone to the pedal via bluetooth (and no small amount of bother) and attempted to remove the compression from all the settings. I think that I was partially successful in this. Despite my efforts, once I started playing, the sound from the amplifier was very weak. Exasperated, I disconnected the fx pedal from the system and suddenly my guitar volume increased ten-fold. I'll have to remove the pedal from the board and restore those that I previously removed.

On a completely different topic: yesterday was quite warm but this morning, when I was taking the dog for her morning walk, it started raining. Now that's not such an unusual event, but what makes it stand out is that the barometer in my head didn't give me a migraine. I did have an odd stomach ache in the evening, so this could be my body's new way of reacting, but it could also be due to the odd stomach rumblings from which I suffer now and then. So it looks like the new prophylactic pills³ that I have (that are meant for epileptics) are working.

Internal links
[1] 2074
[2] 2072
[3] 2095

---

This day in blog history:

Blog #	Date	Title	Tags
566	07/04/2013	A new technique in Word Automation	Programming, Office automation, HTML
822	07/04/2015	I've always kept a unicorn	Sandy Denny, Fotheringay
1119	07/04/2018	Season of the kumquat	Personal
1306	07/04/2020	Statistical methods for Epidemiologists	Statistics, Covid-19, Non-fiction books

Migration completed; now the debugging begins

At 5pm on Thursday afternoon, I completed the migration of the OP's management program with 354 units from ANSI Hebrew and dbExpress components to Unicode and FireDAC. When I write "completed", I mean that I had gone over every form and unit and replaced the dbExpress components with those of FireDAC. I also had briefly tested each form, but there was no guarantee that the program was free of bugs - in fact, I was fairly sure that the units that were converted first probably would not work correctly.

But before that, there was a topic that I had purposely not worked on, leaving it for the end. Almost all the forms that add data to the database have an autoincrement primary key; this is achieved by having a generator (something that returns a sequence of numbers, such as 1, 2, 3, ... 2001, 2002, etc) and a trigger that gets the next value from a generator and inserts it where needed. I didn't want functional generators when I was populating the new database from the old, but now that the translation had completed, I did need the generators and triggers for testing purposes. 

What I really needed was to drop all the generators and triggers before transferring any data, then to recreate them after all the data had been transferred. CoPilot got the dropping code partially correct - it would work if there was a trigger but wouldn't if there was no trigger. Eventually CP developed some complicated SQL code for a conditional delete. This also had to be done in the correct order: first delete the trigger then the generator; this is because the trigger refers to the generator. Recreating the generators and triggers was easy, at least when done in the correct order. Then the generators had to be seeded with their new starting value. Originally I wrote a small query that would return the maximum value in the key field and used this, but then I noticed that the migrator calculates how many rows are in a table (for a progress bar) so I used this - and shortly discovered that the number of rows is not necessarily the highest value of the key; there might have been deletions from the table. Eventually that was fixed and I migrated the database once more.

On Saturday morning, I started working through the program and found some simple problems to fix, for example queries that weren't attached to the database connection. I found more queries whose fields needed to be redefined, and I found some that were wrong because the columns to which they were connected were not defined correctly. This required a fix to the database and remigration of a single table. 

But there were some significant problems each of which took a few hours to fix. In what is probably the entire program's main form, the 'restore grid widths' routine wasn't working, although in fact, the correct values were returned but the grid 'ignored' them. The solution was to update the grid when all screen painting had finished, and so I added an OnPaint handler that sent a message to another procedure that would load the grid widths. This worked.

Another familiar problem occurred with a query called qSentReports: when certain reports are run, their HTML output is stored in a table so they can be retrieved at any stage. I had had problems when saving new reports in the table then restoring, so I assumed that retrieving reports that had been created in the previous version could be restored in a similar manner. But this was not so. According to CP, I was dealing with double or even triple encoding, involving WIN1255, WIN1252, UTF-16 and UTF-8. Then it struck me that the original data was already in UTF8 format, so the migrator shouldn't encode it and my code shouldn't decode it. After this epiphany, I once again transferred the data for this one table and finally it displayed correctly.

I am sure that there are further traps awaiting me, but I suspect that they will be minor. Now I don't know what to do with myself and all the free time that has suddenly opened up. 😉

---

This day in blog history:

Blog #	Date	Title	Tags
240	05/04/2010	Holiday	Cooking
346	05/04/2011	Firebird fixed!	Computers, Firebird
692	05/04/2014	Pneumonia	Health, Nick Drake
821	05/04/2015	Vinyl log 1 - 5 April	Vinyl log
937	05/04/2016	Sorrento shopping (2016/2)	Holiday, Sorrento, Italy
1118	05/04/2018	The sense of an ending (2)	Films, Literature
1207	05/04/2019	Excellent music blog	Beatles, Song writing
1597	05/04/2023	Passover night, 2023	Jewish holidays, Kibbutz
1920	05/04/2025	Slow cooked leg of lamb	Cooking, Slow cooker

2,100 blogs

Blog #2000 was written on 14/09/25, so that's 6.5 months for 100 blogs, or 15.8 blogs per month. Here is the histogram of blogs per month. The previous 100 blogs were written at an average pace of 14.3 blogs per month so I'm writing more.

I have to admit that I am surprised that 10/25 was such a fruitful month, although looking at what was written that month, I can understand. This was the beginning of the Prolog saga, a topic that would dominate the blog for the next few months - until a different programming project, the migration of a database and program to Unicode, began. Here are the blogs ordered by topic

Position	Tag	Count	Previous position	All time position
1	Programming	38	7	1
2	CoPilot	21	-	45
3	Prolog	21	-	39
4	Delphi	13	10	6
5	Personal	13	9	3
6	FireDAC	9	-	102
7	Unicode	8	-	50
8	Obituary	7	12	8
9	D12CE	6	-	142
10	Health	6	4	5
11	Israel	6	8	7
12	Computers	5	18	13
13	Films	5	-	18
14	Grandfather	4	-	37
15	Police procedurals	4	21	21
16	CPAP	3	-	23
17	Guitars	3	19	22
18	Musical group	3	5	27
19	Nick Drake	3	-	91

Of course, the period covered by these 100 blogs do not contain a holiday, so that's two topics off the board immediately. But it does seem that every six/seven months, my interests completely change. Here is the frequency histogram for the past 100 blogs.

---

Last night was the first night of Passover, and due to the security situation, we are back to the lock-down format¹ from the Covid-19 days. At least this time our daughter and grandchildren joined us.

Internal links:
[1] 1307

---

This day in blog history:

Blog #	Date	Title	Tags
124	02/04/2008	ERP	Programming, ERP, Maccabi Tel Aviv, Meta-blogging
820	02/04/2015	Introducing the vinyl log	Vinyl log
1117	02/04/2018	DBA update	DBA

Overconfident AI gets it wrong again

I wrote¹ a few days ago: I would also like to define metadata for the database. For example, if a report uses the field 'name' from the table 'customers' then automatically that field should have the title 'customer name' (in Hebrew) Similarly, 'surname' and 'forename' from table 'people' or 'name' from table 'therapists'. The fact that I want the title in Hebrew is incidental; even in English, the default name for customers.name is 'name', not 'customer name'. I put this to CoPilot who replied that this would be very easy to do: it requires a dictionary that translates between table names and Hebrew titles, and that the source for the dictionary would be a property of each field called 'origin'. The origin propery of a field containing a customer name would be CUSTOMERS.NAME.

I left this idea for a few days while I concentrated on list boxes, but yesterday I returned to continue work on this idea. I linked the 'find dictionary entry' procedure to a simple report and saw ... that it didn't work. The origin of a field CUSTOMERS.NAME was simply NAME and of course I have to distinguish between customer name, therapist name and activities.name, etc. CoPilot said that the table name didn't appear because the query was very simple and didn't invoke a join. So I looked at a more complicated query and saw that the origin property was empty for half of the fields! Only fields from the base table (ie the table after the keyword FROM) had origins, whereas the rest didn't.

OK, said CoPilot, you need the OriginTabName and OriginColName properties. "These do exist and will compile." Except they don't exist. After several rounds of CoPilot offering names of properties that sound plausible but don't exist, I told CoPilot that we were getting nowhere. CP agreed, saying that FireDAC doesn't expose sufficient metadata in order to provide a solution for what I wanted. Thank you very much. It wouldn't hurt to say once in a while that "I got it wrong".

In the end, I suggested and implemented a much simpler - and unfortunately, less automatic - solution: I created a stringlist and populated it with the Hebrew translations of 'customer name', 'contact name', 'person surname', etc. Each value has a specific number attached to it which is simply the offset within the stringlist: stringlist[1] = 'customer name', etc. In the FDQueries and FDMemTables in my code, I assign one of these values to a field's tag propery and then do the lookup on this property. In the extremely complicated form for sending an email, upon which I worked, at one stage it is necessary to display a list of therapists. Instead of having to title each field manually, I simply marked the tags as 56 and 58 and got the displaylabels that I wanted.

In conclusion: there are some things that CoPilot is good at, like seeing that a datasource was not enabled², or for building an ancestral dual list box dialog³. But for more creative solutions, CP is not particularly good and is even frequently wrong. And it still maintains that "This is reliable, simple, and works with all your existing forms". Yeah, right (the one time that two positives make a negative).

---

I've been so wrapped up in all this chat coding for the last few days that I didn't notice that this is blog #2100! The next blog will contain the traditional look at the previous 100 blogs.

Internal links
[1] 2097
[2] 2094
[3] 2099

---

This day in blog history:

Blog #	Date	Title	Tags
1384	01/04/2021	You and your action research project	DBA
1918	01/04/2025	Is this me or a double?	Personal