xbrowse slow

[artu01]

Mr. Rao
please help me, my xbrowse is slow on network.
it contains only 12,000 registers, I note that is slow when I use :
oBrw:lIncrFilter := .t.
oBrw:lSeekWild := .t.
oBrw:cFilterFld := "RUC"

If I omitt these sentences, the movement into xbrowse is normal, but I want that xbrowse has search incremental
I use CDX and DBf's
I open all tables at the beginning of the program

Code: Select all  Expand view

 REDEFINE XBROWSE oBrw                      ;
      ALIAS "MCODI"                           ;
      COLUMNS "Codigo","Ng","RUC","Te","Di"   ;
      FIELDSIZES 50, 195,75,120, 100          ;
      HEADERS "CODIGO"                        ;
             ,"RAZÓN SOCIAL"                  ;
             ,"NO. IDENT."                    ;
             ,"TELÉFONOS"                     ;
             ,"DIRECCIÓN"                     ;
      ID 4001 OF oDlg                         ;
      AUTOSORT                                ;
      LINES CELL //;
      oBrw:bKeyDown   := { |nKey| ProcMtn(nKey, nTipo) }
      oBrw:bLDblClick := { || ProcMtn( 13, nTipo )     }
      oBrw:nMarqueeStyle    = MARQSTYLE_HIGHLROW
      oBrw:lIncrFilter := .t.
            oBrw:lSeekWild := .t.
            oBrw:cFilterFld := "RUC"       
    REDEFINE SAY oBrw:oSeek VAR oBrw:cSeek ID 111 OF oDlg COLOR RGB(0,0,0),RGB(204,204,102) //COLOR CLR_HRED,CLR_YELLOW
    ACTIVATE DIALOG oDlg CENTERED NOWAIT ON INIT (oDlg:Update(), oBrw:setfocus() )

 

[nageswaragunupudi]

1.Please check if you have an index tag on UPPER(RUC)
2.May I see the index keys for all tags?

[Marc Venken]

Sorry to jump in.

I don't think that my xbrowse is slow, but it seems in this post that how the index is build could have affect on speed.

Therefore here my index routine :

Code: Select all  Expand view

function Reindex()
  close master
  ferase("master.cdx")
  use master NEW
  select master
  INDEX ON upper(master->code) TAG code
  INDEX ON upper(master->lev_naam) TAG levnaam
  INDEX ON upper(master->naam) TAG naam
  INDEX ON upper(master->lev_ref) TAG levref
  INDEX ON upper(master->fab_ref) TAG fabref
  INDEX ON upper(master->fab_naam) TAG fabnaam
  INDEX ON master->selection TAG selected
  INDEX ON str(val(master->pagina)+1000) TAG pagina
  INDEX ON master->cat_main+master->Cat_sub1 TAG cat
  INDEX ON master->Cat_sub1+master->fab_naam TAG catsub

  msginfo("Reindex compleet")

return NIL
 

Is this code ok for Xbrowse performance ?

[Rick Lipkin]

Marc

Don't know if this will help, but I remember reading that the database pointer is stored in the index .. try to remove Master-> and just reference your fieldname ..

As far as using Upper() .... I would go in to your table and replace all your primary keys with Replace all xxx with Upper( xxx ) .. try to keep your index keys free of pointers and function calls.

Just my 2 cents worth..

Rick Lipkin

[Marc Venken]

Rick,

As far as using Upper() .... I would go in to your table and replace all your primary keys with Replace all xxx with Upper( xxx ) .. try to keep your index keys free of pointers and function calls.

Doing that, will have not the best effect in using the data in the application.

Marc Venken would become MARC VENKEN and that is not ok for my use of the names.

Thanks anyway...

[nageswaragunupudi]

Sorry to jump in.

I don't think that my xbrowse is slow, but it seems in this post that how the index is build could have affect on speed.

Therefore here my index routine :

Code: Select all  Expand view

function Reindex()
  close master
  ferase("master.cdx")
  use master NEW
  select master
  INDEX ON upper(master->code) TAG code
  INDEX ON upper(master->lev_naam) TAG levnaam
  INDEX ON upper(master->naam) TAG naam
  INDEX ON upper(master->lev_ref) TAG levref
  INDEX ON upper(master->fab_ref) TAG fabref
  INDEX ON upper(master->fab_naam) TAG fabnaam
  INDEX ON master->selection TAG selected
  INDEX ON str(val(master->pagina)+1000) TAG pagina
  INDEX ON master->cat_main+master->Cat_sub1 TAG cat
  INDEX ON master->Cat_sub1+master->fab_naam TAG catsub

  msginfo("Reindex compleet")

