Xbrowse a speed problem using SCOPE ?

[James Bott]

Uwe,

I am still not understanding how you are getting this to work.

From the Clipper manual for SET RELATION:

TO <expKey> is an expression that performs a SEEK in the child work
area each time the record pointer moves in the parent work area. For
this to work, the child work area must have an index in USE.

So when you move the record in the customer file all that is suppose to happen is a seek in the services file on the key value. Thus in the first graphic you should be seeing services for customer 4 at the top, then followed by customers 5, and 6. But your graphic shows only 4. Please explain how this happens without using a SCOPE.

James

[ukoenig]

James did You have a look at the provided DOWNLOAD ?
Exe and prg is included.
The logic ( new exe-file ) has been tested by my customer who was verry happy with the speed.

best regards
Uwe

[James Bott]

Uwe,

Yes, I am looking at it, but it is too complex for me to understand.

Did you understand my previous message?

James

[James Bott]

Uwe,

Here is an example of what you should see with SET RELATION (cursor is at Cust 3):

Code: Select all  Expand view  RUN

Cust  Service
1      3
2      3
3<     4
4      4
5      4
6      5
       6

The SET RELATION does a SEEK on 3 iin the service file so the pointer is at 3 but there is no upper limit, so you will also see 4, 5, and 6 in this case.

So, I do not understand why yours is limited to 3 unless it is an artifact of your test method.

James

[ukoenig]

James,

Yes I understand I know about SETRELATION is doing a seek
Maybe any combination included that it is working without any SCOPE

I can explain in detail the included sample.

I searched in FWH \samples for ORDSETRELATION
but NO result

only found in /source/database.prg

Code: Select all  Expand view  RUN

METHOD SetRelation( uChild, cRelation, lScoped ) CLASS TDataBase
DEFAULT lScoped   := .f.

if ValType( uChild ) == 'O'
   uChild   := uChild:nArea
endif

if lScoped
   ( ::nArea )->( OrdSetRelation( uChild, Compile( cRelation ), cRelation ) )
else
   ( ::nArea )->( DbSetRelation( uChild, Compile( cRelation ), cRelation ) )
endif

return nil
 

best regards
Uwe

[karinha]

Very good. Thanks.

Code: Select all  Expand view  RUN

#include "FiveWin.ch"
#include "xbrowse.ch"

ANNOUNCE RDDSYS
REQUEST OrdKeyNo, OrdKeyCount, OrdCreate, OrdKeyGoto
REQUEST DBFCDX, DBFFPT

STATIC c_Pfad, oBrw1, oBrw2

