Answers.Body: <p>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. </p>
  
  <p><a href="https://stackoverflow.com/questions/535020/tracking-the-script-execution-time-in-php">https://stackoverflow.com/questions/535020/tracking-the-script-execution-time-in-php</a></p>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-02-23T08:52:20.747Z
Answers.Text: Have to check if issue caused by custom code / tpl; will accept the answer if so. Thanks!
Answers.UserDisplayName: yurg
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-02-23T13:27:25.250Z
Answers.Text: 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 
Answers.UserDisplayName: KarinG - Semper IT
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-02-22T23:56:05.553Z
Answers.LastActivityDate: 2017-02-23T13:24:27.963Z
Answers.LastEditDate: 2017-05-23T12:38:59.763Z
Answers.UserDisplayName: KarinG - Semper IT
Body: <p>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:</p>
  
  <ul>
  <li>Redis</li>
  <li>nginx ngx_http_gzip_module </li>
  <li>fine-tuned MySQL (via mysqltuner.pl)</li>
  <li>Drupal caching and  Views Accelerator module</li>
  </ul>
  
  <p>Any advice on how to make it loads as fast as possible (up to, say 2-3 seconds) please?</p>
  
  <p>Drupal 7x and CiviCRM 4.6x</p>
  
  <p><strong>UPD</strong> Views query</p>
  
  <blockquote>
    <p>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</p>
  </blockquote>
  
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2017-02-21T14:50:23.147Z
Comments.Text: Yeah that should not take this long. Can you post your views query?
Comments.UserDisplayName: KarinG - Semper IT
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2017-02-21T16:45:29.840Z
Comments.Text: @KarinG-SemperIT Thank you for reply! Have added it to post body.
Comments.UserDisplayName: yurg
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2017-02-21T17:20:58.907Z
Comments.Text: 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.
Comments.UserDisplayName: Alan Dixon
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2017-02-21T18:16:17.063Z
Comments.Text: @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 **
Comments.UserDisplayName: yurg
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2017-02-22T01:23:19.217Z
Comments.Text: Ok ms sounds good - are you using the modules/set up described here: http://civicrm.stackexchange.com/questions/12862/how-to-put-your-civicrm-contacts-on-a-leafleft-map-in-5min -
Comments.UserDisplayName: KarinG - Semper IT
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2017-02-22T08:37:34.827Z
Comments.Text: @KarinG-SemperIT Yes, except Markercluster, and starting to think may be clustering  results will help..
Comments.UserDisplayName: yurg
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2017-02-22T13:52:59.510Z
Comments.Text: How do you shown relationship on the map? Colouring the Markers perhaps?
Comments.UserDisplayName: KarinG - Semper IT
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2017-02-22T18:27:22.167Z
Comments.Text: @KarinG-SemperIT Even worse I'm afraid.. We retrieve related contact data via CiviCRM API calls inside Views TPL files and show it in map popup..Sometimes we retrieve data of related contact of related contact..
Comments.UserDisplayName: yurg
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2017-02-22T19:00:47.300Z
Comments.Text: Aha! So there is a lot more going on - I'd suggest put some debug/statements into your custom code to figure out how much time is spent where
Comments.UserDisplayName: KarinG - Semper IT
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2017-02-22T19:56:39.533Z
Comments.Text: @KarinG-SemperIT Absolutely! Thank you btw., sometimes just talking to someone helps..there is a special expression for it, don't remember it. Anyway, have just realised there is much more going on under the hood.
Comments.UserDisplayName: yurg
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2017-02-22T22:35:50.090Z
Comments.Text: Yeah, sounds like those tpl files are the issue, not views.
Comments.UserDisplayName: Alan Dixon
Tags: <drupal><drupal-views><geocoding><performance><jquery>
Title: Leaflet map + Drupal Views + 400 CiviCRM contacts data = performance?
ThreadText: # Leaflet map + Drupal Views + 400 CiviCRM contacts data = performance?
  
  (Asked by: username_0 on 2017-02-21)
  
  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
  
  **Tags:** drupal, drupal-views, geocoding, performance, jquery
  
  > **Comments:**
  > **username_1**: Yeah that should not take this long. Can you post your views query?  
  > **username_0**: @username_1 Thank you for reply! Have added it to post body.  
  > **username_2**: 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.  
  > **username_0**: @username_2 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 **  
  > **username_1**: Ok ms sounds good - are you using the modules/set up described here: http://civicrm.stackexchange.com/questions/12862/how-to-put-your-civicrm-contacts-on-a-leafleft-map-in-5min -  
  > **username_0**: @username_1 Yes, except Markercluster, and starting to think may be clustering  results will help..  
  > **username_1**: How do you shown relationship on the map? Colouring the Markers perhaps?  
  > **username_0**: @username_1 Even worse I'm afraid.. We retrieve related contact data via CiviCRM API calls inside Views TPL files and show it in map popup..Sometimes we retrieve data of related contact of related contact..  
  > **username_1**: Aha! So there is a lot more going on - I'd suggest put some debug/statements into your custom code to figure out how much time is spent where  
  > **username_0**: @username_1 Absolutely! Thank you btw., sometimes just talking to someone helps..there is a special expression for it, don't remember it. Anyway, have just realised there is much more going on under the hood.  
  > **username_2**: Yeah, sounds like those tpl files are the issue, not views.  
  
  ---
  
  ## 1 answers
  
  ### Answer by username_1 on 2017-02-22. Score: 0 | Accepted answer
  
  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>
  
  > **Comments:**
  > **username_0**: Have to check if issue caused by custom code / tpl; will accept the answer if so. Thanks!  
  > **username_1**: 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   
  
  ---
  
  