return NIL
 

Is this code ok for Xbrowse performance ?

Very wrong practice to use table name as alias in the index expression.
After building your index try to open the table with a different alias like this:

Code: Select all  Expand view

USE MASTER NEW ALIAS MST SHARED VIA "DBFCDX"
SET ORDER TO TAG CODE
 

You will get a runtime error complaining that the alias MASTER does not exist.

This is the best way

Code: Select all  Expand view

function BuildIndex()

   field CODE, NAAM, ...........
   
   INDEX ON UPPER( CODE ) TAG CODE
   INDEX ON UPPER( NAAM ) TAG NAAM
   ...
   ...
 

You can also use FWH built in function
FW_CdxCreate()
This function creates index on any table for all fields. Character fields are indexed with UPPER function. In addition it also creates a tag
INDEX ON DELETED() TAG DELETED

In case you do not want index on all fields, but only on selected fields then you can use
FW_CdxCreate( "CODE,NAAM,..." )

This is important because SET FILTER TO !DELETED() speeds up access of DBF having many deleted records and provides reliable results for OrdKeyNo() and OrdKeyCount()

[Marc Venken]

Very wrong practice to use table name as alias in the index expression.
After building your index try to open the table with a different alias like this:

USE MASTER NEW ALIAS MST SHARED VIA "DBFCDX"
SET ORDER TO TAG CODE


You will get a runtime error complaining that the alias MASTER does not exist.

Yes. This is a important lesson i've learned now. I always did it wrong and yes I had that error.
Always tried a work around because i din't knew. Thanks
I have probably more wrong code will update this one.

[artu01]

Dear Mr. Rao
Now run perfect

I had the index with Upper (may an insignificant thing costs a lot on network? )


Thanks

[nageswaragunupudi]

I had the index with Upper (may an insignificant thing costs a lot on network? )

Please keep index on UPPER(fieldname) ONLY.
That is the only way to seek any value.

- Create indexes without alias names. Please follow my example
- Using FW_CdxCreate( [fieldslist] ) helps create good indexes
- Creating index on "DELETED()" and then "SET FILTER TO !DELETED()" improves performance on dbfs having many deleted records.
- On network, smaller index files improves performance.

[artu01]

Mr Rao:
Yesterday the xbrowse worked fast after I delete the function UPPER as you suggested, but
Today the xbrowse returned to work slow. The program show for moments the message: Application not responding

I don't understand, What is wrong?


this is my code for creating the index

Code: Select all  Expand view

       FERASE(cPathDBF+cSubCarp+"MCODI"+Exten)
       IF ( VALTYPE( AbreDbf("MCODI", .F., .F.,"MCODI" , cPathDBF+cSubCodi, , "Clientes/Proveedores") ) == "O")
         oS3:SetText("Clientes / Proveedores")
         IIF(lPack,__dbPack(),)

          INDEX ON codigo               TAG 1
          INDEX ON Ng                   TAG 2
          INDEX ON ruc+CODUBI    TAG 3

 

- Create indexes without alias names. Please follow my example
that is the way I create my indexes

- Using FW_CdxCreate( [fieldslist] ) helps create good indexes
Its not possible, I have fwh 13.01

- Creating index on "DELETED()" and then "SET FILTER TO !DELETED()" improves performance on dbfs having many deleted records.
I will try that

- On network, smaller index files improves performance.
Build indexes with few fields, that's what you mean?

[Marc Venken]

function BuildIndex()

field CODE, NAAM, ...........

INDEX ON UPPER( CODE ) TAG CODE
INDEX ON UPPER( NAAM ) TAG NAAM
...
...

I see that you use :

field CODE,NAAM, .....

before starting to index. Why is that ?

[nageswaragunupudi]

I delete the function UPPER

I never said not to use Upper. Actually we need Upper for character fields for proper searches. In my code above also I used Upper.

Are you not using DBFCDX?

[nageswaragunupudi]

function BuildIndex()

field CODE, NAAM, ...........

INDEX ON UPPER( CODE ) TAG CODE
INDEX ON UPPER( NAAM ) TAG NAAM
...
...

I see that you use :

field CODE,NAAM, .....

before starting to index. Why is that ?

field is also is a declaration just like local, static.

When we declare

field CITY,AGE
local nVar, cVar

