Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB General » View Thread |
Messages 1 to 5 of 5 total |
Too many calls for OnFilterRecord |
Tue, Jan 13 2009 10:21 AM | Permanent Link |
"Mauro Botta" | 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. |
Tue, Jan 13 2009 1:19 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Wed, Jan 14 2009 3:36 AM | Permanent Link |
"Mauro Botta" | > << 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. |
Sun, Jan 18 2009 2:23 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Tue, Jan 20 2009 4:38 AM | Permanent Link |
"Mauro Botta" | > 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 |
This web page was last updated on Tuesday, September 17, 2024 at 04:19 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |