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).
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.
We are a RAMP client have noticed a significant response slow down in analytics and the client. We've reported both to TASK.
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
This is a known issue resolved in 15.0.9. The use of BI.VT_CUSTOMER in that constituent element has been altered to something more performant.
Best,Chris
Hi Chris - that's good news! Thank you!!