0

I'm currently running a VFP9 application that uses a database which is basically a folder of multiple DBF, FPT and CDX files.

Now this database is basically shared on the network (1GB network/switch/PCs) for over 50 users to access.

Now at times we are getting an "attempting to lock" message. I've inspected the hardware with no issues or bottlenecks found. Also at times over 50 people could be accessing or even writing to a specific file within the space of a few seconds.

I've come across potential issues with "Oplocks" in the Lanman area via Google but this doesn't seem to exist in Windows 2008 R2 / Windows 7. Also been reading up on SMB in general but I am at my witts end.

I don't know what to look at next? Maybe I am testing the limits of what a VFP/Dbase system can do?

Could anyone help?

Thanks Ryan

Ryan
  • 3
  • 1
  • 6

2 Answers2

0

Could be lots of things, and could be perfectly normal Have you source code for the app ?

" .. over 50 people could be accessing or even writing to a specific file within the space of a few seconds .." means no surprise to me you're getting locking conflicts.

The Attempting to lock message just means more than one person trying to access the same record (or file if crude locking).

Have the number of users increased lately ?
Was the system designed for this volume ? Is it affecting one particular feature of the software (most likely should be), or is it all over the system in several features ?

The technical limits for VFP are (I think 2 GB per single table) and practically it gets slow when there are millions of records (huge generalisation). Good indexing will help when tables get large

Is this an intermittent blip on the system (Eg at Lunchtime and evening clock-out times) or is it a continuous or more frequent load on the system ?

If you have source code you can do a lot, if you haven't you need to figure out which DBFs are being locked (they'll be the ones with lots of records in them and probably big file sizes too, when you look at the directory)

Do you have VFP itself or is this a compiled EXE ?

You've lots of options and things you can do, but more info needed.

Nick

Tin Bum
  • 1,397
  • 1
  • 8
  • 16
  • Well in this instance we have an "audit" file. So no matter what area of the system someone accesses then whatever action is recorded is then recorded into the audit dbf/fpt. We have a system in place so the audit file can become huge (500mb) but it then gets auto archived at the start of each month to stop it from getting ridiculous. the VFP program itself is a compiled EXE and is always accessed locally no matter the machine, it is just the database location itself which is usually access over the network (unless certain users are RDP'd into the server machine) – Ryan Jul 17 '13 at 08:19
  • Then I think maybe clear the Audit file more frequently than monthly. – Tin Bum Jul 17 '13 at 09:39
  • Use VFP.EXE to look at the Audit File before and after Auto Archiving, does the Archive process clear it out entirely or partially – Tin Bum Jul 17 '13 at 09:41
  • Reason to view the file with VFP - Just in case your existing archiving routine prevents running more frequently than monthly (Eg by a limited file naming convention built in to it) by viewing the audit trail file before and after archiving you build up a picture of what it is doing, how it decides how many records to erase etc, so if you have to re-write a new archive routine (without file naming limitation) you'll be better informed. – Tin Bum Jul 17 '13 at 10:02
  • Thanks for your input so far. The archiving does have a monthly naming convention built in. The archiving process itself will see any records dated from the prior month and simply cut them out and put them into a brand new audit file that is read only for administrator access only. I suspect you are right with the file size being an issue, though as the user has not come back to me about the problem again, I wonder if smaller files will alleviate the problem but not cure it in the grand scheme of things. Hmm – Ryan Jul 17 '13 at 12:21
  • I think you are very likely on the right track with the audit file. If every aspect of the software is writing to it then it is by far the busiest file on your system, and as you said, it is also one of the largest. When adding records (which must be happening a lot with the Audit file) VFP will take longer as the file gets larger - in my experience (and logically). – Tin Bum Jul 18 '13 at 13:27
  • Apologies I keep pressing enter for a new line ... and posting unfinished stuff – Tin Bum Jul 18 '13 at 13:28
  • In normal operation when the file is small the process of Lock Header, Add Record, Write Data, Unlock happens quickly - millisecs, when the file gets larger it takes longer, eventually long enough for the message say "Attempting to Lock" to appear. Those sort of VFP messages take quite a few millisecs to construct & display, surprisingly long - much longer than the lock ... unlock on a small file – Tin Bum Jul 18 '13 at 13:52
  • Thanks for the advice, Nick. I think we will have to go down the route of allowing an archive to take place on a weekly basis instead of monthly, or maybe even set the system to operate on the basis of the user having free access to how often they want to archive this file. Thanks for your help and advise on this! Will update with any news after the changes are implemented. (ps. I found I have to use SHIFT+ENTER to go to a new line when typing in the comments box but it still formats it without line breaks) – Ryan Jul 18 '13 at 16:28
0

For the application slowness, one thing I know can be an issue, especially if you have 50 users pounding on a VFP app. Is the application being run from the SERVER... I mean, does every user have their shortcut pointing to something like

S:\SomeShare\YourVFPApp.exe

If that is the case, then that can SIGNIFICANTLY kill performance. It is pulling the application down over the network for every user sucking up traffic. What I have done with clients is the following. Pick a spot on the local C: drive of the machine... ex: C:\NetworkApps and copy the YourVFPApp.exe into this C:\NetworkApps folder.

Next, create a new shortcut pointing to the C:\NetworkApps\YourVFPApp.exe and save it.

Next, modify the shortcut but this time, change the "Start In" folder to the original location such as "S:\SomeShare\". Save changes and run THIS version of the shortcut.

What this is basically doing is running the app LOCALLY, but by starting in the same final location that is being shared (especially if hard-code paths were implemented and horrendous to deal with). What this does is prevents the need of all users getting the applications over the network and just dealing with the actual tables and data traffic.

Yes, this can be a bit of a pain when there are application updates, but for that, I've written another VFP simple app that looks to the local drive, compares the exe to that of the "latest version" as on the network share. If the server version is newer, copy that locally, THEN launch it starting in the expected "S:\SomeShare\" folder.

As for locking, if you are doing TABLE locking vs RECORD locking, you'll obviously see more delay issues of waiting for lock messages, but clearing possible network bottle necks on the application end might help alleviate it for you.

DRapp
  • 47,638
  • 12
  • 72
  • 142
  • Hi Yes the EXE is always accessed locally, it is just the database files which are usually accessed over a network share (unless some users have RDP access directly on to the server first so everything is still local) – Ryan Jul 17 '13 at 08:24
  • We run it as a locked record and not a locked table also. As the person mentioned above, maybe it is partly due to the file size (200mb increasing time it takes to read and write to the table) or maybe it is due to the 50+ users (and incrwasing) potentially accessing and writing to the table at any one time. – Ryan Jul 17 '13 at 12:52