How to optimize the process of searching on the KACE SMA to avoid performance issues (4266562)

It may be noted that the KACE appliances utilize SQL database to store and retrieve data. The following are general search tips and methodology that could be applied to nearly any database-driven toolsets.

This article is intended to suggest best practices and is not a comprehensive solution to performance concerns. If performance concerns are somewhat continuous, please contact support or engage with our services team for an Appliance Health Check. For sporadic issues that do not seem to have a time-based cause (such as a sub-optimal scheduled report, script, or ticket rule), the following may be beneficial.

Slow searches can be a common issue which, in many cases, is related to the amount of data being searched and the optimization of the search being performed.

Depending upon which search mechanism is in use, varying levels of performance degradation may become noteworthy.

Understanding key search methodologies

The following are the three most common search tools used on a regular basis; ordered from the least to the most efficient one.

For the purpose of this topic, images will be mostly from the KACE SMA Device Inventory page, however, the information provided below is applicable to nearly any appliance page.

1. Global Search

Global Search can be used to locate information across the entire KACE appliance database. This is the least efficient way to search as it uses wildcard searches nearly throughout every field and module of the appliance. Though this type of search may bring up the information that is needed, it is also going to take the longest and could potentially affect performance for not just the user doing the search but other appliance users and services running at the time. Global Searches should typically be avoided unless other more-efficient options have been exhausted.

If performing a global search for KYGJ876, the search must look for that string in all character positions, in countless fields, for all data - including Tickets, Assets, Inventory, Reports, etc. This search method assumes a wildcard in the beginning and end of any search string, which makes it incredibly inefficient.

    2. Page/List Search

Page-level List Search allows searching for information on the current page. This option is more efficient than the Global Search option, but it still looks for wildcards within the selected data table.

For example, searching the Device Inventory list for KYGJ876 will prompt a search on every field within Inventory and, though it is more efficient than a global search, it is still not as targeted as it could be.

    3. Advanced Search

Page-level Advanced Search allows searching for information on the current page using various combinations of criteria. Advanced page-level Search is available on most list pages, and can be the most efficient mechanism for performing a search if used properly.

How to use Advanced Search:

• Specify the search criteria using the available fields
• To add another criteria row, click Add line.
• To add a subset of rules, select AND or OR from the operator drop-down list at the right of the criteria, then click Add Group. It is not advisable to mix AND & OR statements. This can lead to less-predictable results. If AND-OR need to be used together, it is recommended to do so by implementing the Group option to keep them together. 

General tips for getting the most out of an Advanced Search (Also applies to Smart Labels/Notifications/Custom Views):

As an example, a search for a user called “Barry Ellison” will be used.

• Adjust the criteria as needed until the results are as expected.
• In larger environments, it is advisable to consider using a development/test environment to test SQL Queries (Advanced searches, Reports, Smart Labels, etc.) prior to introducing them to a production environment.
• Searches that might be useful would typically be performed as a Smart Label, Report, or Custom View.
• Opt to search for information at the page-level. For more effective results, use the advanced search option and if not available, page-level list search should be the next best pick.

The examples below will be for Advanced search.

Avoid “CONTAINS” (or Does not Contain) as an operator

Whenever possible, avoid using the LIKE pattern in the following way:

This SQL would look similar to this:

SELECT * FROM users WHERE name LIKE '%bar%';

The use of the % wildcard at the beginning of the LIKE pattern (KACE UI operator: CONTAINS) will prevent the database from using a suitable index. Since the system does not know what the beginning of the name column is, it will have to perform a full table scan anyway. In many cases, this will slow the query execution and potentially turn out undesired results. In the example above, the search result would include Barry Ellison, as well as anyone containing the string BAR in any part of their name such as Jabari Johnson, Bart Windstone, Carol Barton, Baron Westinghouse, Barbara Baker, and so on

ENDS WITH is slightly better from an efficiency point-of-view, but not much

The query above can be rewritten in the following way:

This SQL would look similar to this:

SELECT * FROM users WHERE name LIKE '%bar';

The performance may be enhanced over a CONTAINS selection only slightly; always consider whether a wildcard character at the beginning is really essential.

In the example above, the query would not return the result ‘Barry Ellison’, given the name does not end with BAR.

BEGINS WITH is an improvement over the previous examples

This SQL would look similar to this:

SELECT * FROM users WHERE name LIKE 'bar%';

The performance will be enhanced a bit more over the previous examples. In the example above, the query result would include ‘Barry Ellison’, but also anyone whose name starts with BAR, such as Bart Windstone, Baron Westinghouse, Barbara Baker, and so on.  Users that contain the keyword ‘BAR’ elsewhere in their name such as Jabari Johnson and Carol Barton would not be shown as a result from this query.

Mathematical operators such as =  !=    > are the most efficient.
If the query can be rewritten in the following way:

This SQL would look similar to this:

SELECT * FROM users WHERE name = 'Barry Ellison';

This option should be always chosen not only for performance, but also for accuracy. In the example above, we would get back Barry Ellison, and no one else.

 Admittedly, not everything can be written this way. Nonetheless, understanding how the different search options work may help improve the quality of the results obtained from a search, as well as, its efficiency.

Understand the differences in how to use the operators to get the desired results

Does not equal (!=) and DOES NOT BEGIN WITH would return different results in many cases. The example below shows a search for computers that do not have the 9.0 KACE Agent.

In this example the operator != (Does not equal) is used:

None of the devices have an agent version that equals 9.0 (They are 9.0.167 and 9.0.181), so all devices are returned. While this operator is the most efficient, the criteria was not complete; instead of “9.0” the correct criteria would be ‘Agent Version != 9.0.167’ or ‘Agent Version != 9.0.181’. When in doubt- look up a device that does have what is needed, and use the exact data that is present.

Modifying that search to DOES NOT BEGIN WITH returns devices not showing an agent version (such as agentless devices) or other versions like 8.1.1 or 9.1.20

Grouped criteria example:

In this example, a search for all 9.0.x agents that belong to a specific domain OR are Windows 10 Laptops is done. Group 1 defines three ways a device may be detected as belonging to the domain, while Group 2 defines the chassis and Operating System criteria. Notice that the resulting list of devices should fit one or both sets of criteria.

This same search without groups could yield very different and incorrect results.