• Replies 7 replies
  • Subscribers 342 subscribers
  • Views 109 views
  • Users 0 members are here
Options
Related

Custom Columns in Portfolios

Seth Ghitelman
$organization

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
  • $organization

    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.

  • $organization in reply to Phillip Parks

    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.

    1.  First Gift Date 
      1. 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
    2. Total Giving Amt FY 
      1. 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)
    3. Largest Gift Amount 
      1. 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
    4. Last Gift Campaign 
      1. 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
    5. Total Number of Gift 
      1. 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
  • $organization in reply to Phillip Parks

    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.

    1.  First Gift Date 
      1. 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
    2. Total Giving Amt FY 
      1. 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)
    3. Largest Gift Amount 
      1. 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
    4. Last Gift Campaign 
      1. 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
    5. Total Number of Gift 
      1. 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