I am looking to see if anyone else has created customs columns in the Portfolio screen. I have recently add some new fields at the request of our gift officers. I add fields like "Last Gift Date" "Gift Amount this FY", etc. Each column works fine on it's own, but I am, finding when I add multiples of these columns. Other users and myself get the following error message.
I suspect either my SQL queries in TR CUSTOM PORTFOLIO ELEMENTS are inefficient or there is a limit to the number of columns.
Has anyone seen this issue?
This is typically caused by only a few things. The most significant is a deadlock. It would help to see the query you are using. It may need to be less complex or optimized (maybe you need a (NO LOCK) statement in it to keep things from locking up.
Phillip,
I added (NOLOCK) to most them , but I still get the error message . I would appreciate any feedback on how to optimize them.
I added (NOLOCK) to most of the queries, but I am still getting timed out. I appreciate any suggestions to optimize the SQL.
I added (NOLOCK) to most of queries, but they're are still timing out. I appreciate any suggestion on to how to optimize.
I added (NOLOCK) to most of the queries, but they're still timing out. I appreciate any suggestions for optimizing them would be greatly appreciated.
select TOP 1 cont_dt from VS_ELEMENTS_CONTRIBUTION with (NOLOCK) where customer_no = (Select Coalesce((Select group_customer_no from t_affiliation where individual_customer_no = @customer_no and primary_ind = 'Y' and inactive = 'N'), (select customer_no from t_customer where customer_no = @customer_no))) order by cont_dt asc
SELECT SUM(cont_amt) FROM VS_CONTRIBUTION_WITH_INITIATOR co JOIN T_CAMPAIGN ca ON co.campaign_no = ca.campaign_no WHERE co.customer_no = @customer_no AND ca.fyear = (SELECT fyear FROM VRS_BATCH_PERIOD WHERE GETDATE() BETWEEN start_dt AND end_dt) AND ISNULL(ca.category, -1) NOT IN (0) AND co.role IN (1,3,4) AND ISNULL(co.creditee_type, -1) NOT IN (0)
select TOP 1 cont_amt from VS_ELEMENTS_CONTRIBUTION WITH (NOLOCK) where customer_no = (Select Coalesce((Select group_customer_no from t_affiliation where individual_customer_no = @customer_no and primary_ind = 'Y' and inactive = 'N'), (select customer_no from t_customer where customer_no = @customer_no))) order by cont_dt asc
select TOP 1 campaign_desc from VS_ELEMENTS_CONTRIBUTION WITH (NOLOCK) where customer_no = (Select Coalesce((Select group_customer_no from t_affiliation where individual_customer_no = @customer_no and primary_ind = 'Y' and inactive = 'N'), (select customer_no from t_customer where customer_no = @customer_no))) order by cont_dt desc
select count (ref_no) from VS_ELEMENTS_CONTRIBUTION With (NOLOCK) where customer_no = (Select Coalesce((Select group_customer_no from t_affiliation where individual_customer_no = @customer_no and primary_ind = 'Y' and inactive = 'N'), (select customer_no from t_customer where customer_no = @customer_no)))
Sorry about the duplicate posts. It got blocked as SPAM and I tried different formats to hopefully get the reply to post. So of course once it was determined it was not spam so now there are 3 versions of the same post
Curiously enough, Seth, all of these queries run exceptionally fast individually when run in SSMS against a single @customer_no, even for a customer with hundreds of contributions or plans. It only starts going slowly when you gang them up in a single statement, as happens when you run a statement like:exec AP_PORTFOLIO @worker_customer_no=N'859303',@status_str=default,@role_str=default,@primary_only=N'Y',@portfolio_custom_columns=N'8,10,11,12,13',@plan_custom_columns=default This is the stored procedure behind the portfolio screen and the @portfolio_custom_columns parameter comes from the ids in TR_PORTFOLIO_CUSTOM_ELEMENT. Using these generates a temp table and then runs a statement like this against it:Select [element_8] = (select TOP 1 cont_dt from VS_ELEMENTS_CONTRIBUTION with (NOLOCK) where customer_no = (Select Coalesce((Select group_customer_no from t_affiliation where individual_customer_no = __plans.customer_no and primary_ind = 'Y' and inactive = 'N'),(select customer_no from t_customer where customer_no = __plans.customer_no))) order by cont_dt asc),[element_10] = (SELECT SUM(cont_amt) FROM VS_CONTRIBUTION_WITH_INITIATOR co JOIN T_CAMPAIGN ca ON co.campaign_no = ca.campaign_no WHERE co.customer_no = __plans.customer_no AND ca.fyear = (SELECT fyear FROM VRS_BATCH_PERIOD WHERE GETDATE() BETWEEN start_dt AND end_dt) AND ISNULL(ca.category, -1) NOT IN (0) AND co.role IN (1,3,4) AND ISNULL(co.creditee_type, -1) NOT IN (0)),[element_11] = (select TOP 1 cont_amt from VS_ELEMENTS_CONTRIBUTION WITH (NOLOCK) where customer_no = (Select Coalesce((Select group_customer_no from t_affiliation where individual_customer_no = __plans.customer_no and primary_ind = 'Y' and inactive = 'N'),(select customer_no from t_customer where customer_no = __plans.customer_no))) order by cont_dt asc),[element_12] = (select TOP 1 campaign_desc from VS_ELEMENTS_CONTRIBUTION WITH (NOLOCK) where customer_no = (Select Coalesce((Select group_customer_no from t_affiliation where individual_customer_no = __plans.customer_no and primary_ind = 'Y' and inactive = 'N'),(select customer_no from t_customer where customer_no = __plans.customer_no))) order by cont_dt desc),[element_13] = (select count (ref_no) from VS_ELEMENTS_CONTRIBUTION With (NOLOCK) where customer_no = (Select Coalesce((Select group_customer_no from t_affiliation where individual_customer_no = __plans.customer_no and primary_ind = 'Y' and inactive = 'N'),(select customer_no from t_customer where customer_no = __plans.customer_no)))),__plans.plan_noFrom ##plans78E09C20692C4BF9AA1FCB2519FC1020 as __plansHaving so many SELECTs against the same view like that creates a much more complicated execution plan than running each statement individually would. I do suspect that having the subquery in the WHERE clause on the @customer_no variable is part of the complication here as it's got to do a fair bit of calculation and index scans for each customer in that worker's list of plans. For worker 859303 who has 2275 plans, that adds up. This procedure took 1:36 minutes to run in your test system. I'm guessing you added this complexity since you have the plan on the individual record but the contributions on the household. Do you track the individual as an initiator on the household's contribution? That would get them into the view you're using without having to go through all the extra table joins. Using fewer custom elements also cuts down the processing time, of course. Only selecting 3 of those 5 elements into AP_PORTFOLIO cut the processing time down to 28-37 seconds.