FUNCTION MAIN()

   LOCAL oDlg1, cMonat := "VPOS12", oFontSys
 
   c_Pfad := cFilePath(GetModuleFileName( GetInstance() ) )

   DEFINE FONT oFontSys NAME "Arial" SIZE 0, -14

   RDDSETDEFAULT ( "DBFCDX" )

   NET_USE ( c_Pfad + "KUNDE.dbf", "KUNDE", 3, .T. )
   DBSELECTAREA("KUNDE")
   ORDCREATE( ,"KUNDE1","UPPER(NAME1 + NAME)", ;
            {|| UPPER(NAME1 + NAME) } , .F. )

   NET_USE ( c_Pfad + cMonat + ".dbf", "VPOS12", 3, .T. )
   ORDCREATE( ,"VPOS12", "UPPER(KUNDE + NAME1 + KENNER)", ;
            {|| UPPER(KUNDE + NAME1 + KENNER) } , .F. )  // .T. = UNIQUE

   DEFINE DIALOG oDlg1 RESOURCE "Liste3"  TITLE  "Test SETRELATION" PIXEL

   DBSELECTAREA( "KUNDE" )

   SET ORDER TO TAG KUNDE1

   ( "KUNDE" )->(DBSETORDER( "KUNDE1" ))
   ( "KUNDE" )->( ORDSETRELATION( cMonat, { || UPPER(NAME1 + NAME) }, "UPPER(NAME1 + NAME)" ) )
   ( "KUNDE" )->( DBGOTOP() )

   REDEFINE XBROWSE oBrw1 ID 110 OF oDlg1 AUTOSORT ;
            COLUMNS { "NAME1", "NAME" } ;
            ALIAS "KUNDE"

   oBrw1:nMarqueeStyle     := MARQSTYLE_HIGHLROWRC
   oBrw1:nColDividerStyle  := LINESTYLE_BLACK
   oBrw1:nRowDividerStyle  := LINESTYLE_BLACK
   oBrw1:nRowHeight                := 25
   oBrw1:lAllowRowSizing   := .F.
   AEval( oBrw1:aCols, {|oCol|oCol:lAllowSizing := .F. } )
   oBrw1:SetFont( oFontSys  )

   oCol  := oBrw1:oCol( "NAME1" )
   oCol:bStrData           := { || ("KUNDE")->NAME1 }
   oCol:cHeader            := "Name 1"
   oCol:nWidth             := 120
   oCol:nDataStrAlign   := AL_LEFT

   oCol  := oBrw1:oCol( "NAME" )
   oCol:bStrData           := { || ("KUNDE")->NAME }
   oCol:cHeader            := "Name 2"
   oCol:nWidth             := 90
   oCol:nDataStrAlign   := AL_LEFT

   WITH OBJECT oBrw1
    :bChange := { ||    DBSELECTAREA( cMonat ), ;
                        ( cMonat )->( DBGOTOP() ), ;
                    oBrw2:Refresh() }
   END

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

   DBSELECTAREA( cMonat )
   ( cMonat )->(DBSETORDER( cMonat ))
   ( cMonat )->( DBGOTOP() )

   REDEFINE XBROWSE oBrw2 ID 130 OF oDlg1 AUTOSORT  FASTEDIT ;
            COLUMNS { "KUNDE", "NAME1", "POS0" } ;
            ALIAS cMonat

   oBrw2:nMarqueeStyle     := MARQSTYLE_HIGHLROWRC
   oBrw2:nColDividerStyle  := LINESTYLE_BLACK
   oBrw2:nRowDividerStyle  := LINESTYLE_BLACK
   oBrw2:nRowHeight                := 25
   oBrw2:lAllowRowSizing   := .F.
   AEval( oBrw2:aCols, {|oCol|oCol:lAllowSizing := .F. } )
   oBrw2:SetFont( oFontSys  )

   oCol  := oBrw2:oCol( "KUNDE" )
   oCol:bStrData           := { || (cMonat)->KUNDE }
   oCol:cHeader            := ""
   oCol:nWidth             := 50
   oCol:nDataStrAlign   := AL_LEFT

   oCol  := oBrw2:oCol( "NAME1" )
   oCol:bStrData           := { || (cMonat)->NAME1 }
   oCol:cHeader            := ""
   oCol:nWidth             := 50
   oCol:nDataStrAlign   := AL_LEFT

   oCol  := oBrw2:oCol( "POS0" )
   oCol:bStrData           := { || (cMonat)->POS0 }
   oCol:cHeader            := "Nr."
   oCol:nWidth             := 30
   oCol:nDataStrAlign   := AL_RIGHT
   
   ACTIVATE DIALOG oDlg1 CENTER

   DBSELECTAREA("KUNDE")
   NET_CLOSE( 3, 3 )
   DBSELECTAREA("VPOS12")
   NET_CLOSE( 3, 3 )

   oFontSys:End()

RETURN NIL

// ---------- NET - FUNCTIONS ----------------------------
// --------------------------------------------------------------------

