We help IT Professionals succeed at work.

Performance problems with FoxPro 9.0 database

Comsyco
Comsyco asked
on
Our client is using Pegasus Opera (which uses FoxPro 9.0) and is having some pretty serious performance issues. The software can take at least a minute to load the software and using the software can be painfully slow for them.
 
We have done some pretty drastic steps to try and help the situation but as of yet nothing seems to be making any difference.
 
We have upgraded the server to
2008 SBS x64
 Intel Xeon 2.27Ghz x 2
 8GB Ram
 Boot Drive - 467GB - Raid 1
 Data Drive - 931GB - Raid 1
 
The network was upgraded to run at 1000mb.

We have also looked into a known issue with SMB2 on 2008 servers and have followed MS tech notes to disable SMB2 in the registry.
 
My question is are there any known issues with FoxPro databases (or Opera if anyone has experience with that) or tips / tricks on improving performance for FoxPro databases in general that may be effective with our setup?
 
Thank you in advance for your help with this.
Comment
Watch Question

Commented:
How an application operates can be based on a very wide variety of things.

Lets start with a few facts about Pegasus Opera.  
Based on some web searching I see that it is an Accounting Application which could have been developed to effectively use the data sources or not.  We cannot evaluate that.

And, again based on web search results, it appears that  Pegasus Opera II uses Microsoft SQL 2000 Database as its data 'backend' - NOT "FoxPro" databases.
Unless you are using an older version of Pegasus Opera where I am not sure what data 'backend' it might use.

And, based on your comments above, you have already made the SMB 'patches' to resolve those issues with Win 2008 R2

Also while the Pegasus Opera 'backend' database 'lives' on the Win2008 Server, the application itself should be running in the individual workstations.
So what are the workstation OS's and their workstation resources?

Now I want to ask, did this application run well at one time and now is not running as well?
Or is this a new installation having its performance being evaluated right-out-of-the-box?

And have you contacted the Pegasus company ( www.pegasus.co.uk/ ) for support to improve performance?

With more clarity we might be able to provide you with better advice.

Good Luck






Author

Commented:
Hi jrbbldr,

The clients workstations are varied between brand new Windows 7 machines with a min of 2GB of RAM and slightly older Windows XP machines running between 512mb - 2GB of RAM. We have spent many hours debating the problems with Pegasus and Pegasus resellers who support the software and each time they blame an aspect of the network or other software on the network only for us to eliminate that as been a problem by removing or replacing the accused. They are also the ones who told me that the software version they are using is using a FoxPro 9 database backend they are unable to upgrade to the SQL version of the software an addon package that the client uses does not support the SQL version of pegasus.

The software has been very slow for so long now that the client does not remember it ever been quicker however I suspect that this has been a steady decline to get to where they are now.

I understand this is almost imposible to diagnose and was hoping maybe someone else out there had experienced similar issues with Pegasus / FoxPro.

Another thing I forgot to mention was that we have swapped network swtiches, cabling infrastructure, setup a direct link between the server and random workstations so nothing else on the network could effect the connection, removed anti virus from server and workstation all to no noticable performance increase.
CaptainCyrilFounder, Software Engineer, Data Scientist

Commented:
Usually there are two things that slow down a FoxPro database:

1) Anti-Virus software which you already disabled and found that it was not the problem.
2) Corrupt index somewhere which when you reindex the database it should sort it out.

I would open the Task Manager on the server to see what is going crazy when you try to use the software. That might tell you something.

Commented:
Do you see any difference in performance between the differing types of workstations?

I can tell you that Visual Foxpro applications want to have as much memory available to it as it can get and its workstation-specific performance will most notably reflect that.  

I'd guess that the Windows XP machines running 512mb would perform noticeably slower than the others.

Also are the users running a LOT of other applications at the same time that they are trying to use the Pegasus Opera?  
If so, those other apps would be consuming workstation resources that a VFP application would very much appreciate having.

If you can get things working though educated guesses by other gurus here, that would be great.
Also through a Google search for   "pegasus opera" forum   I see some other possible support resources that you might look into.

But if those avenues did not provide you with a resolution you could, as a last resort, consider hiring a VFP consultant/contractor to create a relatively small stand-alone VFP9 data access test program to 'hammer the data tables' and see if the problem is in network and/or data table issues.  

If the data table 'exercise' test showed everything was OK, then the problem would reside elsewhere - possibly within the application data utilization methodologies, etc.

Good Luck


CaptainCyrilFounder, Software Engineer, Data Scientist

Commented:
You need to turn the anti-virus off or disable it from the server and the workstation.

If it is faster without it, then you need to exclude the FoxPro database files or the folder where they reside from scanning.

Author

Commented:
The server has 8GB and 2 x 8core CPUs. None of which are majorly taxed at any point.

We do not see any major difference in perfomance in the software between the workstations unfortuantly.

I have already tried other support resources and didn't get much back from them. How ever teh idea about hammering the data tables seems worth passing along to pegasus / reseller to see what their thoughts on that idea are.

Thank you for your help.
CaptainCyrilFounder, Software Engineer, Data Scientist
Commented:
FoxPro is a 32-bit application. It does not see more than 3 or 4 GB of RAM and it does not handle a table more than 1 GB.

The server if it has SQL database, can make use of the additional RAM.
Software Developer
CERTIFIED EXPERT
Commented:
What's more important of the foxpro database is, it's not client/server, there is no server process like with SQL Server. So all the cpu power is worthless for a foxpro database hosting. The only things that matter are network bandwidth and secondary hdd speed.

If performance has degraded over time, it's just a sign of not ideal indexing conditions, indexes defined on foxpro databases are the core thing making them fast or slow. You'd detect that by the high network traffic related to unoptimisabel data retrieval. In worst case situations foxpro would read whole tables unoptimised, which of course degrades over time, if the data grows.

As you can't change the applicaiton itself and cannot optimise the data retrieval through guessing what indexes may help, the only factor is LAN bandwidth and hdd speed.

Raid 1 as you have, highers availability/security but does not speed up writes. Raid 10 can help make writes faster.

File caching is the only other thing you could try to play with on the server side. If the whole database is smaller than 8GB and no memory intensive other services or tasks run on that server, it can indeed make use of that whole RAM memory, despite of foxpro applications not being 64 bit applications, the file caching of the server is solely an OS thing. Once the server is up long enough and most data is cached in it's memory the hdd speed would be secondary, it would only matter in how fast changes to the file cache in RAM get reflected on hdd.

The thing with file cahcing is. by default Windows does not make such extreme use of RAM/caching, because of the security risc of loosing data of course, so you'll need to tweak that and take the risc of eg power outages. You can compensate that risc with UPS (uninterrupted power supply).

Bye, Olaf.

Author

Commented:
Thanks everyone will pass your comments on and hope we can come up with a solution.