Networking Question, when are changes updated without Buffering? 2

[ManniB]

Hello,

please consider the following basic networking question:

Assuming buffering is disabled and there are only free tables. When both User1 and User2 have opened the same table simultaneously and User1 makes changes like adding records and then issues FLUSH, when will User2 start do see the changes? Does VFP fetch Data for User2 periodically? Does User2 have to close the table and open it again to see any changes? Does User2 see the changes after issuing a FLUSH command? Could a FLUSH of User2 remove records which User1 has added in the meantime?

Thank you in advance!
Manni

 

[ManniB]

Thank you, Chriss, actually, it seems like opportunistic row buffering is the path I'll go in the future, however, at the moment it would take a lot if time to change the whole application, so I'll have to make the best out of it for now with no buffering in place and try to avoid conflicts as much as possible.

In the current situation I get errors when performing UPDATE-SQL while another user makes changes on a record that is not affected by the UPDATE-SQL, but not with SCAN + IF + REPLACE. It might contribuite to the problem that exe is running in the server (a desktop shortcuts from different work stations all calling the same exe on the "server") as I've read others had similar problems in this situation. Also, I tested it when two users enter the same textbox on a form with the same recordsource / same record, I get an error like "Data is already in use by another person". I don't have to leave the textbox to trigger the valid event for the error to appear, it's enough if one user writes a new letter inside the textbox, lets the cursor stay inside the textbox and "leaves the office for lunch". Then the other user can't enter the same textbox. So I guess networking with VFP without buffering requires a lot additional code for locking, error handling etc. and many times I'll just have to prevent users from opening forms, i.e. to access general settings of the application or opening a form for the same customer, while another user is already doing edits for the same customer, all by using manual locking.

Thanks,
Manni

 

[Chris Miller]

Opportunistic is not a variant for VFP buffering, optimistic or pessimistic. Opportunistic comes from the SMB network protocol and is not done by VFP, it's a plague.

And it's optimistic table buffering that's most versatile and least intrusive to other users. That means you can do like row buffering and save a row for example in afterrowcolchange of a grid with TABLEUPDATE() just committing only the active row. Since changing a row in that mode does not mean automatic commits you can check whether you would encounter uniqueness problems by seeking or locating other rows without causing a commit. And then do a "well informed" commit, for example, in the sense you are rather sure it will not violate something like that and work.

Don't shy away from table buffering. I have the impression developers not used to it think you start by buffering all data of the table or it means locks are table locks. No, it just means you buffer multiple row changes, as they come and can decide to do commits of single rows or all buffered changes, as the TABLEUPDATE function is offering the options to control that.

You server-side exe is only read by all clients, it still runson their clients CPU, not on the server. And the network is still their bottleneck in file access, the file share isn't local to them because the EXE file is in the same drive. Indeed your major problem is replacing that EXE while a user uses it. Well, and any start or paging of EXE also contributes to the network load. It would be simple to change that to a startup procedure with a batchfile, without changing any code at all.

Chriss

 

[ManniB]

Sorry, I meant optimistic buffering, of course, not opprtunistic.

Yes, I also asumed that table buffering means locking the whole table and therefore didn't consider it. It seems more plausible now to use it, indeed!

It would be simple to change that to a startup procedure with a batchfile, without changing any code at all.

Could you please explain how this can be done?

Thanks,
Manni

 

[Chris Miller]

I assume you have exe and runtimes dlls in your server file share, is that right? Or is the data also in that directory?

You would of course not want to copy over data so everybody has a local database but there is no centralized data.

But assume you even only have the EXE there and every workstation has an install of the VFP runtimes, then you mainly need a bat or cmd file with

xcopy serverdirectory localdirectory
start localdirectory\your.exe

Lookup the options for xcopy to a) create a localdirectory if it not exists and b) only copy newer file. I can look it up, but not right now.

Now, there might just be one issue I can imagine (perhaps more, but you'll see), that is you rely on the start in the server directory to let this be the default directory where all the data also is stored. And now that would change to the local directory where you don't want the data to be. So either you put one CD to the server directory into your start.prg, or you create a shortcut LNK file and use its shortcut properties tab set the "Start in" to the server directory and the EXE will not need to change in that regard, too, as the shortcut determines the working directory that can differ from the EXE location.

That shortcut (your.exe.lnk for example) can be among the files you copy and then you of course don't start your.exe but start localdirectory\your.exe.lnk to let the "start in" property do its job and create the same situation in all practical aspects as if the EXE was started where it is.

This can be refined with many things, including that what you give to the user can be a shortcut to the batch file as replacement to the shortcut to the EXE. If you have a new version the xcopy copies it, with the only newer files option this is only done once per update of the EXE.

Chriss

 

[Chris Miller]

One more thought: Your data design (normalized or not) and above all else how optimizable your data access is (no matter if xbase of SQL) by indexes you have on your tables, will be more important than the decision to buffer or lock in one or the other fashion. The faster you're done with your file access, the less you hinder other clients, of course. And these optimizations of course also reduce network traffic.

Think about one simple aspect: No matter if you have indexes or not the query result will have the same records. So does it really matter, you read the result records through the network and that's the same load, just slower, isn't it? Well, no, not at all, of course. Let's look at the best case and worst case extremes:

In the best case the index tells VFP it needs to only read in record 10 and 115 into a result. Then it only reads two RECSIZE() portions of the DBF at these recnos.

And without index support how does VFP determine these two records? It will need to read through all records. Let's say there are 1000 records, what do you assume VFP needs to read. From record 1 to 115, because we know 115 is the last record in the result, right? Of course not, VFP does not know the result only has 2 records, so it will also read records 116 to 1000. And it will discard 998 records, which it still had to read through the network.

That's the dreaded full table scan problem. Without any index you always read all records of a dbf, at least all the fields that are in the for or where condition. And that's really bad.

I don't think that people don't know indexes, they are very fond of SEEK, and that of course needs indexes, there's more to do than create the single field indexes you need to SEEK to have best usage of optimizations.

Chriss

 

[ManniB]

Thank you, Chriss. Yes, it makes a lot of sense that using Indexes and SEEK would not only speed up the query locally but also reduce network traffic. I always assumed it's working that way, therefore I make use of indexes whenever I can. What I don't fully understand is how LOCATE is also using Indexes, in tests I found out indexes also speed up not only SELECT but also LOCATE. However I try to use SEEK with an SET EXACT ON most of the time to be sure the index is being used.

The downside at the moment really is the network problem when two users access the same record which I couldn't solve yet without buffering, manual locking being the only option.

Do I have to specify CursorSetProp() for every table directly after using the table to enable buffering? Do I just have to add a TABLEUPDATE() after every REPLACE, UPDATE, INSERT etc. and in every valid event or upon closing the form? Is it that easy? Does SET REPROCESS to a high number make sense?

How can I inform the user that the value has been changed in the meantime by someone else and how can I in another situation just commit all changes in an optimistic way without telling the user? Do you maybe have a small example code for that?

Thanks,
Manni

 

[ManniB]

Regarding the exe-file, yes I have data, exe and DLLs all on the server, but DLLs (not only from VFP) have also been registered on the clients. I will experiment with a copy batch file, as I suspect, running the exe from the server through the network is slowing the whole application down. But unfortunately I have no real comparison. Do you have any experience, if it's really slowing the application down even after the exe has been loaded? In the past I assumed the whole exe is loaded once and then run locally, but this seems not to be the case when you are using a shortcut to the server's exe.