Can anyone suggest ways to speed this up?


procedure TContactsForm.DoFindTimer(Sender: TObject);
begin
DoFind.Enabled := False;
if Finder.Text <> '' then begin
 if Finder.Text[1] in ['0'..'9'] then begin
  Contacts.Locate('_ID', StrToInt(Finder.Text), []);
 end else begin
  if (0 = Pos(',', Finder.Text)) or (Finder.Text[Length(Finder.Text)] = ',') then begin
   Contacts.Locate('_Surname', Finder.Text, [loCaseInsensitive, loPartialKey]);
  end else begin
   if not Contacts.Locate('_Surname;_Forename', VarArrayOf([SubFld(Finder.Text, ',', 1), SubFld(Finder.Text, ',', 2)]), [loCaseInsensitive, loPartialKey]) then begin
    ComplexFind.Close;
    complexfind.RequestPlan := true;
    if not ComplexFind.Prepared then ComplexFind.Prepare;
    ComplexFind.ParamByName('Surname').AsString := SubFld(Finder.Text, ',', 1) + '%';
    ComplexFind.ParamByName('Forename').AsString := SubFld(Finder.Text, ',', 2) + '%';
    ComplexFind.ExecSQL;
    if ComplexFind.RecordCount > 0 then begin
     ComplexFind.First;
     Contacts.Locate('_ID', ComplexFind.Fields[0].AsInteger, []);
    end;
   end;
   ComplexFind.Close;
  end;
 end;
end;
end;

Contacts primary key is an autoinc, there is a case insensitive compound index on _Surname and _Forename. Its called in a timer so generally only fires when the user stops typing.


Roy Lambert

Is ComplexFind's SQL something like "SELECT * FROM CONTACTS WHERE
_SURNAME LIKE :surname AND _FORENAME LIKE :forename"?  If so, is it
finding indexes for both fields or is it doing a brute-force search?


In the lines

>    if (0 = Pos(',', Finder.Text)) or
(Finder.Text[Length(Finder.Text)] = ',') then begin
>     Contacts.Locate('_Surname', Finder.Text, [loCaseInsensitive,
loPartialKey]);

If the user pauses after typing a comma, doesn't the routine search for
e.g. "SMITH," when you want it to search for "SMITH" (without the comma)?

Charles


>Is ComplexFind's SQL something like "SELECT * FROM CONTACTS WHERE
>_SURNAME LIKE :surname AND _FORENAME LIKE :forename"? If so, is it
>finding indexes for both fields or is it doing a brute-force search?

It does an index scan on _Surname and a rowscan on the result for _Forename. I tried adding a separate index for _Forname and the results were exactly the same.

>In the lines
>
> > if (0 = Pos(',', Finder.Text)) or
>(Finder.Text[Length(Finder.Text)] = ',') then begin
> > Contacts.Locate('_Surname', Finder.Text, [loCaseInsensitive,
>loPartialKey]);
>
>If the user pauses after typing a comma, doesn't the routine search for
>e.g. "SMITH," when you want it to search for "SMITH" (without the comma)?

No, its not as effective as a FindKey but it will be caught by

   if not Contacts.Locate('_Surname;_Forename', VarArrayOf([SubFld(Finder.Text, ',', 1), SubFld(Finder.Text, ',', 2)]), [loCaseInsensitive, loPartialKey]) then begin

ie a locate on smith and null

Roy Lambert

Roy,

<< Can anyone suggest ways to speed this up? >>

Without running this against the actual data that you're using, it is next
to impossible to figure out where the most time is being spent.  If you want
to email me the catalog/table(s) and the code, I'll take a look.

--
Tim Young
Elevate Software
www.elevatesoft.com

Roy Lambert wrote:
> Charles
>
>
>> Is ComplexFind's SQL something like "SELECT * FROM CONTACTS WHERE
>> _SURNAME LIKE :surname AND _FORENAME LIKE :forename"? If so, is it
>> finding indexes for both fields or is it doing a brute-force search?
>
> It does an index scan on _Surname and a rowscan on the result for _Forename. I tried adding a separate index for _Forname and the results were exactly the same.

