I create an in-memory table (lots of joins for things like company, name etc) called Project for alarm calls. What I want to do as I flip between the form showing the alarms and other forms and back is only refresh Project if I need to. Currently I'm doing a simple test on the number of rows in Project and the base Calls table using the procedure below. Its obviously not foolproof, but its fast. What I need to do is test _CallID from Calls with _fkCalls in Project. Any suggestions for a nice FAST way to do that

procedure TProjectForm.DoFakeFormActivate(var Message: TMessage);
var
CntrChk: string;
CallsToShow: integer;
begin
FormActivate(Self);
if DoneActivate and (tagMode = tmAlarms) then begin
 CntrChk := 'SELECT _CallID FROM Calls';
 CntrChk := CntrChk + ' WHERE ';
 CntrChk := CntrChk + Project.Filter;
 CallsToShow := dm.DB.Execute(CntrChk);
 if CallsToShow <> Project.RecordCount then PostMessage(Self.Handle, DoRefreshRequest, 0, 0);
end;
end;


Roy Lambert

I've come up with


procedure TProjectForm.DoFakeFormActivate(var Message: TMessage);
var
CntrChk: string;
CallsToShow: integer;
begin
FormActivate(Self);
if DoneActivate and (tagMode = tmAlarms) then begin
 CntrChk := 'SELECT _CallID FROM Calls';
 CntrChk := CntrChk + ' WHERE ';
 CntrChk := CntrChk + Project.Filter;
 CallsToShow := dm.DB.Execute(CntrChk);
 if CallsToShow <> Project.RecordCount then PostMessage(Self.Handle, DoRefreshRequest, 0, 0) else begin
  CntrChk := CntrChk + ' AND NOT _CallID IN (SELECT _CallID FROM Memory.' + Project.TableName + ')';
  CntrChk := CntrChk + ' UNION ';
  CntrChk := CntrChk + ' SELECT _CallID FROM Memory.' + Project.TableName;
  CntrChk := CntrChk + ' WHERE NOT _CallID IN (SELECT _CallID FROM Calls WHERE ' + Project.Filter + ')';
  CallsToShow := dm.DB.Execute(CntrChk);
  if CallsToShow > 0 then PostMessage(Self.Handle, DoRefreshRequest, 0, 0)
 end;
end;
end;


Any improvements appreciated.

Roy Lambert

Roy

If  _callid is an auto incrementing field testing the highest _callid in calls against the highest one in project and
the recordcount should determine whether a records have been added or deleted.

John.

John

>If _callid is an auto incrementing field testing the highest _callid in calls against the highest one in project and
>the recordcount should determine whether a records have been added or deleted.

It is but (I love a good but) Calls holds a list of calls to be made / have been made for all of the projects. My project form is used for several purposes - calls on a project:

call backs (ie calls I'm waiting for a call back)
headhunts (people who have promised a cv)
alarms - when a call has been promised
project - general cold calling project

The last one is an extract of the calls for a specific project from the list of calls, The first three may be across several projects.

So a long winded way of saying that the last record in Calls and the last one in Project may or may not be the same eg if there's no alarm set for that call it won't be in Project.

Roy Lambert

John


And even if the top and bottom CallIDs match there's no guarantee that the ones in the middle do

Roy Lambert

Roy
>
> So a long winded way of saying that the last record in Calls and the last one in Project may or may not be the same eg
if there's no alarm set for that call it won't be in Project.

Am I right in thinking you can set/unset an alarm for a call after it has been created?  If this is the case then just
making and project is indexed, if needed, I guess your solution is about as good as you will get.

John

John

>Am I right in thinking you can set/unset an alarm for a call after it has been created?

As often as you like, and change the date / time of the alarm, and change the display from just today's to future to all of them

>If this is the case then just
>making and project is indexed, if needed, I guess your solution is about as good as you will get.

Well I never!  Either you've got to go down in my estimation or I'm getting better at this rubbish

Roy Lambert

Roy

>
> Well I never!  Either you've got to go down in my estimation or I'm getting better at this rubbish
>

I'm sure they are both right but...  if the record counts are the same and the record ids are different a simple
union should show this up and might be faster.

procedure TProjectForm.DoFakeFormActivate(var Message: TMessage);
var
CntrChk: string;
CallsToShow: integer;
begin
FormActivate(Self);
if DoneActivate and (tagMode = tmAlarms) then begin
 CntrChk := 'SELECT _CallID FROM Calls';
 CntrChk := CntrChk + ' WHERE ';
 CntrChk := CntrChk + Project.Filter;
 CallsToShow := dm.DB.Execute(CntrChk);
 if CallsToShow <> Project.RecordCount then PostMessage(Self.Handle, DoRefreshRequest, 0, 0) else begin
  CntrChk := CntrChk + ' UNION ';
  CntrChk := CntrChk + ' SELECT _CallID FROM Memory.' + Project.TableName+ WHERE ' + Project.Filter + ')';
  CallsToShow := dm.DB.Execute(CntrChk);
  if CallsToShow <> Project.RecordCount then PostMessage(Self.Handle, DoRefreshRequest, 0, 0)
 end;
end;
end;

Even if the original is quicker doesn't it need the second part of the union to have the project.filter added to the
where clause?

John

John

>I'm sure they are both right but... if the record counts are the same and the record ids are different a simple
>union should show this up and might be faster.

My reading of the manual

UNION Outputs the rows of both query expressions into the result set.

made me think I'd get repeats (eg 1,7,12,1,7,12). Or is it uniques only?

>Even if the original is quicker doesn't it need the second part of the union to have the project.filter added to the
>where clause?

Yes and it now does

This is the current procedure which actually works and is pretty fast even over the LAN. I'm always willing to speed things up though.

procedure TProjectForm.DoFakeFormActivate(var Message: TMessage);
var
CntrChk: string;
begin
FormActivate(Self);
if DoneActivate and (tagMode = tmAlarms) then begin
 CntrChk := 'SELECT _CallID FROM Calls';
 CntrChk := CntrChk + ' WHERE ';
 CntrChk := CntrChk + Project.Filter;
 if dm.DB.Execute(CntrChk) <> Project.RecordCount then PostMessage(Self.Handle, DoRefreshRequest, 0, 0) else begin
  if AlarmCompare.SQL.Text = '' then begin
   AlarmCompare.Close;
   CntrChk := CntrChk + ' AND NOT _CallID IN (SELECT _CallID FROM Memory.' + Project.TableName + ' WHERE ' + Project.Filter + ')';
   CntrChk := CntrChk + ' UNION ';
   CntrChk := CntrChk + ' SELECT _CallID FROM Memory.' + Project.TableName;
   CntrChk := CntrChk + ' WHERE ' + Project.Filter + ' AND NOT _CallID IN (SELECT _CallID FROM Calls WHERE ' + Project.Filter + ')';
   AlarmCompare.SQL.Text := CntrChk;
   AlarmCompare.Prepare;
   AlarmCompare.ExecSQL;
  end else begin
   if AlarmCompare.Prepared then AlarmCompare.Refresh else begin
    AlarmCompare.Prepare;
    AlarmCompare.ExecSQL;
   end;
  end;
  if AlarmCompare.RecordCount > 0 then begin
   AlarmCompare.Close;
   AlarmCompare.UnPrepare;
   PostMessage(Self.Handle, DoRefreshRequest, 0, 0);
  end;
 end;
end;
end;

Roy
>
> made me think I'd get repeats (eg 1,7,12,1,7,12). Or is it uniques only?
>

Uniques only  UNION ALL for repeats

John