It's all slowing down... multi-user Grid performance, SET FILTER and other sins aplenty (VFP6) 3

[KarenLloyd]

Hi Gurus


I need some general advice on how to improve performance, if that's possible please...
I am still working in VFP6 - mostly running on PCs using XP and Windows 7

I have a networked system with 12 users, working directly in shared data files of 25000 jobs (currently), with memo text included in each job record, plus a second file of the same number of records for additional memo text and a third table of costs, with approx 150000 records. Plus extra add-ins and then look-up tables etc...

The selection screen lists are built using a general purpose grid program - to which I feed a list of fields with the formatting, validation, etc. The grid screen is the key to selecting a job or contract and it is now slowing down to the point of being unworkable at times, even showing "not responding" messages in the title bar, when attempting to search (using a filter!) and sometimes on displaying the whole file (of 250000 records)

I am now aware that I am doing more than a few things wrong - so I'll start listing my known faults as follows:

1) I have used one set grid program to handle every screen, meaning extra event handling (Valid, LostFocus, GotFocus, etc) is slowing down the processing on the read-only (selection screen list) grids.

2) I have been using SET FILTER TO - which is apparently crippling the grid process as it works through the increasing number of records. Obviously I need to convert the two main screens to work using SELECT - SQL to improve the performance of the grid, and dump the FILTERing.

3) I am allowing users to work directly in the database, record locking the main data files and therefore both of their Memo files for minutes at a time .. (I know, someone should probably shoot me at this point - I will change this to work on copy files and then update the master files on exit...)

4) The users share the EXE file from the Server, and also the FOXUSER file - Not sure how relevant that is...

+) There's bound to be lots more... this system has been expanded and {a-hem} enhanced since it originated in FoxPro 2.0 (or possibly mfoxplus). It is working well enough in companies of 5/6 users and less Memo data, but this one needs saving and quickly. It's not a small task. So aside from changing over to SQL selects I have a few other things I need more info on please if you can advise me in any way...?

A) The performance on some machines seems to have deteriorated since they've been upgraded. This may be a coincidence and just the growth of the data files. If a PC has 1.99GB+ of RAM, should I be using SYS(3050) to control the memory that VFP6 takes so it doesn't "over-grab" the memory? (Help!?) The other apps they are running alongside include Outlook, Excel, Internet, Google Maps, TomTom WEBFLEET, etc, all of which apparently perform as they should.

I have not paid any attention to config files for a long time, so mine are probably just ignored now. Is there anything vital I might have missed?

B) Should I make each user have the EXE, FoxUser files and DLLs on their C: drive? Would it help instead of sharing from the Server?

C) I am getting more concerned that I should really upgrade to VFP9. Can my applications be distributed in the same way as VFP6, or does it have to be installed and registered on every workstation? Are there any licencing changes or extra financial implications?

+) There is more to come - development for Tablet applications is being requested and I'm now starting to feel out of my depth.... (There - I've said it. Deep breath! :} ) I apologise for the stressed end of week rambling...


After pouring out that lot - I would just be grateful for any pointers, suggestions, criticism, anything from your wealth of knowledge and experience. Is there anything else obvious that I should also be considering - besides therapy! <grin>

If not, thanks for taking the time to read it anyway.

Kind regards

Karen

 

[Nigel Gomm]

the easy things first.

yes, move all ,exe .fll .app .dll and ocx onto the local drive. make sure your temp folder is on a local drive. and foxuser.dbf

if it's a large program (> 9mb) look at PROGCACHE in the help file.

this at startup
SYS(3050, 1, MIN(512*1024*1024, VAL(SYS(3050, 1, 0))))
SYS(3050, 2, MIN(512*1024*1024, VAL(SYS(3050, 1, 0))))

if win7 or winserver2008 you may want to disable SMB2 and oplocks.


there are quite a few previous threads here that may be helpful.
e.g.

http://www.tek-tips.com/viewthread.cfm?qid=1684141

http://www.tek-tips.com/viewthread.cfm?qid=1691018

http://www.tek-tips.com/viewthread.cfm?qid=1684035

hth

n

 

[Nigel Gomm]

if it suddenly got worse when PCs where upgraded then SMB2 and oplocks may well be a big part of the problem. see

http://fox.wikis.com/wc.dll?Wiki~OpportunisticLocking

my next thing to fix would be set filter on a grid. Instead select into a cursor and make that the recordsource.

n

 

[GriffMG]

Nigel is right in the first instance you should try and get the app and runtimes stored locally.

