[Owncloud] Calendar access - speed issues

[Daniel Danger]

Hi,

I think we can optimize the performance by improving the db-structure.
I'll look into that. Optimization in php has to be done by somebody else
though.

Daniel


On 04/14/2012 02:09 PM, Jonathan wrote:
> 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