Hi all - I wasn't 100% sure where to post this and almost posted it in one of the analytics forums; however, since this is more of a technical issue related to the load, I figured I would post it here.
We are running into intermittent performance issues with our Analytics load specifically with the LOAD_CONSTITUENT_SUMMARY element: -25 query (which appears to be the Primary Address Postcode element). Here is an example of this query:
select -25 as element_id,q.customer_no,MIN(LEFT(REPLACE(primary_addr_postal_code,' ',''),5)) as result from BI.VT_CUSTOMER q group by q.customer_no having q.customer_no in (select customer_no from dbo.T_CUSTOMER WHERE last_activity_dt >= '2019-06-29 23:00:01')
This query runs about an hour and a half and then only returns a small number of rows (particularly when running incremental loads). After looking at query plans and troubleshooting, I traced the problem to the RANK statements used in two CTEs that get the primary address and primary email address in VT_CUSTOMER. Specifically:
WITH addresses AS(SELECT DISTINCT A.customer_no, A.street1, A.street2, A.street3, A.city, A.state, A.postal_code, A.country, A.address_type, A.geo_area, RANK() OVER (PARTITION BY A.customer_no ORDER BY A.last_update_dt, A.address_no DESC) AS rankingFROM dbo.FT_GET_PRIMARY_ADDRESS() A), eaddresses AS(SELECT DISTINCT E.customer_no, E.address, E.eaddress_type, RANK() OVER (PARTITION BY E.customer_no ORDER BY E.last_update_dt, E.eaddress_no DESC) AS rankingFROM dbo.FT_GET_PRIMARY_EADDRESS() E)
I found that our database never has more than one primary address or primary email address per customer, so I am currently using a tweaked VT_CUSTOMER that excludes this ranking, and the -25 LOAD_CONSTITUENT_SUMMARY query now runs in a matter of seconds rather than 90+ minutes.
Has anyone else run into this? I'm still working on tracking down whether there is some index that would help or perhaps an index that needs to be rebuilt. (We do regular index maintenance, so usually that isn't the issue.)
We are on v15.0.4, but from what I can tell, VT_CUSTOMER hasn't really changed in v15.1 and TX_ANALYTICS_DIVISION_ELEMENT setup for this element (Primary Address Postcode) appears the same as well so I don't think v15.1 will help with this.
Thanks!David
Hi David,
This element loads in seconds for us, but we are testing v15.1. We opted to skip v15 because we heard there were a lot of tweaks to Analytics specifically in v15.1.
Thanks, Nancy! I'll soon run our first Analytics load in v15.1 and will see how it goes. But so far, based on what I've checked, it appears this element is the same. Perhaps it relates to something in our environment, such as the use of legacy cardinality estimation or compatibility level (we are currently at 120 but are starting to test with 130).