The SMB2 problem is also bang on - very likely the main problem, if you can try testing with the data on a local drive.

It's a pain removing op locking from the newest M$ servers, so you would probably find you're better off with a cheap NAS
solution for the file storage - they are typically way faster than windows servers, and cheap as chips!

If you're going to stop sharing the app from the server, you have to devise a way of making sure the workstations have the
most recent version of the app... and Windows 7 doesn't make this any easier for you I'm afraid.



Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

 

[TamarGranor]

You don't need to switch to SQL SELECT to solve your locking problems. What you need to do is use optimistic buffering, so that nothing gets locked until you try to save (using TableUpdate()).

That said, for non-editing forms (like look-ups), using SQL SELECT is a very reasonable approach.

Updating to VFP 9 definitely offers some benefits (including lots of developer improvements). Most relevant to your situation is the new Optimize property of the grid, that lets you tell a grid to use Rushmore.

I'm not exactly sure what your distribution concern is, but the only difference distribution-wise between VFP 9 and VFP 6 is that VFP 9 doesn't include the Setup Wizard. It does (IIRC) have a cut-down version of InstallShield. Personally, when I have to do an installation, I use (the free) InnoSetup.

Tamar

 

[Auguy]

Excuse me for jumping in here.
Just a quick question and I apologize for my lack of knowledge.
Griff or anyone, Can I assume from your comments that SMB does not affect a NAS?

Auguy
Sylvania/Toledo Ohio

 

[GriffMG]

SMB might have some effect, but most NAS boxes, which are often Linux based, have a simple on/off setting for op locking, making a resolution
really quick.

For some bizarre reason M$ has always made turning off op locking a bit of a 'dark art' involving registry changes on
both the workstation and the server. WIth Windows Server 2008 onwards, they have set the op locking almost in stone
and you have to download a fix to rid yourself of it - it's buried in the SMB2 and you have to dig it out.

I know the original reason for op locking, it was to help M$ compete with Netware, essentially their servers were much slower when it came
to tests (when networks cost a lot of money) so, in order to make their offering look good M$ figured 'hmmm... when people are benchmarking
they only tend to use one server and on workstation - how about we reduce the network traffic but passing the processing back to the workstation...'

They should have dumped it years ago.

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

 

[Mike Lewis]

For some bizarre reason M$ has always made turning off op locking a bit of a 'dark art' involving registry changes on both the workstation and the server. WIth Windows Server 2008 onwards, they have set the op locking almost in stone
and you have to download a fix to rid yourself of it - it's buried in the SMB2 and you have to dig it out.

I'm not sure if this is relevant, but this article mentions a "user-friendly" way of dealing with op locks:
Trouble-shooting a Visual FoxPro application.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[GriffMG]

That is a very useful article Mike.



Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

 

[Olaf Doschke]

@Karen

You identified your problems yourself already, as Nigel said, easy things first.

I don't have to add much right now, just the general recommendation to check your indexing and measure times. SET COVERAGE enables you to find bottlenecks and long running queries or locates, but it's really not a thing to call "easy" and is not a first step. Query optimisation can be verified with SYS(3054), again that is an advanced step to analyse a single query making your app slow. Everything you do on the clientside and from within memory, variables, arrays or cursors is fast and doesn't need much optimisation and analysis, everything network related is your bottleneck.

About SMB Oplocks:

This is very specific, even less "easy" and important right now to you, Karen. But there is a point I want to make against the bad reputation of it: Actually the concept of reducing network traffic is a good one, and improves the usability of LAN and WAN shared data, but it's rather only implemented in a way handling whole files (like office docs) and helps in situations where reads of these file occur more often than writes.

And that's also true for data, but not on the file level. The single records are much more often read than inserted (which of course occurs just once) and updated, how many times depends on the app, but mainly you update data more seldom than you read it, eg product prices may change weekly or even daily, but are read even more often by customers, even just by the multiplicator of customers.

The problem of oplocks really just is, that the mechanism of breaking oplocks is not very optimal especially in regard of how many times it is needed for DBFs accessed by multiple users. To be a benefit to us, oplocks would need to be established on the level of single records inside DBFs, and that's something the OS won't ever offer, even though VFP and Windows are both from Microsoft. But where should that end? There are many files having an internal structure, also MDBs, which have even worse problems than DBFs, as they contain a whole database and also maybe even the whole Access application and indeed have a substructure inside.

