As your Slate database grows year over year in both record volume and complexity, you may notice some features exhibiting strain under heavy resource utilization. To keep your database working at peak efficiency, perform the audits outlined in this article once or twice a year.

📖 Related reading: To remedy immediate issues, like rule backups or database timeouts, see Tips to Optimize Slate for Efficiency.

Import three purpose-built auditing queries with Suitcase

We’ve created a Suitcase object containing three queries, Rule Audit, Query Audit, and Import Audit, that you can import into your database to help identify some of the issues described in this article.

💼 Slate example: Proactive Maintenance for Database Health

Use Suitcase to import three ready-made queries to audit imports, queries, and rules in your database.

2cf44bf4-8e6c-4ec6-8dd2-e3ddf645d73e:slate-examples

Reduce table size

Slate stores data in a number of interrelated tables.

Some tables can accrue huge amounts of data over time. The larger the table, the more time is required to search through and manipulate its data.

Clean up irrelevant data with retention policies

Use retention policies to clean out data that is no longer relevant to your process.

A retention policy is a rule that reduces the number of records Slate has to look through when running rules or querying tables.

🔔 Exercise caution when working with retention policies. Always run retention policies in your test environment first.

📖 Further reading: Retention policies

Use tracking queries sparingly

On a query, when you select Edit Properties, you’ll find three execution options:

• Retrieve all records each time query is run

• Retrieve all records and save recent result history

• Retrieve only the new records since the query was last run

You create a tracking query when you select either of the latter two options. Each time a tracking query runs, it creates rows on the Query Run table for each row the query returns.

Be judicious about which queries track results: even if runs are deleted, the tracking rows on the query run table aren’t.

📖 Further reading: Query execution options

Use the Queue setting in your queries sparingly

In a similar vein to tracking queries, in a query’s Edit Properties menu you’ll also find a setting called Queue, accessible if you set Execution Options to Retrieve all records each time query is run. Queue defaults to None. The options available in the list depend on the query’s scope.

When you select a queue option, like Person, and run the query, it only returns records that have been updated since the last time the query was run. In the process, these records are added as a new row to an internal Update table.

Say you have five queries with the queue setting enabled that return the Alexander Hamilton record. Every time the Alexander Hamilton record is updated, Slate adds five rows representing that record to the Update table, one for each of the queue-enabled queries. Then, as each of those queries are executed (if ever), the corresponding rows are dropped from the Update table.

From the above example, if those queue-enabled queries aren’t running as frequently as the records they return are updated, you can end up with a bloated Update table.

Audit your queries for use of the queue setting and make sure they’re strictly necessary. Deactivate any that aren't to keep the Update table a manageable size.

📖 Further reading: Query queue setting

Audit exports, imports, and active users

Every time a source is imported, or a query is run, database resources are consumed. While individual items may not seem to have an effect on database health, the aggregate of all of these actions can impact your database's performance.

Use the Suitcase queries to help audit the following:

Exports

Go to Database → Job Activity Monitor to see all scheduled queries and reports across your database.

Use this tool to complete the following steps:

1. Review each unique item. Every item listed here should have a clear business purpose. Investigate any items that are unfamiliar.

2. Investigate problematic exports. Using the start and end date selectors, select a generous range. Then, check out the Late and Failure options in the sidebar. If anything appears in either of these views, troubleshoot them for efficiency.

3. Review each delivery window. Although the Overnight window is generally recommended, some institutions may see late or failed exports if too many are scheduled during this window. If this is the case, consider moving some exports to the Evening window to alleviate overnight strain.

Imports

1. Review all source formats. If there are any unused ones, inactivate them.

2. Review source formats with large numbers of records. Consider also whether this large number of records can be reduced at the source.

3. Check all source formats with the One-Time / Differential setting. Consider whether this is an accurate description of the data. If the data includes mostly the same records every day, switch to Cumulative / Replaceable.

4. Prevent rules backups on source formats with large numbers of records with the Disable Update Queue setting.

5. Review and update the Remap as of Date on older or longstanding source formats. When refreshing values, Slate looks through every value since the Remap as of Date for source fields mapped to prompt-based fields. As more and more data is loaded, this process takes longer to complete.

Active users, permissions, roles, and realms

Although the User table is not large relative to other Slate tables, it's checked constantly throughout the day as users access resources and permissions are checked.

The most impactful proactive maintenance involves reviewing active users and deactivating those no longer using Slate:

1. Go to Database → User Permissions.

2. Select Dormant Users.

3. Enter a date in the selector.

4. A list of users who have not logged in to Slate since the specified date appears. This list provides a good starting point for review.

Additionally, having a large number of custom permissions, roles, and realms can affect database performance. Deactivate any of these objects that are not actively being used.

Audit your rules

As your database expands, rules that may have once executed within the time allotted can slow as more records are included in their purview.

Use operators efficiently

• Limit NOT and OR operators in your rules.

• Generally filter in the positive (include only those records that satisfy a given filter criterion), rather than in the negative (exclude all records that don’t satisfy a filter criterion).

• That said, when deciding whether to use IN or NOT IN in a filter, choose the option that requires the fewest selections. For example, choosing NOT IN for a single selection is more efficient than using IN and selecting all but one or two possibilities.

• Use OR operators sparingly. If you find yourself relying on OR in your rules, consider breaking the rule into multiple rules and using an exclusivity group.

📖 Further reading: Rule efficiency, Exclusivity groups

Avoid custom formulas or SQL

Limit rules that rely on formulas or custom SQL. These rules are often the most computationally expensive.

While some functionality can only be achieved by formulas, use them sparingly.

✨ Tip: Locate rules that use formulas and Custom SQL by going to Rules → Check Rules and sorting the list by Type.

Move rules from the Slate Template Library to Configurable Joins

Rebuild any rules that rely on the Slate Template Library with Configurable Joins.

📖 Further reading: Getting started with Configurable Joins

Deactivate rules in Preview status

Keeping rules in the Preview status allows you to see how records will be affected before the rule is activated. While this is useful while you build the rule, accumulated errors in multiple partially-built rules in the Preview status can contribute to a backup.

Audit any rules in the Preview status to confirm they’re necessary.

Move away from deprecated and legacy features

Deprecated and legacy tools are no longer maintained nor supported, are less efficient, and rely on past functionality.

Moving to new Slate functionality ensures you’re using the most effective and efficient tools available.

📖 Further reading: Legacy and deprecated features