1

We try to pull CiviCRM contacts data incl. multiply custom fields and display on map. There are around 400 contacts to display, yet map page loads a bit slower, than expected (5 to 10 seconds). It has been worse initially even at quite powerful server, but we've got significant improvements using combination of:

  • Redis
  • nginx ngx_http_gzip_module
  • fine-tuned MySQL (via mysqltuner.pl)
  • Drupal caching and Views Accelerator module

Any advice on how to make it loads as fast as possible (up to, say 2-3 seconds) please?

Drupal 7x and CiviCRM 4.6x

UPD Views query

SELECT civicrm_address.geo_code_1 AS civicrm_address_geo_code_1, civicrm_address.geo_code_2 AS civicrm_address_geo_code_2, civicrm_contact.organization_name AS civicrm_contact_organization_name, civicrm_website.url AS civicrm_website_url, civicrm_value_society_chairman_10.public_contact_for_society_988 AS civicrm_value_society_chairman_10_public_contact_for_society, civicrm_address.street_address AS civicrm_address_street_address, civicrm_contact_civicrm_relationship.id AS civicrm_contact_civicrm_relationship_id, civicrm_value_society_chairman_10.subs_rate_single__38 AS civicrm_value_society_chairman_10_subs_rate_single__38, civicrm_value_society_chairman_10.meeting_day_time_1_531 AS civicrm_value_society_chairman_10_meeting_day_time_1_531, civicrm_value_society_chairman_10.meeting_day_time_2_532 AS civicrm_value_society_chairman_10_meeting_day_time_2_532, civicrm_contact.id AS id FROM {civicrm_contact} civicrm_contact INNER JOIN {civicrm_relationship} civicrm_relationship_civicrm_contact ON civicrm_contact.id = civicrm_relationship_civicrm_contact.contact_id_a AND (civicrm_relationship_civicrm_contact.relationship_type_id = '59' AND civicrm_relationship_civicrm_contact.is_active = '1') LEFT JOIN {civicrm_relationship} civicrm_relationship ON civicrm_contact.id = civicrm_relationship.contact_id_a INNER JOIN {civicrm_contact} civicrm_contact_civicrm_relationship ON civicrm_relationship.contact_id_b = civicrm_contact_civicrm_relationship.id LEFT JOIN {civicrm_address} civicrm_address ON civicrm_contact.id = civicrm_address.contact_id LEFT JOIN {civicrm_website} civicrm_website ON civicrm_contact.id = civicrm_website.contact_id LEFT JOIN {civicrm_value_society_chairman_10} civicrm_value_society_chairman_10 ON civicrm_contact.id = civicrm_value_society_chairman_10.entity_id WHERE (( (civicrm_address.geo_code_1 IS NOT NULL ) AND (civicrm_address.geo_code_2 IS NOT NULL ) AND (civicrm_contact.contact_type IN ('Organization')) AND (civicrm_contact.contact_sub_type IN ('Society')) )) LIMIT 1000 OFFSET 0

Improve this question
11
  • Yeah that should not take this long. Can you post your views query?
    – KarinG - Semper IT
    Feb 21, 2017 at 14:50
  • @KarinG-SemperIT Thank you for reply! Have added it to post body.
    – yurg
    Feb 21, 2017 at 16:45
  • Can you confirm whether the issue is the above sql? e.g. on the views admin page, you can get it to tell you how long the query takes. Views caching would be one obvious route. If that's not an option, then maybe verify whether you really need all those fields to display your map - I notice that most of the expensive part (e.g. the relationship and custom fields) are not required for the selection criteria, so if you're not actually displaying them, it would seem a waste.
    – Alan Dixon
    Feb 21, 2017 at 17:20
  • @AlanDixon Unfortunately, we need all fields included to View. According to Views internal performance info (w/o Devel), it looks like this: ** Query build time 2.57 ms Query execute time 3.05 ms View render time 3.15 m **
    – yurg
    Feb 21, 2017 at 18:16
  • Ok ms sounds good - are you using the modules/set up described here: civicrm.stackexchange.com/questions/12862/… -
    – KarinG - Semper IT
    Feb 22, 2017 at 1:23

1 Answer 1

Reset to default
0

We think the answer is that the performance issues are (very likely) in the custom code/tpls - that get invoked as well. We've determined that the Views query itself was not the issue.

https://stackoverflow.com/questions/535020/tracking-the-script-execution-time-in-php

Improve this answer
2
  • Have to check if issue caused by custom code / tpl; will accept the answer if so. Thanks!
    – yurg
    Feb 23, 2017 at 8:52
  • Not sure how the time delays are not in your custom code/tpl - as it's not the views query - and it's not leaflet 😀
    – KarinG - Semper IT
    Feb 23, 2017 at 13:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.