FUNCTION NET_USE ( cDBName, cAlias, nTrials, lNet )

   LOCAL lReturn           := .T.
   LOCAL lOpen             := .F.
   LOCAL nTrcount  := nTrials
   LOCAL YesNo             := .F.

   // SHARED    all Users
   // EXCLUSIVE  1 User

   IF File( cDBName )
    DO WHILE nTrcount <= nTrials
        IF !lNet
            USE &cDBName ALIAS &cAlias NEW VIA "DBFCDX" EXCLUSIVE
        ELSE
            USE &cDBName ALIAS &cAlias NEW VIA "DBFCDX" SHARED
        ENDIF
        IF !NetErr()
            lReturn   := .T.
        ELSE
            IF lNet = .T.
                Status := "SHARED"
            ELSE
                Status := "EXCLUSIVE"
            ENDIF
            IF nTrcount = nTrials
                xName := WNetGetUser()
                IF MsgYesNo( "Öffnen " + Status + "  von " + upper(cDBName) + " nicht möglich !" + ;
                                                " neuer Versuch ?", "Netzwerk-Fehlerr -> &xName !" )
                    nTrials := nTrcount
                    IF nTrials > 0  
                        NET_WAIT ( nTrcount, nTrials )
                    ENDIF
                ELSE
                    lReturn   := .F.
                    EXIT
                ENDIF
            ENDIF
        ENDIF
        nTrials --
    ENDDO
   ELSE
    nMsgBox ("Die Datei -> " + cDBName + " fehlt !", "Netzwerk-Fehler !")
    SET RESOURCES to
    set _3DLOOK OFF
    FreeLibrary()
    CLOSE DATABASE
    QUIT
   ENDIF

RETURN lReturn

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

FUNCTION NET_CLOSE( nTrials )

   LOCAL lReturn           := .T.
   LOCAL nTrcount  := nTrials
   LOCAL cDBName   := DBF()

   DO WHILE nTrcount <= nTrials
    USE
    IF NETERR()
        nTrials --
        IF MsgYesNo( "Schließen von : " + upper(cDBName) + " nicht möglich !" + ;
            " Neuer Versuch ?", "Netzwerk-Fehler !" )
            IF nTrials > 0
                NET_WAIT ( nTrials )
            ENDIF
        ELSE
            lReturn   := .F.
            EXIT
        ENDIF
    ELSE
        lReturn   := .T.
        EXIT
    ENDIF
   ENDDO

RETURN lReturn

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

FUNCTION NET_WAIT ( nTrials )

   LOCAL oMeter, oText1

   MsgMeter( { | oMeter, oText1 | ;
   SHOW_WAIT( nTrials, oMeter, oText1) } , ;
   "Restversuche : " + LTRIM(STR( nTrials )) + ". Versuch..." )

RETURN NIL

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

STATIC FUNCTION SHOW_WAIT( nTrials, oMeter, oText1 )

   oMeter:nTotal = nTrials
   oText1:SetText( "Restversuche : " + LTRIM(STR( nTrials )))
   oMeter:Set( nTrials )
   SysRefresh()

RETURN nil
 



[ukoenig]

FOUND !!!!

It is not found in the Wiki
should be added, because very useful


ORDSETRELATION()
Relate a specified work area to the current work area
------------------------------------------------------------------------------
Syntax

ORDSETRELATION(<nArea> | <cAlias>,<bKey>, [<cKey>])
--> NIL

Arguments

<nArea> is the number of the child work area.

<cAlias> is the alias of the child work area.

<bKey> is a code block that expresses the relational expression in
executable form.

<cKey> is an optional string value that expresses the relational
expression in textual form. If <cKey> is supplied, it must be
equivalent to <bKey>. If <cKey> is omitted, ORDSETRELATION() returns a
null string ("") for the relation.

Returns

ORDSETRELATION() always returns NIL.

Description

ORDSETRELATION() relates the work area specified by <nArea> or <cAlias>
(the child work area) to the current work area (the parent work area).
Any existing relations remain active.

Relating work areas synchronizes the child work area with the parent
work area. This is achieved by automatically repositioning the child
work area whenever the parent work area moves to a new record. If there
is a controlling order in the child work area, moving the parent work
area causes an automatic seek operation in the child work area; the seek
key is based on the expression specified by <bKey> and/or <cKey>. If
the child work area has no controlling order, moving the parent work
area causes an automatic "go to" in the child work area; the record
number for the "go to" is based on the expression specified by <bKey>
and/or <cKey>.