Then wherever we use CITY, AGE the compiler understands that it is a field name and when we use nvar and cvar it understands that it is a local variable.

This declaration is helpful to use to create indexes, filter expressions without using alias->

[Marc Venken]

Changed my index function with the FW_CdxCreate(cList) function.

My browsing is clearly speeding up !! Thanks

For the FW_CdxCreate() function :

The Forum and Google, the manuals also give no data on this function :

There is a annoucment that the function is newly added FW_CdxCreate( [caTagList\ ) but not in the function list.
I do know by now that not all functions are updated in the manuals (is very time consuming, i know)

I figured out that it is inside the database class. I suppose for most of you it is common practice to look into classes and find the function, annalyse it and use it that way.
I'm I correct in this assumption ?

Doing that, I see inside the function that it also accepts a array. I suppose this array can be filled with the field, but also with the tagnames that I want.
The CDX file is not deleted by the function. Should it be done before or not ?
With a Fieldlist, it will be nessessary to at a "DELETED" in order to have that tag.

It is this way to analyse right ? (of course I know that the Forum will always help !! )

[Marc Venken]

field is also is a declaration just like local, static.

When we declare

field CITY,AGE
local nVar, cVar

Then wherever we use CITY, AGE the compiler understands that it is a field name and when we use nvar and cvar it understands that it is a local variable.

This declaration is helpful to use to create indexes, filter expressions without using alias->

Ok, Than I will need to update some more code (I would like to do it good )

1. I hardly ever use Local
2. I never use field (had no idea)

So I have always this kind of code :

Code: Select all  Expand view

       case upper(left(source->type,8)) = "GESLACHT" .and. upper(left(source->waarde,4)) <> "PART"
         if !empty(target->geslacht)
             target->geslacht = alltrim(target->geslacht) + ", "
         endif
         target->geslacht = alltrim(target->geslacht) + alltrim(source->waarde)
        case upper(left(source->type,7)) = "GEWICHT"


function toptextxt()
   use c:\programmas\fotoselect\lever\topfull NEW ALIAS source
   use master NEW
   select source
   source->(dbgotop())
   do while !source->(eof())
      if master->(dbseek(source->Code))
         if len(alltrim(source->memo)) > 1
            cMemo = '<p><strong><span style="color: #4f77e6;"><span style="text-decoration: underline;">Eigenschappen</span> : </span></strong></p>'
            cData = alltrim(source->memo)
            cData := StrTran( cData, ".", "<BR>" )
            cMemo = cMemo + '<p>'+ cData + '</p>'
            master->memotxt = cMemo
         endif
      endif
      source->(dbskip())
   enddo
   master->(dbgotop())
   xbrowse("master")
   close all
return NIL

// A typical scope used for Xbrowse :

STATIC FUNCTION SET_SCOPE(oBrw2)
LOCAL cNName := upper(nofoto->reflev)


DBSELECTAREA( "foto" )
("foto")->( ORDSCOPE(0, cNName ) )
("foto")->(ORDSCOPE(1, cNName ) )
oBrw2:Refresh()
// zoek en toon extra foto

("master")->( ORDSCOPE(0, "" ) )
("master")->(ORDSCOPE(1, "" ) )

 if empty(nofoto->reflev)
    if upper(master->filename) = "MAVECO.DBF"
       cNName := alltrim(STRTRAN(nofoto->id, '.', ''))
    else
       cNName := alltrim(upper(nofoto->ID))
    endif
 endif

l_exit = .F.
do while .t.


   DBSELECTAREA( "master" )

   master->(dbsetorder("fabref"))
   if master->(dbseek(cNName))
      exit
   endif

   master->(dbsetorder("code"))
   if master->(dbseek(cNName))
     exit
   endif

   master->(dbsetorder("levref"))
   if master->(dbseek(cNName))
      exit
   endif


   master->(dbsetorder("levref"))
   if !master->(dbseek(alltrim((nofoto->art_lev))))
     exit
   endif

   master->(dbsetorder("code"))
   if !master->(dbseek(alltrim(STRTRAN(nofoto->id, '.', ''))))
     exit
   endif
   if !l_exit
      cNName := alltrim(upper(nofoto->ID))
      loop
      l_exit = .t.
   endif
exit
enddo
("master")->( ORDSCOPE(0, cNName ) )
("master")->(ORDSCOPE(1, cNName ) )
oBrw4:Refresh()


RETURN NIL

 

So the above code is not good practice. It will benefic with the changes to the locals and fields.

Witch part is best to change ?