xbrowse slow

xbrowse slow

Postby artu01 » Wed Mar 15, 2017 12:56 am

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() )

 
fwh 17.12, harbour 3.2.0, pelles C, bcc7, Ms-Sql
artu01
 
Posts: 397
Joined: Fri May 11, 2007 8:20 pm
Location: Lima

Re: xbrowse slow

Postby nageswaragunupudi » Wed Mar 15, 2017 1:06 am

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

G. N. Rao.
Hyderabad, India
User avatar
nageswaragunupudi
 
Posts: 10206
Joined: Sun Nov 19, 2006 5:22 am
Location: India

Re: xbrowse slow

Postby Marc Venken » Thu Mar 16, 2017 11:20 am

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 ?
Marc Venken
Using: FWH 23.04 with Harbour
User avatar
Marc Venken
 
Posts: 1338
Joined: Tue Jun 14, 2016 7:51 am
Location: Belgium

Re: xbrowse slow

Postby Rick Lipkin » Thu Mar 16, 2017 12:59 pm

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
User avatar
Rick Lipkin
 
Posts: 2606
Joined: Fri Oct 07, 2005 1:50 pm
Location: Columbia, South Carolina USA

Re: xbrowse slow

Postby Marc Venken » Thu Mar 16, 2017 1:03 pm

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...
Marc Venken
Using: FWH 23.04 with Harbour
User avatar
Marc Venken
 
Posts: 1338
Joined: Tue Jun 14, 2016 7:51 am
Location: Belgium

Re: xbrowse slow

Postby nageswaragunupudi » Thu Mar 16, 2017 1:21 pm

Marc Venken wrote: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()
Regards

G. N. Rao.
Hyderabad, India
User avatar
nageswaragunupudi
 
Posts: 10206
Joined: Sun Nov 19, 2006 5:22 am
Location: India

Re: xbrowse slow

Postby Marc Venken » Thu Mar 16, 2017 2:13 pm

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 :oops: will update this one.
Marc Venken
Using: FWH 23.04 with Harbour
User avatar
Marc Venken
 
Posts: 1338
Joined: Tue Jun 14, 2016 7:51 am
Location: Belgium

Re: xbrowse slow

Postby artu01 » Thu Mar 16, 2017 8:44 pm

Dear Mr. Rao
Now run perfect

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


Thanks
fwh 17.12, harbour 3.2.0, pelles C, bcc7, Ms-Sql
artu01
 
Posts: 397
Joined: Fri May 11, 2007 8:20 pm
Location: Lima

Re: xbrowse slow

Postby nageswaragunupudi » Fri Mar 17, 2017 12:04 am

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.
Regards

G. N. Rao.
Hyderabad, India
User avatar
nageswaragunupudi
 
Posts: 10206
Joined: Sun Nov 19, 2006 5:22 am
Location: India

Re: xbrowse slow

Postby artu01 » Sat Mar 18, 2017 4:43 am

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?
fwh 17.12, harbour 3.2.0, pelles C, bcc7, Ms-Sql
artu01
 
Posts: 397
Joined: Fri May 11, 2007 8:20 pm
Location: Lima

Re: xbrowse slow

Postby Marc Venken » Sat Mar 18, 2017 9:30 am

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 ?
Marc Venken
Using: FWH 23.04 with Harbour
User avatar
Marc Venken
 
Posts: 1338
Joined: Tue Jun 14, 2016 7:51 am
Location: Belgium

Re: xbrowse slow

Postby nageswaragunupudi » Sat Mar 18, 2017 9:44 am

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?
Regards

G. N. Rao.
Hyderabad, India
User avatar
nageswaragunupudi
 
Posts: 10206
Joined: Sun Nov 19, 2006 5:22 am
Location: India

Re: xbrowse slow

Postby nageswaragunupudi » Sat Mar 18, 2017 9:47 am

Marc Venken wrote:
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->
Regards

G. N. Rao.
Hyderabad, India
User avatar
nageswaragunupudi
 
Posts: 10206
Joined: Sun Nov 19, 2006 5:22 am
Location: India

Re: xbrowse slow

Postby Marc Venken » Sat Mar 18, 2017 9:52 am

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 !! :D )
Last edited by Marc Venken on Sat Mar 18, 2017 10:48 am, edited 1 time in total.
Marc Venken
Using: FWH 23.04 with Harbour
User avatar
Marc Venken
 
Posts: 1338
Joined: Tue Jun 14, 2016 7:51 am
Location: Belgium

Re: xbrowse slow

Postby Marc Venken » Sat Mar 18, 2017 10:25 am

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 :wink: )

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 ?
Marc Venken
Using: FWH 23.04 with Harbour
User avatar
Marc Venken
 
Posts: 1338
Joined: Tue Jun 14, 2016 7:51 am
Location: Belgium

Next

Return to FiveWin for Harbour/xHarbour

Who is online

Users browsing this forum: No registered users and 10 guests