Hey all. We are running Tessitura 15.1.3. My Marketing people are telling me more frequently than normal "this extraction has been counting for over an hour." When I dig into the extraction (chopping the query up and running individual parts in SMSS), I frequently find the block that is gumming things up uses vs_tck_hist. I also find the replacing the view vs_tck_hist with the table t_tck_hist makes everything better: instead of grinding endlessly, the query finishes in under 1 second.
Any suggestions? Marketing is building the extractions graphically and not appreciably different from how they have built them before. Below is today's query.
Clarke
Select Distinct a.customer_no From V_CUSTOMER_WITH_PRIMARY_GROUP a WITH (NOLOCK) JOIN (Select a1.customer_no From vs_cust_subscription_summary a1 WITH (NOLOCK) Where a1.season in (290,280,298,297,305,295,296,294,293,281,306,286,285,288,292,278,300,279,299,289)) as e ON e.customer_no = a.customer_no Where 1 = 1UNIONSelect Distinct a.customer_no From V_CUSTOMER_WITH_PRIMARY_GROUP a WITH (NOLOCK) JOIN (Select a1.customer_no From vs_tck_hist a1 WITH (NOLOCK) Where a1.season in (290,280,298,297,305,295,296,294,293,281,306,286,285,288,292,278,300,279,299,289)) as e ON e.customer_no = a.customer_no Where 1 = 1
There may be a SQL admin answer to speeding up a view, but have you checked the view definition? It might be doing something slow in there (look out for CROSS APPLYs and Tessitura's contact point functions (FS_GET_PHONES() if I remember the name right, is a particularly bad one).