System info, Tips & Tricks, scharing project info

[Marc Venken]

Hello,

I started my new project (Invoices) and abbonded my older version for a fresh new one. The looks will become better and the programming structure should also become more advances.
Alow me to use this Topic to have the confirmation on topics that I doubt or that I would prefere a better solution that the one I already have. Some topics will be beginners questions, but I noticed
that I use stuff from the early day that work, but should be altered to a newer function or synstax...

[Marc Venken]

Code: Select all  Expand view  RUN

         case nBruto > 0 .and. nAankoop > 0
           replace (cAlias)->bedrag with nofoto->bruto
           replace (cAlias)->aankoop with nofoto->aankoop
           replace (cAlias)->prijs1 with  nofoto->bruto
           replace (cAlias)->marge1 with  (  (nofoto->bruto*100)  /  nozdiv(nofoto->aankoop) ) - 100
           replace (cAlias)->korting with 100 - ( nofoto->aankoop / nozdiv( nofoto->bruto ) * 100 )
           lOk=.t.

 

This kind of stuff I use a lot for changing data into the dbf. (over the Network : 3 users)

Today I realised that this is calling the dbf many and many times (eacht nofoto->bruto is a call to the dbf

One TDatabase object would solve this (checking for data inside it ) just 1 call to dbf. This is correct ?

Then the replace of the data : Each replace will do a call to the dbf on the server (here = 5 times)
Should I also consider a tData or a Hash to first fill all data and then call 1 to oDbf:save()

Need to say that this routine is sitting in a loop and can be called for few, but also for many hundreds of records each time.

[Marc Venken]

My count of Indexes are growing. For products now, there are 14. Several off them are only used in a update routine, but since they are in the initial cdx, they will be active all the time.
Is it adviced that we only make a index for the CDX for main Tags like, code, name, firstname and for all others make a temp index (even with the tmp clause) that i discovered.

Except that there is one 1 call when we open the cdx over the network, all tags will be repositioned each time we move a record, so slowing down the program (or netwerk trafic)?

[Marc Venken]

One of my most used routines, and also the one that is giving frequently the most problems with speed and even freezing is this :

I Use Xbrowse with many fields in the top (Barget function from Xbrowse). I fill data like :

color : Red
Brand : Nike
sleeves : Short

and hit the action button that calls : Marc_Setfilter()

I know that Filters to work good should be optimised (from posts), but many times the search fields are not indexed, So this is not good I think. (this become a speed issue)

Maybe I should reconsider this total function and make a dialog filter system where I can ask the needed info ?

Maybe I need to make (temp) indexes for all the search fields from that browse, so they become optimised ?

Code: Select all  Expand view  RUN

:bBarGetAction := {|| ( oBrwSel:cAlias )->( MARC_SETFILTER( oBrwSel ) ) }  

FUNCTION MARC_SETFILTER( oBrw ) // SETFILTER()?? INTERN COMMAND.

   LOCAL cFilter := ""
   LOCAL n, oCol, uVal, cType

   FOR n := 1 TO Len( oBrw:aCols )

      oCol  := oBrw:aCols[ n ]

      IF ! Empty( uVal := oCol:uBarGetVal )

         IF !Empty( cFilter )
            cFilter  += " .AND. "
         ENDIF

         cType    := ValType( uVal )

         DO CASE
         CASE cType == 'C'
            uVal     := Upper( AllTrim( uVal ) )
            cFilter += '"' + uVal + '" $ UPPER( ' + oCol:CExpr + " )"
         OTHERWISE
            cFilter  += oCol:cExpr + " == " + cValToChar( uVal )
         ENDCASE
      ENDIF

   NEXT

   IF Empty( cFilter )

      IF ! Empty( dbFilter() )
         dbClearFilter()
         oBrw:Refresh()
      ENDIF

   ELSE

      IF !( dbFilter() == cFilter )
         msginfo(cFilter)
         SET FILTER TO &cFilter
         GO TOP
         oBrw:Refresh()
      ENDIF

   ENDIF

   oBrw:SetFocus()

RETURN NIL




 

[Otto]

Hello Marc,
would you mind telling me how many records the database has?
Best regards,
Otto

[James Bott]

Marc,

My count of Indexes are growing. For products now, there are 14. Several off them are only used in a update routine, but since they are in the initial cdx, they will be active all the time.

Is it adviced that we only make a index for the CDX for main Tags like, code, name, firstname and for all others make a temp index (even with the tmp clause) that i discovered.

they will be active all the time.

Whether you use a filter or a temporary index, you are going to have to read the entire database over the network for each field replacement.

Except that there is one 1 call when we open the cdx over the network, all tags will be repositioned each time we move a record, so slowing down the program (or netwerk trafic)?

I don't understand what you are saying above? Define "tags." Do you mean current record? Each user's database's current record can be different at the same time. And their current index can be different than everyone else's. Also, you should have exclusive use of the database while group updates are being made, so it pretty much must be done when nobody else is using the app.

We need to know more about this routine. Is it used often or rarely. Explain when you would need to use this routine. I am having a hard time grasping when you would need to change several fields in an item database for a subset of the database. I don't think I have ever had to do this.

What is this application for?

[Otto]

Dear James,
may I ask your advice.

On the web, we read the data and then hand it over to the client.
Is there perhaps a different approach to choose as on desktop where an object has a certain life?
Is our task on the backend simply to achieve the fastest processing speed on reading and writing from/to the database.

Do we maybe have a complete different situation?
Best regards,
Otto

[James Bott]

Otto,

On the web, we read the data and then hand it over to the client.
Is there perhaps a different approach to choose as on desktop where an object has a certain life?
Is our task on the backend simply to achieve the fastest processing speed on reading and writing from/to the database

When you are using a database, whether local or on a LAN server, and you do a seek for one record, then you are just passing one record back to the local computer.

However, Marc wants to change a group of records that are not available in an index, so he is doing a filter. Using a filter requires that you read the entire database across the network. Ideally, I would use a complex index, i.e. FIELD1+FIELD2+FIELD3. Then using this index you only need a SEEK then read (and write) the number of records that match. Thus, you only send those records across the LAN (and back).

Note that once you do a SEEK and replace, then the record falls out of the index. So you have to do a new SEEK after each replace until the SEEK doesn't find any more matches.

However, it appears that Marc's routine allows the user to pick the fields for the replace. This eliminates the possibility of using an index. So he has to use a plain filter which requires reading the entire database. And this requires the database to be in exclusive use mode, so it needs to be done when no other users are using the app. This should make speed not all that important. Users will just have to wait until the routine is done until they can log back into the program.

James

[Otto]

Dear James,

Thanks very much.
As you contributed so much with your "Introduction to FIVEWIN"
and your DATABASE class for our language, I hope you can do the same for mod harbour.

I posted under this topic, but I don't mean in this particular case. I mean for WEB programming in general and with DBF in particular.

I have a feeling that with DBF the query will have to be built more individually. There is no direct connection between the database and the client browser.

I don't have a lot of practical experience yet, but from what I've seen, for example, an AJAX request calls a small program, which returns the result.

You also have to be aware of the technical development.

For example, if I remember correctly, in WINDOWS 2000 there was a limit of about 20000 files per directory. Today there is practically no restriction. At that time, you had to store the images - in a folder with many images - in the database, but today you can simply save them as files on the hard drive.

The difference in the read/write speed between the main memory (RAM) and the hard disk (SSD) has also greatly reduced and is approaching zero.

In the future, there will no longer be any difference between hard disk access and main memory access.

In my new programs, I include the file system for storing the data. For example, one folder per customer in each folder subfolder with invoice, correspondence, etc. I do not use an extra layer - database.
And I do not add a database system like SQL to my programs, where you have to give up all your freedom and depend on a third party.

We should build a set of mod harbour sample for read and write data with best practice.
What do you think?

Best regards,
Otto

[Marc Venken]

Hello Marc,
would you mind telling me how many records the database has?
Best regards,
Otto

44.000 records

[Marc Venken]

Otto,

However, Marc wants to change a group of records that are not available in an index, so he is doing a filter. Using a filter requires that you read the entire database across the network. Ideally, I would use a complex index, i.e. FIELD1+FIELD2+FIELD3. Then using this index you only need a SEEK then read (and write) the number of records that match. Thus, you only send those records across the LAN (and back).

Note that once you do a SEEK and replace, then the record falls out of the index. So you have to do a new SEEK after each replace until the SEEK doesn't find any more matches.

However, it appears that Marc's routine allows the user to pick the fields for the replace. This eliminates the possibility of using an index. So he has to use a plain filter which requires reading the entire database. And this requires the database to be in exclusive use mode, so it needs to be done when no other users are using the app. This should make speed not all that important. Users will just have to wait until the routine is done until they can log back into the program.

James

This is exact what I'm doing, but this is only done at the moment of mass updating and this is only done by me. indeed filtering on all possible options. Ok, the speed will process all data, but i wonder ....

If I make a second solution for my other users (they just want info about a brand or so)

Dbf = 10.000 records and I select a products brand, that will give me a result of 700 records. With the filter function I posted here, will the function process all 10.000 or position to the 1st brand and just process the 700 records.
There is a index on the brand Tag, but I dont think that the function is using it. In relation there is a scoped call involved...

Idem.

[Marc Venken]

Otto,

However, it appears that Marc's routine allows the user to pick the fields for the replace. This eliminates the possibility of using an index. So he has to use a plain filter which requires reading the entire database. And this requires the database to be in exclusive use mode, so it needs to be done when no other users are using the app. This should make speed not all that important. Users will just have to wait until the routine is done until they can log back into the program.

James

Indeed, speed is not that important, because I simply can ask my wife to stop working on the CPU for 5 mins ))))

