Body: <p>I'm building a Drupal View to put ~1000 marker points with popup information on a Leaflet map using CiviCRM Entity and CiviCRM Entity Leaflet. The map is taking a very long time to render: over 3 minutes, and I have marker clustering enabled.</p>
  <p>My base View is CiviCRM Contact, with two relationships added in Views: CiviCRM Address (to get latitude/longitude fields) and CiviCRM Tag (to get related tag information on the marker popup). I'm also using the tag as an exposed filter.</p>
  <p>Checking performance statistics in Views shows query build time 6.25ms, query execute time 5398ms, and view render time 193862ms.</p>
  <p>If I create a view with no relationships and only latitude/longitude data using the CiviCRM Address table, I can display as many as 5000 markers with a short view render time of 3318ms.</p>
  <p>This issue doesn't seem to be related to using CiviCRM Entity Leaflet, because I get about the same render times if I choose to display as a table or an unformatted list. So it seems that adding relationships in a View with CiviCRM Entity and displaying all items takes a huge performance hit in rendering the View.</p>
  <p>I'm using Drupal 10.3.1, CiviCRM 5.74.5 with the latest version of all modules. CiviCRM is on a separate database than Drupal, and I have added the CiviCRM database connection information in the Drupal settings.php file.</p>
  <p>Any ideas on either how to increase performance in Views, or a completely different way to get Civi data that loads fast on a map?</p>
  
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2024-07-22T17:52:13.967Z
Comments.Text: Are you requiring the relationship(s) ? That would change the JOIN to an "INNER" join vs. "OUTER", and that can help performance. So base table of civicrm_contact, joins to "civicrm_address" .. assuming there MUST be an address to get it on the map, then making it an INNER join should be ok.  That's only for the query though. render time ..  we'll need to dig into that more to understand what is happening.
Comments.UserDisplayName: jackrabbithanna
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2024-07-22T21:16:20.143Z
Comments.Text: Thanks @jackrabbithanna — that's good to know. Requiring both relationships brought the render time down very slightly: query execute at 5156 ms, and view render at 169700 ms... so now just a little under 3 minutes. Still way too long for a web page.
Comments.UserDisplayName: macMark
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2024-07-22T23:58:58.763Z
Comments.Text: NGL I think Views in Drupal esp. with CE is a bit slow. Drupal loads all the entity objects so that takes alot of memory and time. I'd love to see an XHProf profile for your page load to see where the most time is spent and see if anything could be done.
Comments.UserDisplayName: jackrabbithanna
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2024-07-23T23:14:02.137Z
Comments.Text: What happens if you remove the word "clone" here https://github.com/eileenmcnaughton/civicrm_entity/blob/4.0.x/src/Plugin/views/field/CustomEntityField.php#L243
  
  Not sure of unintended consequences, purely an experiment for this performance issue.
