1

Topic: Recorder League Tables timing out

Is there a way of caching the recorder league table on within our websites? The current forms are trying to do it on the fly and we are getting this timeout error 90% of the times it is run:

This league table includes the records entered via the LERC Wales app and SEWBReCORD.
There was an SQL error: ERROR: canceling statement due to statement timeout - SELECT COUNT(*) FROM ( SELECT DISTINCT o.created_by_id FROM cache_occurrences_functional o JOIN users u on u.id=o.created_by_id and u.id<>1 JOIN people p on p.id=u.person_id and p.deleted=false JOIN users privacyusers ON privacyusers.id=o.created_by_id WHERE o.training=false AND (o.website_id in (41) OR privacyusers.id=1 OR privacyusers.allow_share_for_reporting=true OR privacyusers.allow_share_for_reporting IS NULL) AND o.website_id in (23,41) AND o.zero_abundance='f' AND ('142,143,511'='' OR o.survey_id in (142,143,511)) AND o.created_by_id not in (1,8764) LIMIT 42) AS subquery

Dave Slade
Senior IT & Records Officer,
13 St Andrews Crescent, Cardiff, CF14 3DB
www.sewbrec.org.uk, www.sewbrecord.org.uk

2

Re: Recorder League Tables timing out

Hi Dave
Aggregations like this are hard to speed up, even with indexes, since it's simply about counting a large volume of records spread across the disk. That's why I've been busy building a new reporting engine which should work well for queries like this. I'll let you know when I get round to it, but drop me an email if you need it prioritised.
Cheers
John

John van Breda
Biodiverse IT

3

Re: Recorder League Tables timing out

Personally I don't think it's a high priority, but it would be nice to have them working, but I think I'll just disable them for now.
Thanks John

Dave Slade
Senior IT & Records Officer,
13 St Andrews Crescent, Cardiff, CF14 3DB
www.sewbrec.org.uk, www.sewbrecord.org.uk