Data Separation/Server Speed Advice
I've been developing a solution for several months. It is my first FM experience.
The solution is hosted on FMS11 in our main office. We have several locations, so some users are on LAN and others on WAN. Main location's highest available speed is 5mb down 1 mb up. Or branches have the same upload with 2-4 mb down.
As the solution grows more powerfull, performance is starting to really become a problem. There are a lot of scripts that control business logic and automate many complex tasks that use to take a person hours to manualy calculate.
I need advice on two things:
1) Server Speed. How much can the Server internet speed be affecting my performance? There is no faster internet where we are located. My alternative here would be to get a cloud server or host with one of the companies that host FM solutions. Our solution has more than 75 tables, more than 10 tables have over 50,000 records. 1 table has close to a million records and a couple more have half a million records. Agregate calculations and unstored calculations seem to take quite some time, i.e. I have one table with 1,506 records with the fields Part, Week, Location and another table that has the same fields plus the date and quantity which rings it in at about 13,000 records. It concentrates the qty by week on the first table, than calculates the percentage of sales from that part relating the week and location to a sales by location by date table. I have opted more for using replace field instead of unstored calcs, becasue unstored calcs create a lot of lag when scrolling. The replace field (four fields) takes about two minutes. Seems pretty slow to me. I've been using SQL as well, and it seems SQL could to that same operation in less then 10 seconds.
2) Data Separation. Even if I host my solution on a server with blazing internet speeds, I still have really slow speeds at our branches, so I've decided to create an interface file to be stored locally on client computers. This way the only thing travelling is raw data, not layouts and hundreds of script steps.
2a) My first issue is that right now I have people on LAN and WAN. I develop in our main office so I'm on LAN and the IP for the main solution is an internal one. But when at our branches, it is a public IP address. Is there a way to change a FM File's location in one step, or do I manually have to change this source in each Table Occurence from a public IP.
2b) Because the solution is going to be on each computer, I can't have users up do date on the Interface application. Any suggestions??? Do I have the file open automatically as a log in user, goes directly to a log in layout and can't go anywhere from there without putting in username and password. Then do I have to store user passwords? How do I keep them safe (encryption)? Privilege sets don't really worry me, I have just two: full access for me, and one generic for everybody else. All rights are controlled via scripts. I have an internal rights table that is associated with each user. This is cool because I can assign rights by group, then assign a user to one or more groups and his/her rights are figured out from what groups he/she is part of (if member of a and b, a can do X but b can't, that user CAN because at least one of his groups CAN). These privileges are then compiled into a return separated list. This list is set into a global field on start up. Status bar is always hidden and custom menus are in use. So all actions are controlled by scripts, and the first steps are always If[ middlevalues(global_user_priv; Nuserright;1)=1] Else [Exit Script]. So basically my problem is how do I have them log on from the client interface to the main file so I can get there userid and privileges so the solution can work properly.
Thanks in advance for your help!