CiviCRM Community Forums (archive)

I'm working on a 3.x installation that needs to get ACLs to scale to hundreds of groups and contacts in the hundreds of thousands.

The system was originally built for 1.9, and to get this to be at all usable, a lot of tricks were required, included query rewriting at a pretty low level.  The main enemy here is generated code that can use 50 and more left joins in some queries.  There's really no way to slice it:  MySQL won't do this in hours, much less in seconds, if you use the code as generated by CiviCRM.

Looking at 3.1, it turns out that a lot of this stuff is not much different from what it was two+ years ago with 1.9.  So I'm curious what people are doing to deal with the following issues:

• "Manage Groups" -- I just submitted a patch for this against 3.2 (CRM-6341).  More on this below...
• The "alphabet tabs" of a contact search.  Slow, slow, slow.  As in: takes literally hours kind of slow.  This one required custom indexing in 1.9, and query rewriting that added SQL hinting.  Logic in the "WHERE" clauses was also moved into the LEFT JOIN "ON" clauses, which helped the most.  Neither fix is currently in 3.1; I don't know about 3.2 off hand.
• Using the new ACL hooks effectively.   I'm experimenting with hook_civicrm_aclGroup.  civicrm_aclWhereClause is less useful (I think it should call hook_aclGroup before it runs -- would solve a mess of problems.)

What are people doing.  Also, I'd like to submit a patch to CRM_ACL_BAO_ACL::whereClause() that adds a call to hook_civicrm_aclGroup.  I've tried this, and it works very well, for me, anyway.  But I'm curious what behavior other developers want and expect.

Hi,

I ran into serious performance issues in 3.1.3 when around 15-20 ACL groups were operative. I came up with a patch that improved the performance dramatically but which AFAIK has not been scrutinised & tested by the core team to ensure it is logically correct:

http://forum.civicrm.org/index.php/topic,13515.msg58814.html#msg58814

HTH,

Dave J