ORDSETRELATION() is identical to DBSETRELATION()
(and the SET RELATION command),.
but it also sets up a scope on the order in the child work
area. This means that whenever you select the child work area, only the
records related to the current parent record will be visible. This
allows straightforward handling of one-to-many relationships.
Refer to DBSETRELATION() for more information.

Examples

This example displays each invoice with its related line
items:

USE LineTtem NEW VIA "DBFCDX"
SET ORDER TO TAG InvNo

USE Invoice NEW VIA "DBFCDX"

// Set a selective relation from Invoice into LineItem
ORDSETRELATION("LineItem", {|| Invoice->InvNo}, "Invoice->InvNo")

GO TOP
DO WHILE !EOF()
? InvNo, InvDate // Display invoice fields

SELECT LineItem

// Only records for current invoice # are visible
LIST " ", PartNo, Qty, Price

SELECT Invoice // On to next invoice

SKIP
ENDDO

best regards
Uwe

[James Bott]

Uwe,

Contrary to what that reference says, it is not what I see, nor has it ever been. All child records starting with the seek record to the end of the file are what I see.

Below is a minimal sample program showing what I see. You will need your two data files to run it.

Regards,
James

Code: Select all  Expand view  RUN

/*
Purpose  : Test SET RELATION statement
Author   : James Bott, jbott@compuserve.com
Date     : 3/1/2016 1:39:29 PM
Company  : Intellitech
Copyright: Copyright © 2016 Intellitech
Language : Fivewin/xHarbour
Updated  :
Notes    : Just showing how SET RELATION works.

*/

#include "fivewin.ch"


FUNCTION MAIN()
   LOCAL cString :="" , i:=1

   field kunde,name1,kenner, name

   REQUEST DBFCDX
   RDDSETDEFAULT ( "DBFCDX" )

   // Open the files
   use kunde alias "cust"
   index on upper(name1) to kunde

   select 0
   use vpos12 alias "serv"
   index on upper(name1) to vpos12

   // Set the relationship
   select cust
   set relation to upper(name1) into "serv"

   // Seek the customer (Service file automatically gets SEEKed also)
   seek "3 "

   // List the records in the Service file
   select serv
   for i=1 to 15
      cString:=cString + chr(10)+ serv->name1
      skip
   next

   msgInfo( cString, "Service records for Customer "+ ALLTRIM(cust->NAME1) )

Return nil

// EOF

[James Bott]

Uwe,

OK, I have found what is going on. These two lines are not equivalent:

set relation to upper(name1) into "serv"
ORDSETRELATION("serv", {|| cust->name1}, "Serv->name1")

ORDSETRELATION() does add a scope, but SET RELATION does not. I thought that SET RELATION just called ORDSETRELATION() but I guess I was confusing it with DBSETRELATION(). I learned something new.

Oddly, however, it is still using a scope so it should have the same speed as just setting a scope I would think.

James

[ukoenig]

James

the changes just change the customer "4 " to any other existing number
Because a customer can have the same surename, You need a index < surename + forename >
In the sample just BROWSE() the result
I will create a nice sample with existing DBF's we can add to FWH\samples\

Code: Select all  Expand view  RUN

/*
Purpose  : Test SET RELATION statement
Author   : James Bott, jbott@compuserve.com
Date     : 3/1/2016 1:39:29 PM
Company  : Intellitech
Copyright: Copyright © 2016 Intellitech
Language : Fivewin/xHarbour
Updated  :
Notes    : Just showing how SET RELATION works.

*/

#include "fivewin.ch"

FUNCTION MAIN()
LOCAL cString :="" , i:=1

field kunde, name1, name

REQUEST DBFCDX
RDDSETDEFAULT ( "DBFCDX" )

// Open the files
use kunde alias "cust"
index on upper(name1 + name) to kunde  // use surename + forename

select 0
use vpos12 alias "serv"
index on upper(Kunde + name1) to vpos12 // Services in month december

