Server speed problem

[huntert]

I have two copies of the same Data Base. One local and one on a server. I make calls to the local or server database via Select sql calls to load the correct info into my forms and update the appropriate database. This all works well even at distant site.
Problem is a speed hit when two users are accessing/connected to the server database. Calls reduce to a crawl significantly slower than can be justified. There is a form which each users may have a list field with 3700 record called via this way. When a user clicks on a name and simple call for a few field from that record takes about 30-60 seconds when it normally takes 2 seconds when a single user is signed on. Exclusive is OFF and Multilocks ON. Any help to figure out what is happening? WOuld using the NODATA parameter make any difference? thanks hunter

 

[AndyKr]

This normally occurs when you are using grids that have filters in Versions of VFP prior to VFP 8.0. YOu don't really give much information so it is hard to ttell what else may be going on, but I have applications with 200+ concurrent users that do not show this type of behavior.

----
Andy Kramek
Visual FoxPro MVP

 

[huntert]

I am using Visual FoxPro 8 with XP operating system. I am not using a grid field.

 

[opticalman]

What is the backend?

and I'm not sure what you mean by:

" When a user clicks on a name and simple call for a few field from that record takes about 30-60 seconds "

Was that suppose to be "a few fields" or "a new field" ??

I have had a problem where adding one new field to a remote view caused all the records in the view to be committed one at a time, causing long delays.

Jim Rumbaugh

 

[huntert]

That was meant to be "a few fields" called from one record. I am not adding any new fields to the database.

The database on the server is the same foxpro database locally, it has been given another name. It is used by multiple users and record created on a local database when disconnected are then uploaded. All the calls remain the same except the database handle is changed.

Questions
1)Could this be an issue of the cursor for the patient list stored in a listbox control for each user somehow needs to coordinate info on an ongoing basis and the program slows down as a result? even when only one simple call for a few fields in one record is occuring.
2) could this be a buffering or memory issue of the local machines ram trying to manage the multiuser environment added demands?
3) should I use SQL upsizing wizard to create and run the server based info rather than my current use of a duplicate database be placed on the server and being used as in am?
... I am struggling to figure this out.
thanks

 

[Olaf Doschke]

The performance hit with a second user is likely to be caused by the window feature of opportunistic locks.

It works as some kind of cache. If only a single user has a handle on a file the server will grant the client quasi exclusive access and the client will cache all changes instead of writing back to the server to minimize network traffic. The cache will even persist if the client closes the file.

Now if a second user wants to access the file the server must query the first client to submit all changes in it's cache. This is really costing time. The initial benefit for the first user is now causing a performance hit for both user 1 and 2. That's why it's a bad idea to have oportunisitc locking on for a file based database, but that's the windows default.

The second bad thing about it is, if the client fails, that holds it's file changes locally, those changes are lost, the server not seeing a client for a certain timeout will assume the client failed and the file remains at it's initial state.

You can set a registry key on the server (that is in case of a dbc/dbf the file server, or other workstation where the data is in a network share) to deny the granting of opportunisitc locks.

http://support.microsoft.com/kb/296264/en-us

You can also deny the granting of opportunistic locks by setting the following registry entry to 0:
HKEY_LOCAL_MACHINE/SYSTEM/CurrentControlSet/Services/LanmanServer/Parameters
EnableOplocks REG_DWORD 0 or 1
Default: 1 (enabled)
Note The EnableOplocks entry configures Windows-based servers to allow or to deny opportunistic locks on local files. These servers include workstations that share files.

If the registry entry does not exist, add it.

This will affect all apps and disable the feature of offline files.

The first registry entry mentioned in the article is for each client and sets them to not request opportunistic locks in the first place. That is even better, but would need to be set on each client. A single client requesting oplocks and the server granting them would again lead to such a situation if the user 1 is using that client.

You will solve the long waiting times of the second user, but that you have such a great performance hit indicates you're either pulling way too much data to the clients, or the first user has accumulated very many file changes in his local write cache. If it takes up that much time to break the oplock and change the server file accordingly, so the server can grant access to a second user, that explains it.

By the way, don't confuse this with the caching/buffering mechanisms of vfp. It's opportunistic locks, not optimistic locking of vfp.

Bye, Olaf.