I'm afraid I didn't make myself clear. Only the complex statistical analysis and data processing programs would bring the data locally NOT the day to day data edit and displays.

We use a website that directly updates the data (PHP) while our data imports use VFP directly on the data. Our fancy statistical analysis programs typically pull all the data locally, index it and then start join and grouping.

A complex "group by" across our transaction data on the server can take hours. During this time we can (depending on the exact sequence of events) lock out the entire user group. They will just crash. Optimizing the server might improve the results but remember your server is being used by multiple processes.

Originally we had the bulk of our complex query programs server side -- create temporary table ... blah blah . However VFP is so damn fast that it is faster (2-10 times faster) for us to bring 8.000.000 transactions and 2 million customers over a tunnel to our local computer, index the tables and make a group by than let it run on the server!

My advice: don't run complex group by and joins on the server unless you knwo what you are getting into.


>>Bringing all the data locally is my advice if possible:
>
>That cannot be done and absolutely an application killer as the server has to serve the data to many users and such approach will cause a heavy burden.
>The network will be flooded too and a wireless network would become absolutely out of question.
>

>>Problematic might still be statistical programs that use complex joins and group bys. Optimization of selects between the different databases can be very different. A minor change in a select can easily bring a tenfold increase in speed.
>
>I agree on that, so it really should not matter if we get locked into a database. The performance and advanced querries and later on the analysis has to be database dependent.