Filter Building

[Ehab Samir Aziz]

I am using set filter to too much and with huge DataBases it slow the machine . It there a better method than set filter ?

[Enrico Maria Giordano]

Try scopes (OrdScope() function).

EMG

[Ehab Samir Aziz]

in class Txbrowse The filter scope does not work . What is the wrong with those statements :


select 1
USE METE
go top
set filter to 1->me_serial="9855564"

[R.F.]

If you are used to Filters the best way is to use the ADS LOCAL server. The SET FILTER under ADS is hundred of times faster than the standard (x)Harbour filter

[RAMESHBABU]

Hi RF

In continuation to the subject topic, Please clarify :

1) If I use ADS LOCAL server, can I use the traditional SET FILTER TO
command or the ADS filter function ?

2) And if I use SET FILTER TO, will ADS understand it and filter the
records using its own functioinality ?

Please recommend the suitable function in ADS.

Regards

- Ramesh Babu P

[R.F.]

ADS Local (free) and remote servers have the AOF Feature (Advantage Optimized Filters).

AOF are based in the traditional DBSETFILTER() function, you just have to follow certain rules when building the filter.

1) You have to load RDDADS and include the ADS DLLS
2) You have to write the filter expression in TEXTUAL form
3) You have to use DBSETFILTER() function instead of SET FILTER command.

How it works:

ADS has a build-in feature that processes the defined filters as they were SQL Queries, this means that the ADS server, not the app, resolves the filter and send you back a tiny ammount of data instead of the filtered database as Harbour does.

You only have to give the filter expresion as a TEXT, for example:

Incorrect:

Code: Select all  Expand view  RUN

x:= 25
SET FILTER TO AGE >= x
DBGOTOP()

The previos example is incorrect because "x" is a value that cannot be resolved by the ADS server, since the ADS server doesn´t know how much is "x" and cannot go inside your app memory area an "look" for the X value.

However, if you do the text instead of the value:

Code: Select all  Expand view  RUN

x:= 25
cFilter := "AGE >="+STR(x)
bFilter := "{||"+cFilter+¨}"
DBSETFILTER(&bFilter,cFilter)
DBGOTOP()

Then you will have an AOF which is thousands of times faster than the standar filter, no matter how big you database is.

I have tried this over 1000000 records and is damn fast !!!!!

[Ehab Samir Aziz]

I tried this but with error :

select 4
USE METE
x:= '9855564'
cFilter := "(4->me_serial) == "+x
bFilter := "{||"+cFilter+"}"
DBSETFILTER(&bFilter,cFilter)
DBGOTOP()

[R.F.]

Don't use "alias" in the filter expresion (4->)

[RAMESHBABU]

Mr.Ehab Samir Aziz

Don't use alias within brackets

cFilter := "(4->me_serial == )"+x

As RF said in this topic above, the alias can't be resolved by ADS
Server, since ADS can't look into our application memory area.

Hence use the expression as under. My expressions with alias as under
are working fine in filters.

cFilter := "4->me_serial == "+x


Mr.RF

Thank you very much for your guidance in using DbSetFilter() in combination with ADS.

If you are used to Filters the best way is to use the ADS LOCAL server. The SET FILTER under ADS is hundred of times faster than the standard (x)Harbour filter

When I tested it over 1200000 records, I found it faster not more than 7 times only, but not 100 times!.

Regards,

- Ramesh Babu P

[Ehab Samir Aziz]

Code: Select all  Expand view  RUN

select 4
USE METE
x:= "9855564"
cFilter := "4->me_serial = "+x
bFilter := "{||"+cFilter+"}"
DBSETFILTER(&bFilter,cFilter)
DBGOTOP()

This was my code which got argument error (=)

At the beginning That code

Code: Select all  Expand view  RUN

static oWnd
static coname
static custDlg
static oFWnd,oFld1

extern AdsKeyCount, AdsGetRelKeyPos, AdsSetRelKeyPos

#command SET SERVER LOCAL   => AdsSetServerType ( 1 )
#command SET SERVER REMOTE  => AdsSetServerType ( 2 )

