[Owncloud] Calendar access - speed issues

[Florian Hülsmann]

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 <jh-owncloud-dev at ninja.org.uk>
Gesendet: Sat Apr 14 14:09:33 MESZ 2012
An: owncloud at kde.org
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
Owncloud at kde.org
https://mail.kde.org/mailman/listinfo/owncloud

-- 
Florian Hülsmann
<fh at cbix.de>
http://cbix.de