topic-33776

Posts: 400

Joined: Fri May 11, 2007 08:20 PM

Re: xbrowse slow

Posted:

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 (fw): Select all Collapse

       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: 400

Joined: Fri May 11, 2007 08:20 PM

xbrowse slow

Posted: 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 (fw): Select all Collapse

 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

nageswaragunupudi

Posts: 10733

Joined: Sun Nov 19, 2006 05:22 AM

Re: xbrowse slow

Posted: Wed Mar 15, 2017 01: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

Marc Venken

Posts: 1487

Joined: Tue Jun 14, 2016 07:51 AM

Re: xbrowse slow

Posted: 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 (fw): Select all Collapse

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.08 with Harbour

Rick Lipkin

Posts: 2707

Joined: Fri Oct 07, 2005 01:50 PM

Re: xbrowse slow

Posted: 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

Marc Venken

Posts: 1487

Joined: Tue Jun 14, 2016 07:51 AM

Re: xbrowse slow

Posted: Thu Mar 16, 2017 01: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.08 with Harbour

nageswaragunupudi

Posts: 10733

Joined: Sun Nov 19, 2006 05:22 AM

Re: xbrowse slow

Posted: Thu Mar 16, 2017 01: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 (fw): Select all Collapse

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 (fw): Select all Collapse

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 (fw): Select all Collapse

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

Marc Venken

Posts: 1487

Joined: Tue Jun 14, 2016 07:51 AM

Re: xbrowse slow

Posted: Thu Mar 16, 2017 02: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

will update this one.

Marc Venken

Using: FWH 23.08 with Harbour

artu01

Posts: 400

Joined: Fri May 11, 2007 08:20 PM

Re: xbrowse slow

Posted: Thu Mar 16, 2017 08: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

nageswaragunupudi

Posts: 10733

Joined: Sun Nov 19, 2006 05:22 AM

Re: xbrowse slow

Posted: 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

Marc Venken

Posts: 1487

Joined: Tue Jun 14, 2016 07:51 AM

Re: xbrowse slow

Posted: Sat Mar 18, 2017 09: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.08 with Harbour

nageswaragunupudi

Posts: 10733

Joined: Sun Nov 19, 2006 05:22 AM

Re: xbrowse slow

Posted: Sat Mar 18, 2017 09: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

nageswaragunupudi

Posts: 10733

Joined: Sun Nov 19, 2006 05:22 AM

Re: xbrowse slow

Posted: Sat Mar 18, 2017 09: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

Marc Venken

Posts: 1487

Joined: Tue Jun 14, 2016 07:51 AM

Re: xbrowse slow

Posted: Sat Mar 18, 2017 09: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 !!

)

Marc Venken

Using: FWH 23.08 with Harbour

Marc Venken

Posts: 1487

Joined: Tue Jun 14, 2016 07:51 AM

Re: xbrowse slow

Posted: 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

)

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

So I have always this kind of code :

Code (fw): Select all Collapse

        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.08 with Harbour

FiveTech Support Forums

Re: xbrowse slow

xbrowse slow

Re: xbrowse slow

Re: xbrowse slow

Re: xbrowse slow

Re: xbrowse slow

Re: xbrowse slow

Re: xbrowse slow

Re: xbrowse slow

Re: xbrowse slow

Re: xbrowse slow

Re: xbrowse slow

Re: xbrowse slow

Re: xbrowse slow

Re: xbrowse slow