Performance issues with ad-hoc queries that use attributes after upgrading to 4.0

After upgrading to 4.0, a user may notice that ad-hoc queries that use attributes may take longer than normal.
Download and install the latest patch which contains all fixes from previous patches. If you are running an older version, download and install the latest version and then the patch.

Steps to Duplicate

An example impacted query can be seen below:
select  distinct 	[V_QUERY_CONSTITUENT].[NAME] as [Name],
[V_QUERY_CONSTITUENT].[ID] as [QUERYRECID]
from [dbo].[V_QUERY_CONSTITUENT] as [V_QUERY_CONSTITUENT]
inner join [dbo].[V_QUERY_REVENUERECOGNITION] as [V_QUERY_CONSTITUENT\Recognition Credits] on [V_QUERY_CONSTITUENT].[ID] = [V_QUERY_CONSTITUENT\Recognition Credits].[CONSTITUENTID]
left outer join [dbo].[V_QUERY_REVENUESPLIT] as [V_QUERY_CONSTITUENT\Recognition Credits\Revenue Application] on [V_QUERY_CONSTITUENT\Recognition Credits].[REVENUESPLITID] = [V_QUERY_CONSTITUENT\Recognition Credits\Revenue Application].[ID]
inner join [dbo].[V_QUERY_REVENUE] as [V_QUERY_CONSTITUENT\Recognition Credits\Revenue Application\Revenue Details] on [V_QUERY_CONSTITUENT\Recognition Credits\Revenue Application].[REVENUEID] = [V_QUERY_CONSTITUENT\Recognition Credits\Revenue Application\Revenue Details].[ID]
inner join [dbo].[V_QUERY_REVENUESPLIT] as [V_QUERY_CONSTITUENT\Recognition Credits\Revenue Application\Revenue Details\Revenue Splits] on [V_QUERY_CONSTITUENT\Recognition Credits\Revenue Application\Revenue Details].[ID] = [V_QUERY_CONSTITUENT\Recognition Credits\Revenue Application\Revenue Details\Revenue Splits].[REVENUEID]
left outer join [dbo].[V_QUERY_DESIGNATION] as [V_QUERY_CONSTITUENT\Recognition Credits\Revenue Application\Revenue Details\Revenue Splits\Designation] on [V_QUERY_CONSTITUENT\Recognition Credits\Revenue Application\Revenue Details\Revenue Splits].[DESIGNATIONID] = [V_QUERY_CONSTITUENT\Recognition Credits\Revenue Application\Revenue Details\Revenue Splits\Designation].[ID]
inner join [dbo].[V_QUERY_ATTRIBUTE828B0E2235B3433689874785ABB3AAD8] as [V_QUERY_CONSTITUENT\Recognition Credits\Revenue Application\Revenue Details\Revenue Splits\Designation\Designa1] on [V_QUERY_CONSTITUENT\Recognition Credits\Revenue Application\Revenue Details\Revenue Splits\Designation].[ID] = [V_QUERY_CONSTITUENT\Recognition Credits\Revenue Application\Revenue Details\Revenue Splits\Designation\Designa1].[PARENTID]
where [V_QUERY_CONSTITUENT\Recognition Credits].[TRANSACTIONTYPE] = N'Pledge'
 and [V_QUERY_CONSTITUENT\Recognition Credits\Revenue Application\Revenue Details\Revenue Splits].[APPLICATION] = N'Donation'
 and [V_QUERY_CONSTITUENT\Recognition Credits\Revenue Application\Revenue Details\Revenue Splits\Designation\Designa1].[VALUE] = (select [DESCRIPTION] from [dbo].[DESIGNATIONTRAITCODE] where ID = N'90ac5bfb-6a34-4334-884c-cc3b374e981b')
 and [V_QUERY_CONSTITUENT\Recognition Credits\Revenue Application\Revenue Details].[PLEDGEBALANCE] > 0

Note the join on V_QUERY_ATTRIBUTE828B0E2235B3433689874785ABB3AAD8 and then the subsequent filter with a nested select [VALUE] = (select [DESCRIPTION] from [dbo].[DESIGNATIONTRAITCODE] where ID = N'90ac5bfb-6a34-4334-884c-cc3b374e981b').

Environment

 Blackbaud CRM
 4.0
 4.0
 Service Pack 4

Was this article helpful?