The more a problem is that using several combinations and so calling the function several times (ex. 10 calls) It seems that the program is going to freeze. I hardly ever have been thinking of releasing objects, fonts, etc. and have no clue about
memory leaking, so this could also be the problem for that, or maybe I need to call a function to clean a network buffer ?

[James Bott]

Marc,

The more a problem is that using several combinations and so calling the function several times (ex. 10 calls) It seems that the program is going to freeze. I hardly ever have been thinking of releasing objects, fonts, etc. and have no clue about
memory leaking, so this could also be the problem for that, or maybe I need to call a function to clean a network buffer ?

Well, after looking at your posted code for the filter, I see that you are filtering the browse not the database. That's new to me, so I don't know which would be faster. I suspect the browse filter since it is only going to process enough records to fill the browse. Whereas, I think, a database filter is going to process the whole database so it can build a "filter" of just those records meeting the conditions. Then the browse is redisplayed.

Whenever you are wondering about speed it is best just to record the start and end times of a routine, then calculate the difference and record it. Then try another method and check for the fastest one.

And, yes, you should be releasing resources or at some point that is going to be an issue. And it will be hard to find since you won't know where it is leaking.

[Jimmy]

hi

as James say you are using FILTER on "Skipper" of "Browse"
DbSetFilter() will use hole DBF and "Skipper" will test until EOF() / BOF()

