I was wondering if I could get some help/advice on a room booking database I'm working on. I'm having serious speed issues with two scripts and I cannot figure out why that is.
Background: There are several buildings in my job and each of those has several rooms that are required to be booked for meetings, etc. Not everyone has access to all buildings so users have persmissions set. The rooms are booked in half hour increments and note the person who booked the room, the reason and it greys each half hour slot out to show visually that it is unavailable. I'll provide some screenshots.
So there are two main tables. One I just called menu and the other I called room bookings. The room bookings has a record in it for each half hour slot of each room for the day. So, if there are two rooms, there would be 20 records for each room in a day to account for each slot, nearly 15,000 reccords for the year. The script runs a find when they want to book a room, looking for that day/time/room and if the fields associated with a booking are blank, it writes info it it, if it's already booked the user gets a custom message saying it's already booked, find another room, etc.
Initially all my layouts were based on the menu with relationships built between the two. I put loads of portals showing only one field from the Room bookings. It filtered what was showed based on the relationships. At the bones of it all of this works fine, as it should. I have a script on a button, when the users enters the date, room, time they want to book, it goes to room bookings table, finds the appropriat records and either books or confirms a clash in bookings. This script is taking a long time to load, sometimes timing out users. There are two steps to it, first it loops through all the records it needs for the booking and does a check to see if it's already booked, if it is, there is a text it saves. Then once this loop is completed it has two options: If it's already booked tells them, does nothing more. If it's not, in then finds the records again via loop, and sets the appropriate fields. Screenshots attached again. Clearly there is something in my scripts that is upsetting the process, but I can't see what. Maybe I'm looking at it way too long.
It's of note that some of the fields were originally global fields, all working off the one menu record. I thought this may be an issue so on the login script it creates a new record in menu and they are no longer global fields. Each user is now just amending the record they created on menu.
Can anyone see any problems? Or am I going about this all wrong? Is there a different/better/faster way? Thank you SO much.