Best practices for the Service Management query type

Set the  Jdbc-Row-Limit parameter.

If a report uses complex queries and data structure and is used to fetch a dump of transactional data for a specific period, the report might take time to load. 

We recommend the following guidelines to prevent this issue:

• Define a period as one month or one week and enable the broadcast.
• Avoid running complex queries or generating transactional data dump reports directly from the UI.
• Schedule broadcasts of the report during off-hours with a specified time range.

Limit the use of the SELECT DISTINCT operator.

The SELECT DISTINCT operator is used to select unique values in a column and thus eliminate duplicate values. However, it can be resource-intensive on large datasets, as the database needs to scan and remove duplicates.

It has the following syntax: 

SELECT DISTINCT column_name FROM table_name;

Add missing indexes.

Table indexes in databases help retrieve information faster and more efficiently. Indexing makes columns faster to query by creating pointers to where data is stored within a database. Indexes provide a structured way to access data, allowing the database engine to locate the rows that match specific criteria more quickly. This indexing is especially beneficial for queries with filtering conditions in the WHERE clause.

Add appropriate indexes to the relevant columns in join conditions and filter WHERE clauses to provide these benefits:

• Improve the performance by speeding up matching rows between the tables.
• Decrease the time required to run join operations.

Use SELECT fields instead of SELECT*.

The SELECT statement is used to retrieve data from the database.

Disadvantages of using SELECT*:

• Unnecessary I/O cycles: When you use SELECT*, you retrieve all columns from a table. However, you may only need specific columns for your query. Fetching unnecessary data from the database pages consumes multiple I/O cycles. 
  Also, instead of retrieving data directly from index pages, SELECT* reads the data from regular data pages, which is less efficient. Therefore, specify only the columns you actually need in your SELECT statement to optimize query performance.
• Increased network traffic: SELECT* returns more data that requires more network bandwidth. 
• Consumes more application memory: Due to this increase in data, the application might require more memory to store unnecessary and unused data from the database.
• Dependency on order of columns on result set: If you use the SELECT* query in your application that depends on the column order, the order of the result changes when you add a new column or change the column order.
• Conflict in the JOIN Query: If you use SELECT* in the JOIN query when multiple tables have columns with the same name, conflicts can arise.

We recommend using the explicit column list in a SELECT query instead of a * wild card. This practice not only improves performance but also makes your query more explicit.

Define filters by using the WHERE clause instead of the HAVING clause.

The primary goal of query tuning is efficiency, and clauses are calculated earlier than statements in the order of operations. This practice makes it more efficient for defining filters because it limits the number of records retrieved from the database. Therefore, WHERE restricts the result set before returning rows and HAVING restricts the result set after retrieving all the rows. Therefore, WHERE is faster on SQL Standard compliant DBMSs.

HAVING clauses should be used to apply conditions on group functions; otherwise, these conditions can be moved into the WHERE condition. For example. if you want to restrict your query to groups with COUNT(INC) > 10, you will put the condition into a HAVING clause because the HAVING clause acts on the groups, not individual rows.  

Keep wildcards at the end of phrases. 

A wildcard is used with the LIKE operator. The LIKE operator is used with a WHERE clause to search for a specified pattern. Pairing a leading wildcard with an ending wildcard checks for all records matching between the two wildcards.

Let’s understand this concept with the help of an example.  

The HPD:Help Desk contains two columns: Incident Number and Assignee. There are two different Assignees named Liam and William. 

• Select `HPD:Help Desk`.`Incident Number`, `HPD:Help Desk`.`Assignee` from `HPD:Help Desk` where `HPD:Help Desk`.`Assignee` like ‘%liam%’; 
• Select `HPD:Help Desk`.`Incident Number`, `HPD:Help Desk`.`Assignee` from `HPD:Help Desk` where `HPD:Help Desk`.`Assignee` like ‘liam%’; 

In the first case, when you search %liam%, you get both results: Liam and William. In the second case, a search for liam% returns only Liam.

Consider the placement of the wildcard either at the end or start of the search term to enhance the efficiency of the search.

Sort operations.

Sorting can be a resource-intensive operation. 

Verify that the sort operations are necessary for your use case, and if possible, consider creating indexes to avoid explicit sorting.  

Use nested loops.

Depending on the size of the tables involved, the usage of nested loops can be resource-intensive.

Make sure that the tables involved in the nested loop joins are appropriately indexed.

Watch memory usage.

Monitor the memory usage during the sort operations. If the available memory is insufficient, PostgreSQL might spill to the disk, causing a significant fall in its performance.

Configure the work_mem parameter to prevent memory shortage.

Add partitions to tables.

Designers should maintain awareness of the partitions they have created.

Consider partitioning the tables to improve query performance depending on the size of the tables involved. 

Provide correct formatting for the query.

Provide the correct formatting while writing a query to enhance readability and simplify review and troubleshooting.

Some of the rules for formatting a query: 

• Add each statement in the query on a new line. 
• Add SQL keywords in the query in uppercase. 
• Use CamelCase capitalization in the query and avoid underscore. 

For example:

SELECT DISTINCT `HPD:Help Desk`.`Incident Number`, `HPD:Help Desk`.`Priority` 

FROM `HPD:Help Desk` INNER JOIN `HPD:Help Desk Assignment Log` 

ON `HPD:Help Desk`.`Incident Number` = `HPD:Help Desk Assignment Log`.`Incident Number` 

WHERE `HPD:Help Desk`.`Status`= ‘Resolved’; 

Remove correlated subqueries if not required.

A correlated subquery is a nested query that depends on the outer query for its values. If there are many users in the database, the correlated subquery is inefficient and takes a lot of time as it will need to run many times. In that case, an inner join is more efficient. 

Example: This is a query that displays the CustomerID of the customers that have currently ordered products using a correlated subquery. 

SELECT CustomerID 

FROM Customers 

WHERE EXISTS (SELECT * FROM Orders WHERE Customers.CustomerID = Orders.CustomerID); 

It is better to use the inner join as follows to obtain the same result:

SELECT DISTINCT Customers.CustomerID 

FROM Customers INNER JOIN Orders 

ON Customers.CustomerID = Orders.CustomerID; 

We recommend avoiding a correlated subquery if all rows are needed from the database. However, they are sometimes inevitable and must be used.

Avoid functions in predicates.

Functions in SQL are used to perform specific actions. However, they are inefficient as they do not allow the usage of indexes that slow the execution time of the query.

We recommend avoiding functions in a query as much as possible to ensure its optimization. 

Example: This is a query that displays the details of the products whose name starts with 'Sha'. 

SELECT * 

FROM Products 

WHERE SUBSTR(ProductName, 1, 3) = 'Sha'; 

It is better to avoid the function and use the LIKE clause instead to obtain the same result. 

SELECT * 

FROM Products 

WHERE ProductName LIKE 'Sha%';