Announcement

As of April 17, 2018, this site is now in read-only mode. To start new Blackbaud Developer’s discussions, you will find Developer Discussions in the Blackbaud CRM Community.

To post or respond to Blackbaud Community discussions, you will have to login using a www.blackbaud.com login. Learn how to login here. If you have questions or need assistance, please email community@blackbaud.com.

Using the BypassProcessorUpdate flag on smart fields

By: Ron Frum

Posted on: May 25, 2012

Smart fields are a great way to speed up the end user experience by performing complex calculations over night or on a scheduled basis so that the results are cached and available to users when needed. For information on getting started with smart fields, I would recommend reviewing the Smart Field SDK documentation. For some general guidelines on improving smart field performance, I would also recommend my previous post.

 

In version 2.91, we added the BypassProcessorUpdate flag to smart field specs. In doing some performance evaluations of our existing smart fields, we found that the previous smart field processing would sometimes generate unnecessary inserts and scans in TEMPDB when processing millions of rows. In smart fields with the BypassProcessorUpdate flag set to False or not specified, the stored procedure in the spec needs to return an ID for the appropriate record as well as the smart field value. When the smart field instance is processed, the platform will then take the results of the SP and dump them into a temp table. From the temp table, it will then perform separate INSERT, UPDATE and DELETE statements to the appropriate SMARTFIELD<GUID> table. This behavior is still supported and any smart fields created prior to 2.91 will still process the same as before.

 

Enabling the BypassProcessorUpdate flag gives the smart field author more control over the INSERT, UPDATE and DELETE operations on the SMARTFIELD<GUID> table. Instead of writing to a temp table and performing separate INSERT, UPDATE and DELETE statements, you can often achieve better performance by using the MERGE statement. The MERGE statement allows you to perform an “upsert” by inserting, updating and deleting rows in one T-SQL command. This is highly optimized for large tables and is often used in data warehousing scenarios where large tables need to bring in new and updated rows from the source system. This will almost always provide better performance and result in less I/O on the database server.

 

When using the BypassProcessorUpdate flag, the stored procedure will need to include a @SMARTFIELDID parameter with a datatype of uniqueidentifier. The platform will then handle populating this parameter with the ID of the smart field instance from the SMARTFIELD table. The SMARTFIELDID will then need to be used to determine the name of the table that stores the IDs and values for the smart field instance (i.e. the SMARTFIELD<GUID> table). The SQL in the stored procedure is then responsible for inserting new rows into this table, updating existing rows and removing rows for deleted records.

For an example of a smart field using the BypassProcessorUpdate flag, I would recommend taking a look at the Constituent Age smart field by running the following SQL:

select [SMARTFIELDSPECXML] from dbo.[SMARTFIELDCATALOG] where [DISPLAYNAME] = 'Constituent age' 

 

You’ll notice that it also contains logic for backwards compatibility in the event that the SMARTFIELDID is not populated. This should typically not be necessary for newly created smart fields. Here’s an excerpt of the stored procedure that handles finding the smart field table name and building the merge statement:

        declare @TARGET nvarchar(100); declare @TARGET_FIELD nvarchar(128); select @TARGET = [TABLECATALOG].[TABLENAME], @TARGET_FIELD = [SMARTFIELD].[VALUECOLUMNNAME] from dbo.[SMARTFIELD] left join dbo.[TABLECATALOG] on [SMARTFIELD].[TABLECATALOGID] = [TABLECATALOG].[ID] where [SMARTFIELD].[ID] = @SMARTFIELDID; set @sql = ' merge dbo.' + @TARGET + ' as target using (' + @sql + ') as source on target.ID = source.ID when matched then update set ' + @TARGET_FIELD + ' = source.VALUE when not matched by target then insert (ID, ' + @TARGET_FIELD + ') values (source.ID, source.VALUE) when not matched by source then delete;';

Good luck on making your smart fields even smarter!

Leave a Reply

Privacy Policy | Sitemap | © 2011 Blackbaud, Inc. All Rights Reserved

Digital Ocean