FoxPro has had it's own solution to the network traffic problem, it's called buffering. I think it already has been around before oplocks where ever introduced on the network protocol. The mechanism of VFP buffers also delays network traffic in a similar same way. In optimisitc buffer mode, foxpro simply writes changes to the buffer at client side without letting the server know it caches data. It's done in the optimistic expectation nobody else will also change the same records, but even that won't hurt, it will be detected and you can decide to overwrite intermediate changes in the end or inform a user, somebody else also changed the same record(s).

The advantage of optimistic buffering is, the server doesn't need to know and maintain an oplock, all other clients are allowed to write even in the records the client has changed in it's buffer without any lock breaks and negotioans and acknowledgements all needing network traffic. You control the buffer on the client side, not on the server side. And you determine, when you submit, not another client.

In pessimisitic mode, you not only buffer your changes, you also lcok a record in the server side file and the server maintains that. Unlike oplocks, this is a real lock disabling other clients. This needs communication from the first client making changes to the server on a per record basis, but also lowers network traffic until the point, where you commit your buffer and unlock records.

So oplocks are somewhat inbetween the optimistic and pessimistic buffering/locking mechanims of VFP, in that they save bandwidth but let the server decide by request of further users, to let a client commit his buffer.

From that perspective we already have the better solution to the VFP world and DBFs and it's no loss to turn off SMB oplocks for VFP. The bad thing is, this is a system wide setting and not done per file handle. A newer mechanism of leases instead of oplocks will perhaps be better, especially for applications profiting of oplocks already. You would still also not use leases from VFP, as buffering already is our solution.

After this comparison, this still doesn't mean oplocks are a locking mechanism, it's rather a caching mechanism like buffering and unlike pessimistic locks never locks any other user out of reading or writing to files, it just delays any network traffic as far as you can and that hurts file access as it's done to database files because of it's file level approach and the broken oplock break mechanism.

Bye, Olaf.

 

[KarenLloyd]

Hi

Thank you for your replies and the links to additional information. There are a few points from reading all this that I need to chase up on before I start recoding.

1) I will set the loader to copy the DLLs, FoxUser files & latest EXE to the local drive, into the temporary folder. The largest EXE file is only 1.3MB

2) I had a conversation with someone from the IT company recently, who agreed to disable virus scanning of the system folder and also restart the server on a weekly basis. They may not have done so yet. I will check that the DBF, CDX, FPTs etc are not being scanned, though I expect the EXE files on the server still should be.

3) I didn't consider the timing of the AV scans running on each workstation. As each user becomes aware of a sudden change of speed, they now ask each other what is going on.

Someone mentioned that the situation can be worse first thing in the morning, this is usually the second user to log in. Then again the problem can occur any time of day - it is not consistently slow.

Sometimes a user will try to look something up for a customer on the phone (or it could be the boss looking over their shoulder !), only for the screen to pause for 30 seconds or even a minute or two before giving them the results. It’s very frustrating.

From reading the information from

http://fox.wikis.com/wc.dll?Wiki~OpportunisticLocking

and

http://www.hexcentral.com/articles/troubleshoot.htm

- I need to find out more about the server / network settings. I wasn't aware of SMB or Op Locks before. The customer is using Small Business Server, that's about all I know.

I will contact the IT company on Monday and run through some of this with them before I get to work on cleaning up my code.

Thank you Nigel, Griff, Mike and Tamar for your advice. I will come back and let you know the results.

Kind regards

Karen

 

[KarenLloyd]

Hi Olaf

I have just seen your response as I posted mine.

The situation with this particular part of the system is that the users do go back into records that were created days or weeks before and they have to add more information into them, such as dates, memo text, costs etc.

I have been keeping a record locked in the main DBF on the Server while the user adds this information in. I was considering copying the record set to a local temporary file for editing and then applying the updates on exiting. This may not be necessary, if as you (and Tamar) have suggested I change over to optimistic buffering while I make the next set of changes.

I'd better dust off the VFP user guides. There is so much for me to learn...

Thank you.

Kind regards

Karen

 

[KarenLloyd]

Hi, Griff

Please can I just ask what you mean here...?

If you're going to stop sharing the app from the server, you have to devise a way of making sure the workstations have the most recent version of the app... and Windows 7 doesn't make this any easier for you I'm afraid.

I have it set up so that the system starts from a Loader EXE, which checks the latest version of the Live EXE and then calls it as follows:

** _mSysVersion will be stored as '"JOBSYSyyyymmdd.EXE"'
_mrunver = 'RUN /N '+_mSysVersion
&_mrunver

I am changing this to work as follows:

