I have a 50 file solution with 150 users running FMSA v10.
The server setup is:
2 x 3 ghz Quad-Core Intel Xeon, Xserve, with 4gb RAM.
RAID level 5 using Apple's RAID card
Mac OS 10.5.8
In general everything runs smooth, I see occasional spikes in the CPU usage across all 8 cores, but they don't last for more than a few seconds.
However, when particular reports are run that require a lot of unstored calculations, I see the CPU usage stay past 100% for many minutes until the reports are complete. This in turn slows everyone down because they are waiting for responce's from the server. The reports are looking at sales results across a few hundred records over a varied time period, then doing a lot of math to come up with CPM's and weighted ratio's. Basically the reports cannot be stored because a person needs the flexibility to pull results over a varied date range.
All the records needed for the report live in multiple tables, but in the same FM file.
So, while I need to take a second look into the logic of the reports to try and tweak them to run quicker, I was wondering if it would be possible to have a 2nd Server instance, and put the fm file that houses the report on that server. Hopefully alleviating the workload and wait time for everyone else while the reports are run.
This spawned several questions:
Has anyone done this before?
Would moving the file, (which is also used by many people who do not run the report), have an adverse effect on their performance and access?
Do I just need to change the External Data source (file reference) to that file to point to its existence on the new server?
I assume that having External Data sources on different servers has some performance cost, but is there anyway to tell how much it will be?