#command SET FILETYPE TO <x:NTX,CDX,ADT>                              ;
      => AdsSetFileType( if( upper( <(x)> ) == "NTX", 1,              ;
                         if( upper( <(x)> ) == "CDX", 2, 3 ) ) )

STATIC cState := ""

//----------------------------------------------------------------------------//

function Main()
local oBrush, oBar, oBmp


   /*REQUEST DBFCDX
   rddsetdefault( "DBFCDX" )*/

   REQUEST ADS

   rddRegister( "ADS", 1 )
   rddsetdefault( "ADS" )

   SET SERVER LOCAL
   SET FILETYPE TO CDX

select 1
USE cust

INDEX ON upper(cust->cu_acct) TO cust
INDEX ON upper(cust->cu_name) TO cust1
INDEX ON upper(cust->cu_coun) TO cust2
INDEX ON upper(cust->cu_city) TO cust3
INDEX ON upper(cust->cu_gove) TO cust4
INDEX ON upper(cust->cu_stre) TO cust5
INDEX ON upper(cust->cu_phon) TO cust6
INDEX ON upper(cust->cu_fax ) TO cust7
INDEX ON upper(cust->cu_mobi) TO cust8
INDEX ON upper(cust->cu_man ) TO cust9
INDEX ON upper(cust->cu_appr) TO cust10
SET INDEX TO cust

select 2
USE cont
INDEX ON cont->CT_CU_ACCT+dtoc(cont->ct_strd) TO cont
INDEX ON cont->CT_CU_name TO cont1
INDEX ON dtoc(cont->ct_strd) TO cont2
INDEX ON dtoc(cont->ct_endd) TO cont3
INDEX ON cont->CT_machno TO cont4
INDEX ON cont->CT_mamc TO cont5
INDEX ON cont->ct_paymet TO cont6
INDEX ON cont->ct_payamt TO cont7
INDEX ON cont->ct_currenc TO cont8
INDEX ON cont->ct_excmet TO cont9
INDEX ON cont->ct_excamt TO cont10
INDEX ON cont->ct_minvol TO cont11
SET INDEX TO cont






select 3
use mach
index on mach->MC_CU_ACCT to mach
index on UPPER(mach->MC_ct_ctna) to mach1
index on UPPER(mach->MC_model) to mach2
index on UPPER(mach->MC_TYPE)+UPPER(mach->MC_model) to mach3
index on UPPER(mach->MC_serial) to mach4
index on UPPER(mach->MC_meres) to mach5
index on UPPER(mach->MC_brn) to mach6

select 4
select 4
use mete
index on mete->ME_serial to mete
index on str(mete->year) + str(mete->month) to mete1
index on mete->me_date to mete2

select 5
use coun

select 6
use city

select 7
use gove

SET 3DLOOK ON                         // Microsoft 3D Look

*   DEFINE BRUSH oBrush STYLE TILED       // FiveWin new predefined Brushes
*colors iniColor("windowText"),iniColor("appWorkSpace");
*colors iniColor("graytext"),iniColor("appWorkSpace");

   DEFINE WINDOW oWnd FROM 4, 4 TO 25, 75 ;
      colors CLR_BLACK,CLR_LIGHTGRAY;
      TITLE "Xerox Application for Outsourcing Works" ;
      MENU BuildMenu()
   DEFINE BITMAP oBmp RESOURCE "Background"

set century on
set date format to "DD/MM/YYYY"
   SET 3DLOOK ON                         // Microsoft 3D Look
//      ON RESIZE oBmp:Center()


   SET MESSAGE OF oWnd TO FWVERSION + ", " + FWCOPYRIGHT CENTERED TIME DATE

   oWnd:bPainted = { | hDC | BmpTiled( hDC, oWnd, oBmp ) }

   ACTIVATE WINDOW oWnd MAXIMIZED


return nil


[/quote]

[R.F.]

Don't use ALIAS for creating indexes, ADS cannot recognize them

[Ehab Samir Aziz]

Code: Select all  Expand view  RUN

