optimizing calendar repeat events
От | Thomas T. Thai |
---|---|
Тема | optimizing calendar repeat events |
Дата | |
Msg-id | Pine.NEB.4.21.0110071248480.8226-100000@ns01.minnesota.com обсуждение исходный текст |
Список | pgsql-general |
I'm currently trying to optimize a calendar application's handling of repeat events. Here are two relevant tables: -- Calendar event entry -- cal_date is an integer of the format YYYYMMDD -- cal_time is an integer of the format HHMM -- cal_duration is in minutes -- cal_priority: 1=Low, 2=Med, 3=High -- cal_type: E=Event, M=Repeating event -- cal_access: -- P=Public -- R=Confidential (others can see time allocated but not what it is) CREATE TABLE webcal_entry ( cal_id INT NOT NULL, cal_group_id INT, cal_create_by VARCHAR(25) NOT NULL, cal_date INT NOT NULL, cal_time INT, cal_mod_date INT, cal_mod_time INT, cal_duration INT NOT NULL, cal_priority INT DEFAULT 2, cal_type CHAR(1) DEFAULT 'E', cal_access CHAR(1) DEFAULT 'P', cal_name VARCHAR(80) NOT NULL, cal_description TEXT, PRIMARY KEY ( cal_id ) ); -- repeat cal_type are: Daily, Weekly, Monthly (by day), -- Monthly (by date), Yearly -- cal_frequency is an interval of how the event will repeat, i.e. -- every (2) months, every (3) weeks, etc. -- cal_days is for weekly repeats. default is 'nnnnnnn'. If you want to -- repeat weekly on sunday and monday, you'd have cal_type set to -- 'weekly' and cal_days set to 'yynnnnn'. -- cal_end is when the day repeat will end. -- cal_id is the event id CREATE TABLE webcal_entry_repeats ( cal_id INT DEFAULT '0' NOT NULL, cal_type VARCHAR(20), cal_end INT, cal_frequency INT DEFAULT '1', cal_days CHAR(7), PRIMARY KEY (cal_id) ); When I want all the events for a particular date range, I query for those rows in webcal_entry. In addition, I have to pull in ALL the rows for webcal_entry_repeats, loops through each webcal_entry row. For each webcal_entry row, loop through all the webcal_entry_repeat rows to see if any repeat events go before or after that webcal_entry row. As you can see, with many rows of webcal_entry_repeats, this can get rather bogged down and slow, not to mention memory intensive. Also, this current method doesn't allow me to use the LIMIT clause to 'paginate' the results to display only certain number of events per page. What I'm looking to do is combine the columns of webcal_entry_repeats into webcal_entry so describe each event. If that event doesn't repeat then cal_type is set to "no-repeat". Then when I query for a date range, the query would somehow figure out if the combination of repeat type columns would include that event in the date range i'm querying. This would allow me to do it in one SELECT and make use of the LIMIT clause to 'paginate' results per page (say results of 20 events per query). I thought about it for a while, but couldn't come up with an efficient solution. It's especially hard with the frequency, one would have to somehow loop in the sql query a certain times of frequency while the end date is within our selected range. Thanks for any suggestions.
В списке pgsql-general по дате отправления: