netFORUM 2015.1.4 Performance Enhancements

Overview

netFORUM 2015.1.4 (KB release notes and documentation) contains numerous performance enhancements that will make netFORUM run even faster. This article will provide more information about each enhancement, upgrade considerations (if any), and benefit. The article is divided into a section on enhancements specifically related to the netFORUM database, and another section that relates more to application enhancements.
 

Database Performance Enhancements

AlwaysOn Availability Groups

Microsoft SQL Server AlwaysOn Availability Groups offload reporting and read-only commands to a replica database to optimize database performance and balance the workload across two databases. In earlier versions of netFORUM, Availability Groups could be used for reports. In netFORUM 2015.1.4, virtually any read operation will execute against the read-only replica database. See KB article for more information (link coming soon). Also see EnableSQLReadOnlyAGConnection more more information on configuring Availability Groups.

This feature requires Microsoft SQL Server Enterprise edition and a secondary database server.

See netFORUM Enterprise SQL Server AlwaysOn Availability Groups for more details.


Clustered Indexes

Clustered indexes - until 2015.1.4, the clustered index on each database table was that table's primary key. For example, the clustered index on co_customer previously was cst_key. Beginning with netFORUM 2015.1.4, the clustered index was shifted to the xxx_add_date column in each table (e.g. ev_registrant.reg_add_date), which is a DateTime column that stores the datetime stamp for when a record gets inserted . Although the add_date column is not a unique column, SQL Server automatically adds a 4-digit uniqueifier value to satisfy the uniqueness requirement of a clustered index.

Benefit: switching the clustered index to the add_date column means smaller indexes, which has resulted in an observed 10% saving in disk storage for indexes, and faster INSERTs.

Upgrade Considerations: the distribution database for new netFORUM customers has clustered indexes on the xxx_add_date columns.  Clients who are on earlier releases of netFORUM will need to have the clustered indexes changed with a manual SQL script. Depending on the size of your database, this can require less than one hour for small databases, up to multiple hours for large tables. During this time, your netFORUM database must be taken offline. Abila's upgrade team will work with you on how and when to add these indexes.

SQL Table Partitioning

SQL Table Partitioning is a feature in SQL Server 2012 Enterprise edition, and SQL Server 2016 SP1 Standard edition, that splits the data in a table into two or more disk partitions, based on a common data characteristic such as a date column.

Benefit: Searching a smaller partition can result in faster searches. In netFORUM 2015.1.4, when netFORUM sees that a table is partitioned by date, then netFORUM will search only the newest partition when a user is doing a Search or Query. The user has the option to search the entire table if they want to search older records.

Upgrade Considerations: netFORUM 2015.1.4 does not partition every table, or any table, automatically. The decision on whether and how to partition needs to be considered carefully. See linked documentation for more.

Enable SQL Trace Comments

While this feature does not affect performance, it does aid and improve discovery and troubleshooting efforts.​  See ​Viewing SQL Trace Comments for more.

Application Performance Enhancements

Optimized Conditional Logic

Many areas of netFORUM, in particular eWeb, use a technique called Visibility SQL that conditionally makes all or part of a web page, link or other content be visible or invisible, depending on the results of a condition. For example, a page in eWeb might be visible only if the authenticated user is a member using a Visibility SQL command of "select visible=1 where {cst_member_flag}=1". These commands are executed on the netFORUM database, and if the resulting recordset returns one or more rows, then the content is visible; if zero rows are returned, then the content is not visible.

In netFORUM 2015.1.4, the visibility parser attempts to evaluate these commands locally in the application layer using information already in memory, instead of executing these commands in the database. Only more complex commands, typically those that might query into other tables, will require being run in the database to evaluate. But many simpler commands, such as the one above, can be evaluated in the application without needing to execute a SQL command.

As a result, netFORUM performance improves and overall overhead goes down as a result of eliminating the need to run a significant number of SQL commands, in particular in busy eWeb sites.

See EnableSQLLogicEvaluation for more.

Parameterization of SQL Commands

When an application executes SQL commands, sending the command to the database server in a parameterized query (as opposed to a non-parameterized string) can enhance database performance by allowing SQL Server to cache the execution plan of the query.

In netFORUM 2015.1.4, many queries that formerly were not parameterized are now being parameterized. These include record sets for any netFORUM data (individuals, invoices, events, etc.), child form selects, and many other netFORUM queries.

See EnableSQLStatementParams for more.

Cache Optimization

Many of netFORUM's caching operations have been improved to prevent premature eviction and allow for more precise clearing of cache.

More of eWeb content is cached.

Multi-Threading of Scheduled Tasks

netFORUM has many scheduled tasks that process many records in a bulk process, such as membership renewal, recurring gift processing, installment billing and payments, and many others.

With netFORUM 2015.1.4, the scheduled task framework has been enhanced to allow for any task to run multiple asynchronous threads, rather than looping through a set of records and processing one record at at a time. Each scheduled task must be revised to implement multi-threading. In this release, the installment payments and recurring gifts tasks have been converted to run in multi-thread mode. In future releases of netFORUM, additional tasks will be converted to run in multi-thread mode.

A scheduled task can be configured to run a maximum number of parallel threads in the task type setup, with a default setting of 20, a minimum setting of 8 and a maximum setting of 50. You cannot configure each individual task's thread count, only the overall task type.

Benefit: a task running in multi-thread mode can process a set of records faster compared to running in legacy synchronous mode.

FacadeClass Data Retrievals

Every object in netFORUM -- individuals, invoices, events, etc. -- is represented as a facade class.

In this release, the core select routines that load a facade object's data has been enhanced to load data faster with some basic code improvements.

Row Level Security

Improved user-identification helps speed sites that use Row-Level Security.

Database View Optimization

Performance improvements have been made to a core database view called vw_ac_invoice_detail that contains data about invoices. Optimizing this view resulted in select operations running approximately 25% faster than the previous version of the view.

Asynchronous Logging

Records added to the page access (md_page_access) and error log (fw_error_log) tables are inserted in an asynchronous thread, thereby speeding up any thread that insert into these logging tables.

Workflow Logging

Workflow Rules -- a widely-used feature in netFORUM -- now can be configured to not log the execution of each workflow task into a logging table. In previous versions of netFORUM, these events were always logged.  Turning off logging allows the application to run slightly faster by eliminating this overhead.

The WorkflowLogSetting system option provides the following options: Never;Always;Upon Error.  The recommended setting is Never. Change it to Always or Upon Error when you need to troubleshoot a Workflow Rule.

Miscellaneous Optimizations

Immediate elimination of unused connections and transactions reduces load on the server.M

Back to Build List -->