Our database is very sluggish. Admittedly it is complex, and sums related records that are summing related records, and the relationships sort on unstored variables (often involving related data so canNOT be stored). We require this level of complexity, and i've tried storing the data and referencing the stored fields, but then it is not realtime, so that's not great either.
We are using 100Mbit ethernet and switches, MacPro dual 2.6 duo, 2GB ram 3Gb/s SATA drive FMS9.
I have a small budget because of rough times and we are non profit. I am getting different advice to improve performance. One expert says to never use SATA and replace the hard drive with SAS (which means buying also a raid card). Apple says it's not the hard drive, that i should buy FMS10 (does that sort the unstored relationships faster?).
1) FMS9 says i am using 480 MB cache. How much RAM would I have to put in the machine to be able to cache 1GB? Then hard drive speed doesn't matter.
2) If I can't do all the options, would my money be better spent on A) SAS hard drive + raid card B) FMS10 C) Faster processor single duo IMac with 2Gb and external firewire drive? (that way I can buy the newest Imac in a year and just move the drive? I can't just keep buying the newest MacPro) C) Upgrade MacPro motherboard to faster processor. I assume this option would be the least bang for the buck. D) use windows or OSX-Server or multiple hard drives E) use ODBC or something to use FMP as a front-end for a SQL back-end
I would be buying FMS10 eventually either way so please don't advise based on sales but I would like to improve performance right now because when someone goes to a record, they have to wait 20 seconds while there is "sort" screen because it is calculating and sorting through the relationships. Repeat, there is no sort on the found set, the sort must occurring through the relationships.
Please advise because people are always complaining about how long it takes to load records. Especially over the internet. I kindof suspect the internet ones can't be helped that much. I tried IWP but it did not work on complex layouts so it didn't seem viable.
Thanks in advance