- When working with smart fields, remember that databases have huge amounts of data. Every layer of complexity, every temp table, and every index is a cost in both memory and time. It is better to use dynamic SQL to fork logic then make inefficient SQL execution plans.
- When creating dynamic sql, use ‘exec sp_executesql’ instead of just ‘exec’ so that the execution plan will be reused.
- Declaring a table with a primary key creates an index on the key. There is almost no reason to add an additional index on the key column after adding content to the table.
- Do not use the sql type ‘text’. It is deprecated and there is a conversion performance hit when switching between ‘nvarchar’ and ‘text’.
- Indexes on temp tables don’t auto-magically make something faster. By default, don’t put additional indexes on a temp table until evaluating how the table is used. In many cases, SQL may just be performing a table scan either way.
- Do not copy the entire contents of any table, such as dbo.Constituent, into a temp table
- Eliminate correlated sub queries. For example, ‘ID = (select top(1) ID…)’ is a performance bottleneck. Using ROW_NUMBER and other ranking functions are much more efficient.
- In version 2.91 and higher, smart fields support the ability to take control of the update process. This allows smart fields which could potentially have millions of records from doing an unnecessary insert\scan in tempDB. This requires the use of the BypassProcessorUpdate flag.
- Test with large data sets. When developing a constituent\revenue related smart field, we recommend testing with at least 150,000 constituents and at least a million revenue records.
Connect and collaborate with fellow Blackbaud users.