i use DbSetFilter() only with SCOPE
SCOPE will "limit" EOF() / BOF()

[James Bott]

Marc,

Just revisiting this old message thread.

This code of yours:

Code: Select all  Expand view  RUN

           replace (cAlias)->bedrag with nofoto->bruto
           replace (cAlias)->aankoop with nofoto->aankoop
           replace (cAlias)->prijs1 with  nofoto->bruto
           replace (cAlias)->marge1 with  (  (nofoto->bruto*100)  /  nozdiv(nofoto->aankoop) ) - 100
           replace (cAlias)->korting with 100 - ( nofoto->aankoop / nozdiv( nofoto->bruto ) * 100 )
 

           
Using OOP, the above lines would be something like below.

I substituted oLineItem for cAlias) and oPart for the "nofoto" alias. Note that with objects you don't ever need to use aliases. And it is much easier to understand when you use real world terms.

Code: Select all  Expand view  RUN

           oLineItem:bedrag  := oPart:bruto
           oLineItem:aankoop := oPart:aankoop
           oLineItem:prijs1  := oPart:bruto
           oLineItem:marge1  := ( oPart:bruto * 100 / nozdiv( oPart:aankoop) ) -100
           oLineItem:korting := 100 - ( oPart:aankoop / nozdiv( oPart:bruto ) * 100 )
 

Regards,
James