I'm new to FMS so please bear with me. I'll try to describe the problem as precisely as I can:
In our small company, we us a "home-grown" FM DB for all sales-related processes. The DB has 10 tables with approx. 100 relationships. The largest two tables have some 25K records with 25 fields and 10K records with 130 fields respectively. The remaining tables are much smaller. The total DB is 37MB in size. Each user has a copy of the DB on his laptop and synchronizes it with a central copy. This has worked very well in the past 9 years and performance has never been an issue (except for the syncing which take a couple of minutes).
However, now we would like to add a task management DB to the sales DB and this requires that the DBs are hosted on a central server, so the task management is always up to date.
So I've installed FMS 11 on our central "server" (an office PC running Win7pro32) and uploaded the sales DB. When I open the sales DB in FMP11 on the same machine, it behaves pretty much normally in terms of performance. When I open it on any of other machine on the intranet (1GB LAN), it already becomes quite noticeably slower. When I open it over the internet/WAN (ADSL) from my home office, it becomes very slow. Especially find and sort processes take a long time, even on indexed fields. Also, when I open the DB on an iPad2 in the office (54Mb WLAN) it is very slow and sort/find processes that usually take less than 5 seconds take more than 5 minutes.
Is there a way I can influence where the sorting/finding/number crunching is done? Currently, it looks like the server pushes all of the data to sort to the client, lets it do the work, and then receives the data back from the client. But I'd like to put as much load onto the server and as little as necessary on the client. Of course, a web interface would achieve exactly that, but I think it will take a lot of work to write a custom web interface for that DB and instant web publishing doesn't fly because it is a multi-window solution. So I would prefer to keep using FMP as the front end.
Do anybody have an idea how I can achieve what I want (preferably w/o a major re-write of the DB)?
I should add the expected load is pretty small, i.e. there will never be more than 5 concurrent users (typically 0-2 active users).