Hi ^^

I have found a  strange behavior in EDBTable OnFilterRecord procedure.

i have a db with 5 record , 1 field.

VALUE :
A1
A2
A3
A4
A5

i have this code :

procedure TForm1.EDBTable1FilterRecord(DataSet: TDataSet;  var Accept:
Boolean);
begin
Caption := IntToStr(StrToInt(caption) +1);
Memo1.lines.Add('OFR :  '+caption+'
'+DataSet.FieldByName('FIELD_DESC').AsString);

if Pos(K_FILTER_STRING,DataSet.FieldByName('FIELD_DESC').AsString) <> 0 then
  Accept := True
else
  Accept := False;
end;

if i filter the table for a char with Zero record ( for example : " X " )
the OnFilterRecord is called 5 times , is all correct.

but if i do a filter with 1 record of good result ( 1 or more record of
result  )  there are any problems.

the OnFiltelRecord function is called 3 times ( 5 record =  OFR is called 15
times )

10'000 records -> 30'000 times  !


TIM...
Optimize this problem is important in Network enviroment , for make EDB
always faster


result of my example  ( table.refresh )

K_FILTER_STRING = 'X'
( zero record of filter result )

OFR :  1  A5
OFR :  2  A4
OFR :  3  A3
OFR :  4  A2
OFR :  5  A1
( only 5 times , all ok )

----

K_FILTER_STRING = '3'
( 1 record of filter result )

OFR :  1  A3
OFR :  2  A3
OFR :  3  A3
OFR :  4  A4
OFR :  5  A5
OFR :  6  A5
OFR :  7  A4
OFR :  8  A3
OFR :  9  A3
OFR :  10  A2
OFR :  11  A1
OFR :  12  A1
OFR :  13  A2
OFR :  14  A3
( OFR is called too much times )

----
K_FILTER_STRING = 'A'
( 5 record of filter result )


OFR :  1  A3
OFR :  2  A3
OFR :  3  A3
OFR :  4  A2
OFR :  5  A2
OFR :  6  A1
OFR :  7  A1
OFR :  8  A3
OFR :  9  A4
OFR :  10  A4
OFR :  11  A5
OFR :  12  A5
OFR :  13  A5
OFR :  14  A1
OFR :  15  A1
( OFR is called too much times )


EDB 2.02 build 7 + Delphi 2007

Best Regards

Mauro.

Mauro,

<< I have found a  strange behavior in EDBTable OnFilterRecord procedure. >>

See my other reply regarding performance.  EDB handles OnFilterRecord in a
completely different manner from DBISAM, so you cannot expect the two to
behave the same way at all.

EDB starts from the current position, and proceeds to scan rows (in the
active index order) until it reaches a row that satisfies the current
OnFilterRecord event handler.  This can mean that many, many rows are
scanned before a satisfactory row is reached.  Furthermore, the process must
repeat any time the dataset is refreshed, or during any navigation of the
dataset at all.

Is there any particular reason that you don't just use a normal filter or
query instead ?  We normally only recommend that you use OnFilterRecord
against small datasets, or datasets that have already been reduced in size
through a range or expression filter.

--
Tim Young
Elevate Software
www.elevatesoft.com

> << I have found a  strange behavior in EDBTable OnFilterRecord procedure.
>  >>
>
> See my other reply regarding performance.  EDB handles OnFilterRecord in a
> completely different manner from DBISAM, so you cannot expect the two to
> behave the same way at all.
>
> EDB starts from the current position, and proceeds to scan rows (in the
> active index order) until it reaches a row that satisfies the current
> OnFilterRecord event handler.  This can mean that many, many rows are
> scanned before a satisfactory row is reached.  Furthermore, the process
> must repeat any time the dataset is refreshed, or during any navigation of
> the dataset at all.

yes.. ok....

but my question was :

Why EDB read 3 times all my table when i make a filter ?
( Indeed filters with EDB in network are slow )

This problem creates a lot of traffic on the network performance and worst

i have try with Mysql component and NexusDB, today , ( dbisam 4 yesterday )
and of course no one goes to the table 3 times when active a filter.


> Is there any particular reason that you don't just use a normal filter or
> query instead ?  We normally only recommend that you use OnFilterRecord
> against small datasets, or datasets that have already been reduced in size
> through a range or expression filter.

Because with OnFilterRecord i can make a very complicate filter in easy
mode.

I have 10000000 table in my application and 10 query.
For me is more easy change db engine that convert all my tables in Query.

Mauro,

<< Why EDB read 3 times all my table when i make a filter ? >>

It's not reading the table 3 times.  It's navigating the table, and having
to call OnFilterRecord to make sure that the row that it positions on is
still valid.

Here's the sequence that you gave with the description of what is happening
when opening a table with the following filter condition in the
OnFilterRecord (no data-aware controls attached):

K_FILTER_STRING = '3'
( 1 record of filter result )

OFR: A1 <<<<<<< First row operation
OFR: A2 <<<<<<< Looking for a valid row
OFR: A3 <<<<<<< Found a row
OFR: A3 <<<<<<< GetCurrentRow operation
OFR: A4 <<<<<<< Next operation
OFR: A5 <<<<<<< Looking for a valid row
OFR: A5 <<<<<<< Didn't find any rows and hit end of dataset, need to go back
OFR: A4 <<<<<<< Looking for a valid row
OFR: A3 <<<<<<< Found the row
OFR: A3 <<<<<<< SetToBookmark operation

The First, GetCurrentRow, Next, and SetToBookmark are all automatic
operations of the TDataSet architecture in the db.pas unit, and are not
controlled by ElevateDB.

<< i have try with Mysql component and NexusDB, today , ( dbisam 4
yesterday ) and of course no one goes to the table 3 times when active a
filter. >>

How many of them are executing the filter in the same fashion as ElevateDB ?
My guess would be 0 (DBISAM most certainly is not), which means that your
comparison is irrelevant.  Look, we got beat up by DBISAM customers non-stop
because we didn't execute the OnFilterRecord on an incremental basis and in
the active index order.  Well, we changed it to do so, and now you're not
happy.  We're not going to win this battle, no matter how we design it.  I'm
not in any position to change the entire way that ElevateDB handles
OnFilterRecord at this point, nor would I necessarily want to, lest I simply
switch from making you angry to making a bunch of other people angry.

If you want to get the best performance, then you need to use an expression
filter instead of an OnFilterRecord event handler.  Using a filter like
this:

POSITION('3' IN FIELD_DESC)

will give you a single-pass of the table to evaluate the filter.

--
Tim Young
Elevate Software
www.elevatesoft.com

> The First, GetCurrentRow, Next, and SetToBookmark are all automatic
> operations of the TDataSet architecture in the db.pas unit, and are not
> controlled by ElevateDB.
>
> << i have try with Mysql component and NexusDB, today , ( dbisam 4
> yesterday ) and of course no one goes to the table 3 times when active a
> filter. >>
>
> How many of them are executing the filter in the same fashion as ElevateDB
> ? My guess would be 0 (DBISAM most certainly is not), which means that
> your comparison is irrelevant.  Look, we got beat up by DBISAM customers
> non-stop because we didn't execute the OnFilterRecord on an incremental
> basis and in the active index order.  Well, we changed it to do so, and
> now you're not happy.  We're not going to win this battle, no matter how
> we design it.  I'm not in any position to change the entire way that
> ElevateDB handles OnFilterRecord at this point, nor would I necessarily
> want to, lest I simply switch from making you angry to making a bunch of
> other people angry.
>
> If you want to get the best performance, then you need to use an
> expression filter instead of an OnFilterRecord event handler.  Using a
> filter like this:
>
> POSITION('3' IN FIELD_DESC)
>
> will give you a single-pass of the table to evaluate the filter.

I have try with POSITION function  for the filter

The benchmark for the filter is very good in local , only one user. now.

with only OnFilterRecord : 12 sec  
with FILTER + POSITION : 2.5 sec   

medium in network with more users

with OnFilterRecord : 110 sec
with FILTER + POSITION : 95 sec

p.s.
 Using POSITION and OnFilterRecord together
 the OFR is called the correct number of times.


I will be a little difficult to convert all OFR into FILTER
but I am still happy.


Tnx TIM