select 4
USE METE
x:= "9855564"
cFilter := "me_serial = "+x
bFilter := "{||"+cFilter+"}"
DBSETFILTER(&bFilter,cFilter)
DBGOTOP()


That is also does not work Argument error (=)

[RAMESHBABU]

Hi Ehab

This is the working sample for your prblem.

******

#include "FiveWin.ch"
#include "ads.ch"

FUNCTION main()

REQUEST _ADS
rddRegister( "ADS", 1 )
rddsetdefault( "ADS" )
AdsSetDeleted(.T.)
SET SERVER LOCAL
SET FILETYPE TO CDX


select 4
USE METE

x := "'9855564'"

cFilter := "me_serial = "+ x

bFilter = "{||"+cFilter+"}"

DBSETFILTER(&bFilter,cFilter)
DBGOTOP()

BROWSE()

RETURN nil

**********

Note the use of single quotes within Double quotes of x value assignment.
Else the value of `x' is converted as as a number in macro evaluation i.e.
&bFilter and generates a run time error, bacuase of two different data types comparision in filter condition.


Regards,

- Ramesh Babu P

[Ehab Samir Aziz]

it is work fine with your example but with footer function it is not

Code: Select all  Expand view  RUN

STATIC FUNCTION Footers( oWnd )
*--------------------------
   local oChild, oBrw
   local nFor
   local bFilter := ""
   local cFilter := ""
   local x := ""

REQUEST ADS
rddRegister( "ADS", 1 )
rddsetdefault( "ADS" )
AdsSetDeleted(.T.)
SET SERVER LOCAL
SET FILETYPE TO CDX

select 4
USE METE

x := "'9855564'"

cFilter := "me_serial = "+ x

bFilter = "{||"+cFilter+"}"

DBSETFILTER(&bFilter,cFilter)
DBGOTOP()




   DEFINE WINDOW oChild FROM 4, 4 TO 25, 75 ;
   TITLE "Meter Works"

   oBrw := TXBrowse():New( oChild )

   oBrw:nMarqueeStyle       := MARQSTYLE_HIGHLCELL
   oBrw:nColDividerStyle    := LINESTYLE_BLACK
   oBrw:nRowDividerStyle    := LINESTYLE_BLACK
   oBrw:lColDividerComplete := .t.
   oBrw:nHeaderLines        := 1
   oBrw:nFooterLines        := 1
   oBrw:nDataLines          := 2
   oBrw:lFooter             := .t.
   oBrw:SetRDD()



   for nFor := 1 to Fcount()
   if nFor=20
      oBrw:aCols[ nFor ]:cHeader       := FieldName( nFor )
      oBrw:aCols[ nFor ]:cFooter       := alltrim(STR(TOTALEM2(),12,0))
      loop
   endif
      *oBrw:aCols[ nFor ]:cHeader       := "Field: " + ltrim( str( nFor ) ) + CRLF + FieldName( nFor )
      oBrw:aCols[ nFor ]:cHeader       := FieldName( nFor )
      oBrw:aCols[ nFor ]:cFooter       := FieldName( nFor )
      oBrw:aCols[ nFor ]:bRClickHeader := {|r,c,f,o| Msginfo("Right click on column header " + o:cHeader, "TXBrowse power") }
      oBrw:aCols[ nFor ]:bLClickFooter := {|r,c,f,o| Msginfo("Left click on column footer " + o:cHeader, "TXBrowse power") }
      oBrw:aCols[ nFor ]:bRClickFooter := {|r,c,f,o| Msginfo("Right click on column footer " + o:cHeader, "TXBrowse power") }
      oBrw:aCols[ nFor ]:blDClickData  := {|r,c,f,o| Msginfo("Left double click on column data " + o:cHeader, "TXBrowse power") }
      oBrw:aCols[ nFor ]:bRClickData   := {|r,c,f,o| Msginfo("Right click on column data " + o:cHeader, "TXBrowse power") }
   next




   oBrw:CreateFromCode()

   oChild:oClient := oBrw

   ACTIVATE WINDOW oChild MAXIMIZED ON INIT oBrw:SetFocus()

RETURN NIL