IF !FILE("C:\TEMPVFP\JOBSYS.EXE") OR FDATE(_mSysVersion) # FDATE("C:\TEMPVFP\JOBSYS.EXE")
	COPY FILE &_mSysVersion TO C:\TEMPVFP\JOBSYS.EXE
ENDIF
_mrunver = 'RUN /N "C:\TEMPVFP\JobSys.EXE"'
&_mrunver

NB The loader also needs to check for the DLLs and FoxUser files and copy those into the temp folder.

FILE() is used throughout some of my programs as some additional functions are disabled if the files are not present.
The system does not have a path string set as everything is stored in one folder.

I saw your comments on this thread the other day thread184-1684035

Is there a problem with use of FILE() or FDATE(), with or without the full path in the file name? The Server is Small Business Server 2003, the workstations a range of XP and Windows 7.

Thanks

Karen

(Note to self... disable the constant checking of FILE() within program loops, as it surely adds to the delay! :{)

 

[GriffMG]

I have my executable automatically check for newer versions of themselves when they load, by comparing
file times and dates with a shared folder.

The only snag is that in Win 7, when the program tries to fetch the update, the UAC tries to elevate the updater to
adminstrator and when this happens the network drives vanish - or change - so that the update is not there anymore.

So what I do now, is copy the update to a local temp folder, then copy that file over my original executable.

File() isn't normally going to slow you down, the problem - for me - is that it tends to ignore your specified
path in the first instance - and uses your current folder, then (I think) whatever you have as default. This means
it sometimes gives false positives - i.e. you say File("c:\tempfolder\myexe.exe") and it says .t. because it found
myexe.exe in the default path, even though it isn't in c:\tempfolder!

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

 

[Mike Lewis]

File() isn't normally going to slow you down, the problem - for me - is that it tends to ignore your specified
path in the first instance - and uses your current folder, then (I think) whatever you have as default. This means
it sometimes gives false positives

We've had this discussion before (including in the thread that Karen linked to).

But, in this case at least, surely there won't be a problem, as Karen is hard-coding the path ("C:\TEMPVFP\JOBSYS.EXE"). The use of the FILE() function should be fine here, don't you agree?

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[GriffMG]

In all probability you are right, I just remember spending ages trying to debug a problem
where I couldn't see a file and File() could... so I developed a bit of OCD and stick with
my own home grown MyFile() instead!

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

 

[KarenLloyd]

OK - thanks Griff.

I don't think it will be a problem as the loader runs in the folder on the Server
and then copies to a hard-coded file name, as Mike said.

Thank you both.

Karen

 

[KarenLloyd]

Hello again.

I know its been over a month but I thought I would just add final comments in case anyone comes across this thread later on. (However unlikely...)

The advice that I received regarding server issues such as OpLocks and Server Message Blocking was eyeopening, and I have passed this on to the IT manager. However, sticking to what I could do from a developer viewpoint, I handled these problems by making the following changes.

1) The application now runs from the C: drive for each user, using a loader from the server to alway check and load the latest version of the system on start up. (See the code example on RunVer above). The FoxUser files and DLLs are also copied / stored locally, in a temp/startup folder and the default directory is then set for accessing the data from the server.

2) The main selection screen lists now run from a readonly grid that displays data from a SQL SELECT statement. This is much quicker than displaying FILTERed data in a modifiable grid on a key database.

3) The updating of job details is now carried out in a local temp file (named by jobno so changes can be recovered in the event of system crash or abnormal shutdown). The changes are written back to the central DBF on exiting the update screen.

4) I used a separate file to create & maintain a user lock record on each job, with the option to manually unlock a job after showing the date, time and user name. The "lock" file is zapped on Reindexing the system


As the master jobs file and memo text were constantly being locked, viewed and edited - these changes have since shown a great improvement in performance.


Having spoken to the IT company - they have also disabled server and user scanning of the data files, and say that they have already addressed the issues of OpLocks and SMB (though I cannot be sure).

I have developed a general purpose grid class over a number of years and it had so much functionality in it that I had to chase through the event processing to assess the speed. I created a second version for readonly grids and removed all the superfluous event processing just in case.

Lots of little(ish) steps really helped to improved the performance, without having to do a total rewrite at this time...


Thank you all so much for your help.

All the best

Karen

 

[Mike Lewis]

Glad to hear you're making good progress, Karen. It's often the case that a whole series of small steps gives a better result than one major change.

Thanks for sharing these results with us. I'm sure other people will find them useful.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy