Hi -
We wrote a little view that joined contributions with credits so that we could pull lists out of list manager that pulled on TOTAL contributions (i.e. a donor of $5K with a soft credit of $5K comes up as a 10K donor or a donor with 4 gifts of $2500 comes up as a $10K donor).
It worked like a charm. As of yesterday (the earlist I know of), it is now taking up 100% of the SQL Server CPU and crashing whatever lists use it. Has anyone run into this before where a view works like a champ then doesn't? The view works in SSMS - but pull it throught he client and the processor use goes through the roof.
Any ideas are helpful. Thanks!
Thanks – I’ll give that a look. What the view does is summarize soft credits and contributions for donors using a min(cont_dt) – for the first date of any number of gifts or credits – and ties to the campaign using Tessitura’s “vs_campaign” view to get the campaign description for list manager.
There are only three fields built into List Manager that use this view – “Alley_cont_amt” (our summarized field), “Alley_cont_dt” the min(cont_dt) of the summarized gifts and “Alley_campaign” which ties to “vs_campaign” to pull the campaign.
On a further note, after more testing, I’ve isolated it down so that I can build a list using “Alley_cont_amt” and “Alley_cont_dt” successfully, but when I put in the “Alley_campaign” is when the CPU goes crazy.
I did get a suggestion of putting an index on the view – but what concerns me about that is that the gifts are grouped by customer_no, campaign_no, fund_no and cont_designation. Can you index on four columns like that?
From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of David FrederickSent: Thursday, June 07, 2012 7:32 PMTo: Richard JacksonSubject: RE: [Tessitura Technical Forum] View using 100% CPU on server
Hi Richard,
Is this a list being generated with SQL code was it added to T_KEYWORD, and then used in the graphical interface of list manager? Perhaps you already did this, but the first thing I would suggest looking at is the actual SQL code used in list manager for one of the broken lists. There may be a bad join or something causing the problem.
_______________________________________________________David FrederickDatabase Applications AnalystSegerstrom Center for the Arts600 Town Center Drive, Costa Mesa, CA 92626T (714) 556-2122 x 4067 E DFrederick@SCFTA.org
From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Richard JacksonSent: Thursday, June 07, 2012 2:34 PMTo: David FrederickSubject: [Tessitura Technical Forum] View using 100% CPU on server
This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!