Hi Jonathan, I had the same problem a year ago when working on another calendar app (unfortunately closed source...) and had to highly optimize the calculation of recurrence events (IMO there are too many possible RRULEs in the iCalendar standard). Here's some ways to go:
* given a start and end time of the currently viewed interval, select only events with dtstart < end * use a caching table for recurring events (select by month) which is smartly cleaned on every update * store the recent requests in a table, check for updates, only once pass all events (with rrules) to javascript and include the functionality of When.php in javascript (or better: contribute to fullcalendar!) though this is most work of these provided solutions... * make When.php even more efficient Florian -------- Original-Nachricht -------- Von: Jonathan <[email protected]> Gesendet: Sat Apr 14 14:09:33 MESZ 2012 An: [email protected] Betreff: [Owncloud] Calendar access - speed issues Hi, I'm trying to use owncloud in anger now, but have come across what seems to be a speed issue when viewing/retrieving/querying calendar data. Essentially, I have now imported my calendar entries from my mobile phone (I have 15 years of calendar data history), and this has degraded the response times of the web-based calendar app and the DAV interface as used by the dmfs CalDAV Android app for example, to a point where they are effectively unusable. Full details below, but essentially it appears that the calendar/DAV code is reading in *all* calendar events, then filtering/evaluating them in software, before returning just the subset that has been asked for. With this quantity of events, it's taking about a minute to crunch through that - and this affects the performance of the web interface as well as calendar sync via DAV. I would have thought that a simple SELECT query could narrow down the data before having to crunch through it all - or am I missing something? A profile of caldav.php's execution is here, showing just how many times each function call is called for the query below: http://users.ninja.org.uk/~jonathan/caldav/webgrind-caldav.pdf I have 4081 calendar entries, of which 291 are repeating events: SELECT count(*) FROM owncloud.oc_calendar_objects WHERE calendarid=4; 4081 SELECT count(*) from oc_calendar_objects where calendarid=4 and repeating=1; 291 The issue is that when trying to view calendar events for a reasonable time period (e.g. the current month), it takes a very long time to return the results: localhost$ time curl -k --data-binary @calendar_query.xml \ -H "depth:1" -H "user-agent:caldav" \ -H "content-type: application/xml" -D - \ -X REPORT -u username \ http://localhost/apps/calendar/caldav.php/calendars/username/main [...] real 1m0.854s user 0m0.007s sys 0m0.031s (These are figures from just now; last night it returned the data in 37 seconds, but for some reason it's taking longer at the moment) The calendar query is asking for basically the current month's events: <?xml version="1.0" encoding="utf-8" ?> <C:calendar-query xmlns:D="DAV:" xmlns:C="urn:ietf:params:xml:ns:caldav"> <C:filter> <C:comp-filter name="VCALENDAR"> <C:comp-filter name="VEVENT"> <C:time-range start="20120401T000000Z" end="20120501T000000Z"/> </C:comp-filter> </C:comp-filter> </C:filter> <D:prop> <D:getetag /> </D:prop> </C:calendar-query> In actual fact, there are 34 events in this time period: SELECT count(*) FROM owncloud.oc_calendar_objects WHERE calendarid=4 and startdate>'2012-04-01' and enddate<'2012-05-01'; 34 plus I expect the 291 repeating events would have to be evaluated, too - thus making a total of 325 events to process, not 4081. Still not insignificant, but in theory that should be over 10 times quicker! I would have thought that the code could (should?) first of all ask the database for just those events in the relevant time period (plus perhaps all events with 'repeating=1', as they will need to be evaluated), rather than just grabbing all events. Have I missed something? Many thanks! Jonathan _______________________________________________ Owncloud mailing list [email protected] https://mail.kde.org/mailman/listinfo/owncloud -- Florian Hülsmann <[email protected]> http://cbix.de _______________________________________________ Owncloud mailing list [email protected] https://mail.kde.org/mailman/listinfo/owncloud