I'm surprised that a forename index doesn't help, though I guess it
makes sense if the surname search returns a fairly small set of rows.

>
>> In the lines
>>
>>> if (0 = Pos(',', Finder.Text)) or
>> (Finder.Text[Length(Finder.Text)] = ',') then begin
>>> Contacts.Locate('_Surname', Finder.Text, [loCaseInsensitive,
>> loPartialKey]);
>>
>> If the user pauses after typing a comma, doesn't the routine search for
>> e.g. "SMITH," when you want it to search for "SMITH" (without the comma)?
>
> No, its not as effective as a FindKey but it will be caught by
>
>     if not Contacts.Locate('_Surname;_Forename', VarArrayOf([SubFld(Finder.Text, ',', 1), SubFld(Finder.Text, ',', 2)]), [loCaseInsensitive, loPartialKey]) then begin

That other Locate is on the far side of the else clause, so it'll never
be called when the last character is a comma.

BTW, the last call to ComplexFind.Close is misplaced--it is called even
when ComplexFind hasn't been opened.

How does loPartialKey behave when a compound index is used?  Does it
mean that the surname must be an exact match, but the forename can be
incomplete?  Or can both names be incomplete?  For instance, will the
last Locate find "Smith, John" when Finder.Text = "SM, J"?  I guess my
suggestion is that if the last Locate frequently fails, why not ditch it
and go immediately to ComplexFind.

Just wondering--is there any advantage/disadvantage to calling ExecSQL
over Open when you know the SQL statement is a SELECT?  And does
TDataSet.Open imply TDataSet.First?  (The docs don't say so, so you're
right to explicitly call First, but I've always wondered if we're
wasting a nanosecond here).

Charles

>I'm surprised that a forename index doesn't help, though I guess it
>makes sense if the surname search returns a fairly small set of rows.

Exactly that. In fact the execution plan was identical even with the extra index - index scan for _Surname and row scan for _Forename

>That other Locate is on the far side of the else clause, so it'll never
>be called when the last character is a comma.

Yes you're right - I was being blind. I've moved that to the top of the procedure so it doesn't even try if a ',' is the last character entered. There's no point looking because there must be more coming and its has made it feel snappier - thanks.

>BTW, the last call to ComplexFind.Close is misplaced--it is called even
>when ComplexFind hasn't been opened.

moved

>How does loPartialKey behave when a compound index is used? Does it
>mean that the surname must be an exact match, but the forename can be
>incomplete? Or can both names be incomplete? For instance, will the
>last Locate find "Smith, John" when Finder.Text = "SM, J"? I guess my
>suggestion is that if the last Locate frequently fails, why not ditch it
>and go immediately to ComplexFind.

First I think you're right about the way it works. Second it was added in as an experiment, on the basis that generally you know the surname of the person and use the forename to select out of multiple hits and humans being what they are they'll generally type the full surname. So it slows things down when a partial surname is entered but seems to speed things up when a full surname is entered. Gut feel only, but that's the way users look at it.

>Just wondering--is there any advantage/disadvantage to calling ExecSQL
>over Open when you know the SQL statement is a SELECT?

According to Tim there's no difference and I prefer ExecSQL - just one of those things

>And does
>TDataSet.Open imply TDataSet.First? (The docs don't say so, so you're
>right to explicitly call First, but I've always wondered if we're
>wasting a nanosecond here).

I believe so, its just habit and since its already there as you say probably a nanosecond gone forever.

Roy Lambert

Tim


I think Charles spotted the mistake. It was my code that was the problem, not ElevateDB and it feels a lot snappier now that I've moved one test.

Roy Lambert

Roy,

<< I think Charles spotted the mistake. It was my code that was the problem,
not ElevateDB and it feels a lot snappier now that I've moved one test. >>

Cool, I'm glad that it's fixed now.

--
Tim Young
Elevate Software
www.elevatesoft.com