Improving a query in the Management program

The management program that I maintain for the OP has had a comments table 'from time immorial'; depending on a field in the table, a comment can be connected to a docket, a therapist or to a project. From these screens, the appropriate comments can be seen (otherwise known as vertical access); i.e. for a given docket, one can easily see the comments for that docket.

A few months ago, I added the concept of a 'comment type' as we had introduced certain automatic comments; whilst these comments can still be in the vertical manner, there was no way of seeing all comments of a given type (aka horizontal access). So I developed a report that showed all the comments, depending on the various parameters chosen. As a comment regarding a therapist is somewhat different from a comment regarding a docket, the query that I had to write required a case statement in order to selectively display data. As this query was written in the 'modern age', it included all the bells and whistles developed in the past (i.e. adding parameters in a dynamic fashion).

select comments.curdate, comments.subject, comtypes.name, 
case when comments.sug = 'D' then comments.docket
when comments.sug = 'T' then therapists.name
else comments.docket end as avalue,
customers.name, comments.comment
from comments
left join comtypes on comtypes.id = comments.comtype
left join therapists on therapists.id = comments.docket
left join xtracust on xtracust.docket = comments.docket
left join customers on customers.id = xtracust.customer
where 1 = 0

This worked, but yesterday I noticed that the screen took a long time to open; the query itself won't return any records because of the 'where 1 = 0' clause, but apparently the SQL parser still has to parse the entire statement.

This morning I woke up thinking about this query and how it could be improved. At first I couldn't see how to do this, but a bit later, I realised that the default query (as shown above) is replaced by a more dynamic query when the program runs; the purpose of the default query is only to define the fields that will be displayed in the database grid, and to define the indexes on the basis of those fields. So all I need do was write the most minimal query that showed the same fields. This became

select comments.curdate, comments.subject, comtypes.name, 
cast (comments.docket as varchar (32)) as avalue,
customers.name, comments.comment
from comments
left join comtypes on comtypes.id = comments.comtype
inner join xtracust on xtracust.docket = comments.docket
inner join customers on customers.id = xtracust.customer
where 1 = 0
and comments.sug = 'P'

There's no need for left joins: removing them speeds up the query. Unfortunately, I discovered that the 'case' statement had concealed a problem: in the 'avalue' field was being displayed either a number (comments.docket) or a string (therapists.name). Once I had removed the 'case' statement, I had to display a string as the fourth field, so I had to use the extremely ugly 'cast...' function. Why cast the docket number to a string when I could use the therapist's name (after all, there are fewer comments for therapists than for dockets, although there are fewer comments for projects than for therapists)? Because in the context of retrieving therapist comments, there is no concept of customers, making the 'customers.name' field even more complicated than using the cast.

Once I had done this, I realised that by the time the dynamic query is built, the screen already 'knows' whether it is to display comments from dockets, therapists or projects, so there's no need for the 'case' statement in the live query. The query itself has to change according to which type is chosen; in the end I defined several constants (as shown below) then built the query line by line.

const
 q1 = 'select comments.taarich, comments.subject, comtypes.name, ';
 q2 = 'cast (comments.docket as varchar (32)) as avalue, customers.name, ';
 q3 = 'therapists.name as avalue, cast (''-'' as varchar (32)) as name, ';
 q4 = 'comments.comment from comments ';
 q5 = 'left join comtypes on comtypes.id = comments.comtype ';
 q6 = 'inner join xtracust on xtracust.docket = comments.docket ';
 q7 = 'inner join customers on customers.id = xtracust.customer ';
 q8 = 'inner join therapists on therapists.id = comments.docket ';
 q9 = 'inner join projects on projects.id = comments.docket ';
 q10 = 'inner join customers on customers.id = projects.customer ';
 q11 = 'where 1 = 1';
 
[following is the code that builds the query]
 s:= q1;  // this is the query string
 sug:= GetSug; // 'sug' is Hebrew for 'type', a reserved word
 if sug = 'T'
  then s:= s + q3
  else s:= s + q2;
 s:= s + q4 + q5;
 case sug[1] of
  'D': s:= s + q6 + q7;
  'T': s:= s + q8;
  'P': s:= s + q9 + q10
 end;
 s:= s + q11;

This strange code builds the query as according to the type. Note that if the type is 'T' (i.e. therapist) then the value for the customer name is '-'.

Thinking about the above now (a few hours after having implemented the above), I've noticed one other problem that is not with the SQL code but with the parameters. On the screen is displayed a combo box from which the customer can be chosen. As I've noted above, this makes no sense in the context of therapists; I should add to the 'rgSugExit' event handler to update the combox box data with those of the therapists should the T option be chosen; this event handler already reloads the 'comment type' combo box with the types applicable to the comment type. After looking at the code one more type, I see that in fact the customers combo box is hidden (but not its label!) if the T option is chosen. This is almost correct but a little more needs to be done.

---

This day in history:

Blog #	Date	Title	Tags
118	28/01/2008	Brains and mirrors	Psychology, Social intelligence, Martin Seligman, The brain
1374	28/01/2021	Cormoran Strike	TV series, DCI Banks, Cormoran Strike