Hi

i need to have a ( optimized !!! ) filter in a simple table (
code+Description , code is indexed )

the problem is the lenght of .FILTER property of EDBTable.

i need this :
TEDBTable.Filter  := 'CODE = 1 or CODE = 5 or CODE = 19 or CODE = 1111 or
CODE = 1213 or ... )

with 100 - 200 - 500 filter.


Are there any limit of Filter string ?

if i use 200 filter , are are ALL optimized , always ?

p.s.
i don't want user ONFILTERRECORD for performance problem ( slow.. )

-------------------------------------------------------------------
Edb 2.03 last.. No Unicode  - Delphi 2010
No Client Server

Ciao Mauro,

Change your code to Filter := 'Code in (1, 5, 19, 1111)'.
This will save a lot of characters.

BTW why do you not use a Query? Maybe you can find the ID´s by using a sub query.
Like that:

SELECT * FROM "TableWithCodeField" WHERE Code in (SELECT Code FROM TableX WHERE FieldY='Code to search for').

Hope this can help
Michael

Mauro


The filter in ElevateDB, like DBISAM before it, are strings so the limit is the limit on a string in Delphi.  As Michael suggests I'd use IN simply to make the string shorter, there should be no difference in performance. Here's the code I use in one case

 sl := TStringList.Create;
 sl.Capacity := sqgCompanies.RecordCount;
 while not sqgCompanies.Eof do begin
  sl.Add(sqgCompanies.FieldByName('_ID').AsString);
  sqgCompanies.Next;
 end;
 Companies.Filter := '_ID IN (' + sl.CommaText + ')';
 Companies.Filtered := True;

In this case a query involving JOINs is used to generate the list and by then setting a filter I retain an editable table.

Under the hood filters and sql queries are the same (I think) its why you can use pretty much identical syntax with the exception of JOINs. One consequence of this is that even if you want to use a table and filter (I do a lot) you can still test for optimisation by writing and testing the equivalent query in EDBManager.

In the specific example you quote then as long as there is an index on CODE the filter is optimised. I don't know how much difference it will make but and old habit of mine from the days of using interpreted code is that where practicable I try and sequence the ORs or the order of items in an IN to put the most frequently occurring first. I doubt that it will make much if any difference unless you have a mass of tests.

Roy Lambert [Team Elevate]