Reference Information: Query Results

By default, the values displayed in each column in the results are the values in the fields of records included in the results. You can modify the default behavior, however, if you want all cells in a column to display the same text.

To modify the values displayed in a column, you will need to modify the value in the Field cell for the appropriate column in the grid in the Conditions section of the Design workspace.

You can run Select queries in two different modes: formatted and unformatted.

Details

• Formatted Mode: Causes the results to display formatted values rather than stored values. In other words, the values displayed in the results will be formatted based on any format rules or criteria defined for the fields included in the query.
• Unformatted Mode: Causes the results to display stored values rather than formatted values. In other words, any format rules that have been defined will not be applied, and the results will display values exactly as they exist in the database.
  Note: When you run a query in unformatted mode, the results will still display formatted date values. Date values will always be displayed in the local time for the user; however, if you export the unformatted query result set to a dataset, the exported date values will be unformatted.

The alias for a field specifies how it will be labeled in the query results and in any report or graph that is created from the query. When you add fields to the grid in the Conditions section, the alias is set automatically to the field caption. You can modify the alias.

Details

When defining aliases, keep in mind the following considerations:

• Within a given query, each alias must be unique. If you specify an alias that has already been defined for another column in the same query, a number will be appended to the end of each duplicate alias to distinguish them from the other aliases in the query. For example, if you specify Asset as the alias in four columns, the aliases would be changed to:
  • Asset
  • Asset0
  • Asset1
  • Asset2

  Each duplicate alias will be numbered in this way using the next available number (i.e., 3, 4, 5, and so on).

• The alias cannot exceed 27 characters in length (including spaces). You will not be able to enter more than 27 characters into any Alias cell.

You can specify that results of a query return only unique records. For example, if you design a query to return all types of Air Cooled Heat Exchangers, you might get 10 results, but there might be only two different types of Air Cooled Heat Exchangers within those results, such as Air Cooled Heat Exchanger and Cooling Tower. If you specify that the query return only records where the asset type is unique, you will see only two results: one Air Cooled Heat Exchanger result and one Cooling Tower result.

Uniqueness is defined at the query level, not the field level. This means that if the same query that returns two results with different asset types is also designed to return the asset status, and you specify that the query return only unique records, you will see records where the asset type and status are unique. You could see results where there are active Air Cooled Heat Exchangers, Inactive Air Cooled Heat Exchangers, and active Cooling Towers in the database.

Query performance is influenced by a number of factors, including hardware efficiency (e.g., how fast the Server machines are), the system's workload at the time you run the query (e.g., how many users are logged in and making requests), and the efficiency of the database (e.g., how well the database is being maintained). As you can see, many of the factors that influence query performance are beyond your control. For example, you cannot control how many other users are currently using the system.

Query performance is also affected by a number of factors that you can control, including how you construct the query and the options that you choose for running it. The following Details sections discuss things that you can do to maximize the performance of queries.

Limiting the Size of the Query

Generally, the more data that you attempt to retrieve with a query, the longer the query will take to run. For example, a query that retrieves data for all fields in the Recommendation family (which has many subfamilies) will take longer to run than a query that returns only a few fields from the APM General Recommendation family (A subfamily of the Recommendation family).

Knowing this, you can improve query performance by limiting the amount of data that you return in the query results. For example:

• Always query on the lowest-level family possible. For example, instead of querying on a family, query on individual subfamilies. If you need to query on more than one subfamily, you might want to create more than one query.
• Only include in your query results the fields that you actually need. Instead of returning the information for all fields in a given family, limit your results to only the specific fields that you want to analyze.
• Use criteria to limit results to a specific set of records. For example, if you are interested only in the pumps associated with a given manufacturer, limit your query results by applying criteria to the Manufacturer field.

Running Select Queries in Unformatted Mode

APM provides two output modes for running Select queries: formatted mode and unformatted mode.

Because unformatted mode returns results without taking the extra step of applying formatting before displaying results, running a query in unformatted mode can be more efficient than running the same query in formatted mode. This performance improvement is particularly significant when you run a query that includes many formatted fields.

Other Recommendations

• Avoid outer joins because they can impact the performance.
• Avoid using OR. Instead use UNION for better performance.
• If using Like with a leading wildcard character on an indexed field, the index will not be used.
• Avoid unnecessary joins. If no fields are used from a family, do not add the family to the query.
• Avoid Distinct when possible.
• When writing aggregate queries, use Where and Having correctly; Where is used to filter results before calculating the aggregate, whereas Having is used to filter out the results based on the aggregation.

You might want to limit the number of results that are returned for certain queries such that only the first n number of records are returned based on a specific set of criteria.

There are two URL routes associated with queries: query and qdetail. The following table describes the various paths that build on the route, and the elements that you can specify for each.