// Set the relationship
select cust
( "cust" )->( ORDSETRELATION( "serv", { || UPPER(NAME1 + NAME) }, "UPPER(NAME1 + NAME)" ) )
( "cust" )->( DBGOTOP() )

// Seek the customer (Service file automatically gets SEEKed also)
( "cust" )->(DBSEEK("4 "))

// List the records in the Service file
select serv
BROWSE()

//for i=1 to 15
//      cString:=cString + chr(10)+ serv->name1
//      skip
//next
//msgInfo( cString, "Service records for Customer "+ ALLTRIM(cust->NAME1) )

Return nil
 

best regards
Uwe

[James Bott]

Uwe,

Because a customer can have the same surename, You need a index < surename + forename >

Understood, I was trying to make a sample as simple as possible. It is also possible to have two or more people with the same surname and forename. And presumably users can edit the name fields which can create all kinds of problems. These are all very good reasons for always using ID numbers for primary keys. I would have a CUSTNO (or similar) in both files. And not allow users to change them.

I am planning to do some speed tests with a DBSetRelation() & setScope() and with just OrdSetRelation() to see if there are any significant speed differences.

James

[nageswaragunupudi]

Mr James

Scoped orders ( OrdSetRelation or SET RELATION .. SCOPED ) worked well for me for many years.

Like you said it is always a good idea to link tables with IDs not by names.
Apart from the fact that is a safe and good programming practice, it also has the merit of having smaller indexes. Smaller indexes is a key to get get good speeds.

[James Bott]

Nages,

Scoped orders ( OrdSetRelation or SET RELATION .. SCOPED ) worked well for me for many years.

According to the Clipper manual there was no SCOPED clause for Clipper.

Code: Select all  Expand view  RUN

SET RELATION
 Relate two work areas by a key value or record number
------------------------------------------------------------------------------
 Syntax

     SET RELATION TO [<expKey> | <nRecord> INTO <xcAlias>]
        [, [TO] <expKey2> | <nRecord2> INTO <xcAlias2>...]
        [ADDITIVE]

 

The SCOPED clause must have been added to (x)Harbour and I never saw any reference to it until now. This is why I never saw much use for SET RELATION, thus I rarely, if ever, used it. Now, with scoping, I can see it is much more useful. Thanks for pointing that out as I am sure I am not the only one that didn't know about it.

I do wonder though, why setting a relationship and scoping would be any faster that just scoping? It seems like it would have to be slower since there is another step involved. And what advantage is there to setting a relationship when scoping is all you need?

Regards,
James

[ukoenig]

James,

I think a defined SCOPE starts from top and collects records until reaching the end
A relation is doing a SEEK collecting records and breaks reaching values are <> from start

best regards
Uwe

[James Bott]

Uwe,

I think a defined SCOPE starts from top and collects records until reaching the end

If by "top" you mean the top of the scope, then yes, but if you mean the top of the file, then no.

A scope requires an index on the field to be scoped. It does a seek on the index key, then only reads until the key changes. Thus, if you have a file containing 1000 records but only 10 of them are within the scope, then the database appears to only contain those 10 records. Unlike a filter it does not read the entire database looking for records that match a filter. A scope only reads the index for the records and they are already sorted in order of the criteria so it only has to read records within the scope.

With a scope you can do a GO TOP, GO BOTTOM, SKIP, etc. and only records within the scope will be visible.

If you wish to do a parent-child database relationship using a scoped child database, you (the programmer) must set the scope for the child database on each movement of the parent database's record pointer. If you do a ORDSETRELATION() with the scoped parameter, you don't need to set the scope for each movement of the parent database's record pointer--the set relationship does that for you. I would think that these two methods would be pretty much the same speed.

Where the speed might be slower, is if you are doing a GO TOP on the child database, when you have already set the scope using ORDSETRELATION(). This would cause the GO TOP to be issued twice. However, I can't imagine the speed difference would be noticeable to the user.

Thus, I think you had a memory leak somewhere in your program that was causing the slowdown. I don't know how switching to ORDSETRELATION() would have made the memory leak go away, unless something else in your code was also changed.

James