Continuing to search on two columns

On Friday, I wrote about the problems that I was having in exporting data to Excel then sorting by two columns. As I couldn't do this by automation, I found a baroque method of sorting the strings in a stringlist prior to transferring them to Excel. After considering the matter, I thought that it might be better to take an SQL approach by saving the intermediate data into a temporary table (a non-connected clientdataset [cds]) then use SQL to return the data sorted by however I would want it. 

So yesterday I converted the code into something more streamlined that would either insert data into the cds (first pass, values for respondents who took the test before a given date) or edit the data (second pass, values for those who took the test after a given date). The first part worked perfectly but something didn't work on the second pass; eventually I realised that for some reason the locate method of the cds was not locating the required tuples.With little choice in the matter, I had to write a short loop that is the equivalent of the locate method; it might even be faster as it doesn't need to check the flags that are passed as the third argument to locate.

Once this little problem was out of the way, I could concentrate on sorting by two columns, the raison d'etre of the code. This was quite simple:

 s:= field1 + ';' + field14;
 qData.addindex ('idx0', s, [], '', '', 0);
 qData.indexname:= 'idx0';

'Field1' is the scale name and field14 the difference. I thought it best to create the index name in a separate statement before creating the index itself. Originally I omitted the third statement and couldn't understand why the data wasn't sorted; stupid mistake.

I had inserted a statement start:= gettickcount; at the beginning of the procedure and a matching finish:= gettickcount; at the end (before exporting the data to Excel) so that I could see how much of a difference the cds approach made. The csv version took 781 ticks (I ran the procedure a few times and received this answer most of the time - it surprised me that two runs would require exactly the same number of ticks). The cds approach took about 125 ticks - six times faster!

Why am I not surprised? I suppose now that I will have to go over code in this program as well as in the management program, looking for code that creates a csv stringlist that is exported to Excel. The queries in most of these places can probably be sped up.

---

This day in history:

Blog #	Date	Title	Tags
265	21/07/2010	It's the 80s all over again	Programming, Prolog, Bill Thompson
498	21/07/2012	Trivets and punnets	Slow cooker
1245	21/07/2019	Saturday swimming	Swimming