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
David Frederick LSC is on RAMP and 15.0.4. The Tessitura network did find some performance problems with our system and have done some "fixes". At the moment I don't have all of the details. I don't think the fixes were related to Tessitura Analytics Loading Problems. However here is what I'm finding.
Working in my DEV environment on RAMP.
The first query above runs 4 seconds with 380 records returned in our database. (We have a lot of walkup repeat business on our house Zipcode Accounts)
The select in the "addresses" CTE runs 16 seconds with 460k+ records.
The select in the "eaddresses" CTE runs 15 seconds with 366k+ records