Our company implemented a calendar system a few months back with recurring appointments, using iCal strings to store the recurring appointment criteria.
We are starting to run into performance problems because of these.
Our current thinking is something along these lines...
A user requests all appointments for a date range. Typically no more than 30 days.
We grab all non-recurring events from the database that fall in this date range. Then we grab ALL events that contain an iCal string and run each one through a processor to determine if the event happens to repeat in our requested date range. For example, the user may have the event starting on 1/1/14, repeating every Tuesday and Wednesday ongoing forever. Another event, however, may begin on 1/1/14, repeating every Tuesday and Wednesday for one month and is now outside of our selected range. The first one would repeat to our date range and be included. The second one would not.
As far as I can tell, there really isn't any good way to determine any kind of filtering so that I don't have to pull every single one of these events from the database and test them to see if they match our date range.
As the number of recurring events grows, our database is being slogged down with requests that return more and more records, and our server needs to process each of these, most of which aren't necessary, but we don't know until we get them to the server and process them.
Has anybody faced this before, and is there a better algorithm that we can implement to limit the number of records returned?