I'm trying to develop a custom function to use in filtering a table depending on the contents of another table. What I've written works but its slow. I want to do it as a filter rather than sql (which is a lot faster) because it allows me to provide "live and interactive joins" sort of.

I'm hoping someone will be able to suggest a faster approach.

TIM (shouting just to get your attention how would something like this work in ElevateDB

Companies is the table I'm filtering- c14k records

companies.Filter := '_fkOrgType = ''HQ'' AND Test1(_ID,' + QuotedStr('redundancy') + ') OR Test1(_ID,''unsubscribe'')';
companies.Filtered := TRue;

MandN is  the related table with c23k records, and the custom filter is:

if (CompareText(FunctionName, 'Test1') = 0) then begin
 if not tq1.Prepared then tq1.Prepare;
 tq1.Close;
 tq1.ParamByName('CpyNo').AsInteger := FunctionParams[0].AsInteger;
 tq1.ParamByName('Words').AsString := FunctionParams[1].AsString;
 tq1.ExecSQL;
 Result := tq1.RecordCount > 0;
end;


with the sql being

SELECT _MsgNo FROM MandN WHERE
_fkCompanies = :CpyNo
AND
TEXTSEARCH(:Words IN _Message)
TOP 1


In MandN I have an compound index on _fkCompanies - _fkCompanies;_TimeStamp and _Message is in the full text index.

I've tried it with and without RequestLive for the query and virtually no difference

Roy Lambert

Roy,

<< I'm trying to develop a custom function to use in filtering a table
depending on the contents of another table. What I've written works but its
slow. I want to do it as a filter rather than sql (which is a lot faster)
because it allows me to provide "live and interactive joins" sort of.
I'm hoping someone will be able to suggest a faster approach. >>

Custom functions are going to be slow not matter what when used as filters
because they will be evaluated once for every single record in the table.
So, in this case, DBISAM is executing the query 14,000 times every time the
filter is evaluated.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim

>Custom functions are going to be slow not matter what when used as filters
>because they will be evaluated once for every single record in the table.
>So, in this case, DBISAM is executing the query 14,000 times every time the
>filter is evaluated.

I appreciate that (well not really appreciate it but you know what I mean). I was just hoping someone would be able to point out how to improve my code to make it faster Using a mixture of range and filter I've managed to speed it up by a factor of 2 (from 45 seconds) which is acceptable, but I wouldn't object to it being sub 5 seconds!

I assume I'm be able to use the same approach with ElevateDB - all I know so far is we're going to have something called PSM, PMT or somesuch

BTW if you think this one is slow you want to see what the next one is - its going to check linked files (mainly .docs) for strings. Its paint drying time!!!!!


Roy Lambert

Roy,

<< I appreciate that (well not really appreciate it but you know what I
mean). I was just hoping someone would be able to point out how to improve
my code to make it faster Using a mixture of range and filter I've
managed to speed it up by a factor of 2 (from 45 seconds) which is
acceptable, but I wouldn't object to it being sub 5 seconds! >>

Which sounds about right - the table open/closes are probably what is eating
up all of the time.

<< I assume I'm be able to use the same approach with ElevateDB - all I know
so far is we're going to have something called PSM, PMT or somesuch >>

Yes, you'll be able to code the function in SQL if you want.

--
Tim Young
Elevate Software
www.elevatesoft.com

Having spent ages playing I've now decided to create filters in the following way:


1. "native fields" - just added to the filter
2. "joined" fields - create sql to select a list of the primary keys for the target table into a memory table, index it (on the only field) and have a custom function which does a findkey on the memory table to see if the record should be included. The sql would be run before setting the filter to active.

The final filter would look something like

_JobTitle = 'MD' AND CheckForeignFields(_ID)

with the custom function like

procedure TForm1.CustomFilters(Sender: TObject; const FunctionName: string; FunctionParams: TDBISAMParams; var Result: Variant);
begin
if (CompareText(FunctionName, 'CheckForeignFields') = 0) then begin
 Result := ChkIDs.FindKey([FunctionParams[0].AsInteger]);
end;
end

and the sql

DROP TABLE IF EXISTS Memory\ChkIds;

CREATE TABLE IF NOT EXISTS Memory\ChkIds
(_ID INTEGER,
PRIMARY KEY (_ID));

INSERT INTO Memory\ChkIds (_ID)
SELECT DISTINCT _ID FROM Contacts
JOIN Career ON Career._fkContacts = _ID
JOIN Companies ON Companies._ID = Career._fkCompanies
WHERE
Companies._fkOrgType = 'ELECTRONIC'
OR
Companies._fkOrgType = 'APPLEGATE';

a) Can anyone see a problem with this
b) Since I only want a list of the IDs and I don't care where in the list it is only that it is in the list is there a faster way/better way of doing this?


Roy Lambert

Roy,

<< a) Can anyone see a problem with this
b) Since I only want a list of the IDs and I don't care where in the list
it is only that it is in the list is there a faster way/better way of doing
this? >>

The only problem I see with it is that you're recreating what the SQL joins
do.  But, other than that, it looks fine.  BTW, did you see that 4.22 B5
included a fix for that BLOB handle / unlock error that you were getting ?

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim


>The only problem I see with it is that you're recreating what the SQL joins
>do.

Yup I know so effectively I'm doubling the filtering time. The big but (not that is an English but and nothing to do with my anatomy) is that I can give a "semi-live" filter linking with multiple tables and for this one table that's quite important.

>But, other than that, it looks fine. BTW, did you see that 4.22 B5
>included a fix for that BLOB handle / unlock error that you were getting ?

Didn't read the fix list this time so I'll just say THANKS.

Roy Lambert