Custom Columns in Portfolios

Seth Ghitelman $organization over 3 years ago

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?

Parents

Phillip Parks $organization over 3 years ago

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.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Cancel

Seth Ghitelman $organization over 3 years ago in reply to Phillip Parks

I added (NOLOCK) to most of the queries, but I am still getting timed out. I appreciate any suggestions to optimize the SQL.

DESCRIPTION	SQL_QUERY
First Gift Date	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
Total Giving Amt FY	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)
Largest Gift Amount	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
Last Gift Campaign	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
Total Number of Gift	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)))

Reply

Seth Ghitelman $organization over 3 years ago in reply to Phillip Parks

I added (NOLOCK) to most of the queries, but I am still getting timed out. I appreciate any suggestions to optimize the SQL.

DESCRIPTION	SQL_QUERY
First Gift Date	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
Total Giving Amt FY	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)
Largest Gift Amount	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
Last Gift Campaign	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
Total Number of Gift	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)))

Children

No Data