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?
Best Answer
In one of Tom Kyte's books he described a company with this exact problem. They did what you did and the performance was terrible. His recommendation was to store each instance of a recurring appointment in the database up to some point in the future. This solved all their performance problems with appointment searches.
Yes, there may be hundreds of occurrences of some appointments. So what? You only need to store the start times, and that's only a few kilobytes. And you don't need to store them in the indefinite future. Two or three years is plenty. When is the last time you wanted to know what appointments you had on some date in 2017?