Comments.UserDisplayName: jackrabbithanna
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2024-07-24T02:05:10.777Z
Comments.Text: @jackrabbithanna That reduced the render time to 96450ms: about a minute and a half!
Comments.UserDisplayName: macMark
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2024-07-25T16:47:53.910Z
Comments.Text: Thanks for letting me know. Did you happen to have a lot of CiviCRM custom fields on the View? Or not? I need to think about a better solution. IDK "clone" is truly necessary or not.
Comments.UserDisplayName: jackrabbithanna
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2024-07-25T22:30:44.820Z
Comments.Text: @jackrabbithanna There are 3 custom fields, other than lat/lon and contact id. Playing more with this, I found that adding the CiviCRM Tag relationship (which is necessary for the data and exposed filtering we want to show) leads to an increase in render time. That returns multiple rows of the same contact corresponding to the number of tags on that contact. I used Views aggregation to correct that, and that increased the render time as well. Removing "clone" does help and I'll keep exploring options. I do appreciate your help and insight so far!
Comments.UserDisplayName: macMark
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2024-07-26T20:21:30.877Z
Comments.Text: I made an experimental patch if you'd like to try it: https://patch-diff.githubusercontent.com/raw/eileenmcnaughton/civicrm_entity/pull/495.patch
Comments.UserDisplayName: jackrabbithanna
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2024-07-26T21:36:41.753Z
Comments.Text: Thanks, @jackrabbithanna — render time slightly down compared with just "clone" removed: 92950 ms.
Comments.UserDisplayName: macMark
Tags: <drupal-views><civicrm-entity>
Title: Drupal Views using CiviCRM Entity with relationships added takes too long to render results
ThreadText: # Drupal Views using CiviCRM Entity with relationships added takes too long to render results
  
  (Asked by: username_0 on 2024-07-22)
  
  I'm building a Drupal View to put ~1000 marker points with popup information on a Leaflet map using CiviCRM Entity and CiviCRM Entity Leaflet. The map is taking a very long time to render: over 3 minutes, and I have marker clustering enabled.
  
  My base View is CiviCRM Contact, with two relationships added in Views: CiviCRM Address (to get latitude/longitude fields) and CiviCRM Tag (to get related tag information on the marker popup). I'm also using the tag as an exposed filter.
  
  Checking performance statistics in Views shows query build time 6.25ms, query execute time 5398ms, and view render time 193862ms.
  
  If I create a view with no relationships and only latitude/longitude data using the CiviCRM Address table, I can display as many as 5000 markers with a short view render time of 3318ms.
  
  This issue doesn't seem to be related to using CiviCRM Entity Leaflet, because I get about the same render times if I choose to display as a table or an unformatted list. So it seems that adding relationships in a View with CiviCRM Entity and displaying all items takes a huge performance hit in rendering the View.
  
  I'm using Drupal 10.3.1, CiviCRM 5.74.5 with the latest version of all modules. CiviCRM is on a separate database than Drupal, and I have added the CiviCRM database connection information in the Drupal settings.php file.
  
  Any ideas on either how to increase performance in Views, or a completely different way to get Civi data that loads fast on a map?
  
  **Tags:** drupal-views, civicrm-entity
  
  > **Comments:**
  > **username_1**: Are you requiring the relationship(s) ? That would change the JOIN to an "INNER" join vs. "OUTER", and that can help performance. So base table of civicrm_contact, joins to "civicrm_address" .. assuming there MUST be an address to get it on the map, then making it an INNER join should be ok.  That's only for the query though. render time ..  we'll need to dig into that more to understand what is happening.  
  > **username_0**: Thanks @username_1 — that's good to know. Requiring both relationships brought the render time down very slightly: query execute at 5156 ms, and view render at 169700 ms... so now just a little under 3 minutes. Still way too long for a web page.  
  > **username_1**: NGL I think Views in Drupal esp. with CE is a bit slow. Drupal loads all the entity objects so that takes alot of memory and time. I'd love to see an XHProf profile for your page load to see where the most time is spent and see if anything could be done.  
  > **username_1**: What happens if you remove the word "clone" here https://github.com/eileenmcnaughton/civicrm_entity/blob/4.0.x/src/Plugin/views/field/CustomEntityField.php#L243  Not sure of unintended consequences, purely an experiment for this performance issue.  
  > **username_0**: @username_1 That reduced the render time to 96450ms: about a minute and a half!  
  > **username_1**: Thanks for letting me know. Did you happen to have a lot of CiviCRM custom fields on the View? Or not? I need to think about a better solution. IDK "clone" is truly necessary or not.  
  > **username_0**: @username_1 There are 3 custom fields, other than lat/lon and contact id. Playing more with this, I found that adding the CiviCRM Tag relationship (which is necessary for the data and exposed filtering we want to show) leads to an increase in render time. That returns multiple rows of the same contact corresponding to the number of tags on that contact. I used Views aggregation to correct that, and that increased the render time as well. Removing "clone" does help and I'll keep exploring options. I do appreciate your help and insight so far!  
  > **username_1**: I made an experimental patch if you'd like to try it: https://patch-diff.githubusercontent.com/raw/eileenmcnaughton/civicrm_entity/pull/495.patch  
  > **username_0**: Thanks, @username_1 — render time slightly down compared with just "clone" removed: 92950 ms.  
  
  ---
  
  ## 0 answers
  
  