Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB General » View Thread |
Messages 1 to 3 of 3 total |
What may be long TEDBTable.Filter ? |
Thu, May 20 2010 7:12 AM | Permanent Link |
Mauro Botta | 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 |
Thu, May 20 2010 7:48 AM | Permanent Link |
gripsware gripsware datentechnik gmbh | 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 |
Thu, May 20 2010 8:29 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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] |
This web page was last updated on Sunday, May 19, 2024 at 08:46 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |