Analytics load issue with VT_CUSTOMER view

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 ranking
FROM 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 ranking
FROM 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

Parents
  • Hi David --

    Just in case it's helpful, we just upgraded to v15.1.1 on Monday from v15.0.1.  At the same time we elevated the [Impresario] compatibility level to 130 and enabled Legacy Cardinality Estimation.  Element: -25 in the LOAD_CONSTITUENT_SUMMARY took 20 seconds for 935,854 rows in our latest FULL data warehouse load.  I know v15.1.x includes a lot of index optimizations, so will be interested in what you experience after the upgrade.

    DGomez

  • Hi Daniel - thanks for letting me know about your experience! We are planning to elevate our compatibility level to 130 with our 15.1.1 upgrade as well. We already use legacy cardinality estimation. Anyway, fingers crossed that those changes will do the trick! Thanks again.

Reply Children
No Data