So, I'm gearing up to use a lot more Contribution Types (LTR_TNEW_CONTRIB_TYPE) in our consortium, and as I've been testing this out in Test and Dev it seems to be having dire performance consequences. While it is a lot of Contribution Types (I guess), it doesn't feel like it would be a lot for a larger consortium. LTR_TNEW_CUSTOM_FORM_DATA is the table that really explodes when you add additional Contribution Types, and presumably the main one referenced by "Reset Custom Forms". What I observe is that the Reset Custom Forms and Reset Cache buttons became slow, then frequently timed out. I'm seeing worse behavior in our Dev environment, which has fewer forms than Test, but is also probably resourced less and also is in v16, while we are still in v15 for Live and Test.
Support suggests that we're not the only licensee seeing some of this behavior, and I was curious who else might be seeing it and what people have in terms of Contribution Types and Form Data. Using the query:
use impresario; go select 'LTR_TNEW_CONTRIB_TYPE' as table_name, COUNT(distinct ct.organization) as organizations, COUNT(*) as uses, COUNT(*) as config_rows from LTR_TNEW_CONTRIB_TYPE as ct union all select 'LTR_TNEW_CUSTOM_FORM_SAVE_DATA' as table_name, COUNT(distinct sd.organization) as organizations, COUNT(distinct sd.intended_use) as uses, COUNT(*) as config_rows from LTR_TNEW_CUSTOM_FORM_SAVE_DATA as sd union all select 'LTR_TNEW_CUSTOM_FORM_DATA' as table_name, COUNT(distinct fd.organization) as organizations, COUNT(distinct fd.intended_use) as uses, COUNT(*) as config_rows from LTR_TNEW_CUSTOM_FORM_DATA as fd ;
I get this in Test:
What do other people have?
interesting. we have a boatload because we use TNEW for a lot of "free registration" events. So 860ish rows of Custom Form, but only 130ish of each contrib types and save data.
We use it regularly, but not heavily. We have a lot of things that all essentially have the same questions/fields, and so simply reuse a lot of them over and over. Though I anticipate that will just continue to grow as we do more things (we just expanded by another 90 or so lines in the last month).
Personally, I think the whole thing should be expanded into a full on "forms module" in Tessitura and there should be a dedicated saving table (or tables) for the data). When it was pioneered 6ish (I think?) years ago, this was a new idea to the use of TNEW, having questions/forms with purchases. But this is really becoming a standard thing for many of us. Especially for museums and/or education type events. Most performances are straightforward enough with tickets, but there is almost always something that needs to be asked for Education events. And yes, these questions are often "custom" in the fact that they may vary from event to event, but they are hardly "custom" anymore in the sense that they are "outliers to the norm".
And storing the data in CSIs is not horrible, but it is certainly not the BEST place for it, I do not think. I envision something like a cross between the Pricing Rules module and the Content Tabs on performances/constituents. To build them in some at least halfway decent GUI for people who are not database people but Box Office or Education workers who are NOT adept at system tables. And then some area where you could see on the performance/event the responses per constituent and some place on the constituent where you could see their event responses in some form of tabular structure.
But anyway, to go back to the original question, we have noticed no issues thus far, but, as I said, we are really not using it quite to your level yet.
John A. Moskal II
Gawain Lavers, that's interesting. None of the users in my consortium have complained about this, but that doesn't mean they aren't experiencing any slowness. We do have a good number of rows in the forms table:
Keep us posted on what you find out!
I have conflicting evidence about whether the issue spills across organizations: Initially it did appear that even organizations with a modest number of form rows were seeing the timeouts (for us, pretty much all of the Contrib Types/Form Data belong to a single organization). I've also pulled the vast majority of Contrib Types/Form Data from our Dev environment and that site and admin remain virtually non-functional. Support has cautioned me that there might not be a direct correlation, but as I understand it are still trying to figure out what the exact problem is. They have said that it is affecting other licensees.
John A. Moskal II said:And storing the data in CSIs is not horrible, but it is certainly not the BEST place for it, I do not think. I envision something like a cross between the Pricing Rules module and the Content Tabs on performances/constituents. To build them in some at least halfway decent GUI for people who are not database people but Box Office or Education workers who are NOT adept at system tables. And then some area where you could see on the performance/event the responses per constituent and some place on the constituent where you could see their event responses in some form of tabular structure.
Taking this kind of data from customers is important and probably going to become increasingly important, and I think that having better management and storage options should be a roadmap item. Maybe it's up to us to start assembling a comprehensive list of use cases. I feel like many of the use cases I've had have been awkwardly served by the current survey functionality: there's always something in it that's a little wonky.
I have an outstanding project to put together an Idea for a better organized survey building structure (the current structure is needlessly repetitive for, I think, probably most users). I think I probably shouldn't burden it with improvements to data management just yet, maybe that can be a second, related Idea.
I watched the webinar on the Education Extension and it seems that it is the most likely solution for dealing with custom forms and CSI's. I think it would be useful for us, but it looks like it would cost $10k to set up, plus an annual fee of $2250. There are too many of us who use custom forms daily for something like this to have to be an extra cost and add-on.
It's definitely a more comprehensive "survey" system, but very purpose-specific.
I built my own custom table for storage which records basically an ersatz SQL column_name and column_data into it that is tied to a perf_no and customer_no that I use to then generate a custom report for any given performance. It works well enough. But it is definitely a hacky sort of solution to the CSI situation rather than anything near a grand solution idea.
The one I'm poking at right now is the fact that we have a survey field to capture "Declines Benefits" for our contributions. That's fine for us for now, as we only take money On Account and then manually create contributions later using the information from the survey CSI. But if we were ever to take money directly to Fund (and then automatically create Memberships) we'd need to know about this item and process it. Weirdly, "Decline Benefits" is actually a field that can be set in the web contribution tables, and if it is set, then generated memberships are properly flagged with the Declined Benefits Indicator, but TNEW has never surfaced this feature.
John A. Moskal IIPersonally, I think the whole thing should be expanded into a full on "forms module" in Tessitura - love this idea!