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
This was happening to us also, it was taking over an hour for the simplest extractions to run. I put in a help ticket and we found out it had to do with comparability level in SQL. Once I changed the compatibility level from 2012 to 2014 it solved the issue.
That sounds like it is our issue. I may put in a ticket just to be sure, but I do remember that setting the compatibility level was something I did without being sure what was right.
From Tessitura:If SQL Server 2017 is being used with v15.1, we recommend to set the compatibility level to 130 (SQL Server 2016).... For SQL 2016 and Tessitura 15.1 you don't need to have the compatibility set lower than the default for 2016.