APM

Database Indexes

The database team profiles the database performance periodically and maintains indexes as needed. However, if there is a need for a new index, reach out to the database team.

Outer Joins

Outer joins result in full table scans. Therefore, you must avoid them wherever possible.

Example:

SELECT d.name, d.deptno, e.name, e.mgr, d.loc
FROM dept d
LEFT OUTER JOIN emp e
ON  d.deptno = e.deptno

OR Conditions

OR conditions can slow down the query performance in large tables. Consider using a UNION instead of OR to avoid a full table scan.

For example, the following code sample...

SELECT [MI_EQUIP000].[MI_EQUIP000_EQUIP_ID_C] "Equipment ID"
, [MI_EQUIP000].[MI_EQUIP000_EQUIP_SHRT_DESC_C] "Equipment Short Description"
, [MI_EQUIP000].[MI_EQUIP000_SITE_C] "Site"
, [MI_EQUIP000].MI_SITE_KEY "MI_SITE_KEY"
FROM [MI_EQUIP000]
WHERE [MI_EQUIP000].MI_SITE_KEY = 64262809275
OR [MI_EQUIP000].MI_ENTY_ID = 'My ID'

can be rewritten as follows:

SELECT [MI_EQUIP000].[MI_EQUIP000_EQUIP_ID_C] "Equipment ID"
, [MI_EQUIP000].[MI_EQUIP000_EQUIP_SHRT_DESC_C] "Equipment Short Description"
, [MI_EQUIP000].[MI_EQUIP000_SITE_C] "Site"
, [MI_EQUIP000].MI_SITE_KEY "MI_SITE_KEY"
FROM [MI_EQUIP000]
WHERE [MI_EQUIP000].MI_SITE_KEY = 64262809275

UNION ALL

SELECT [MI_EQUIP000].[MI_EQUIP000_EQUIP_ID_C] "Equipment ID"
, [MI_EQUIP000].[MI_EQUIP000_EQUIP_SHRT_DESC_C] "Equipment Short Description"
, [MI_EQUIP000].[MI_EQUIP000_SITE_C] "Site"
, [MI_EQUIP000].MI_SITE_KEY "MI_SITE_KEY"
FROM [MI_EQUIP000]
WHERE [MI_EQUIP000].MI_ENTY_ID = 'My ID'

The Asset Hierarchy Home Parameter

When Asset Hierarchy was created, there was no constant to represent the hierarchy home. Since the data type has changed over time and -1 is not intuitive, a new MetaSQL constant has been added. To make the queries more readable and protect you from future changes, use the new constant. (# :ahhome)

Example:

SELECT [MI_EQUIP000].[MI_EQUIP000_EQUIP_ID_C] "Equipment ID"
, [MI_EQUIP000].[MI_EQUIP000_EQUIP_SHRT_DESC_C] "Equipment Short Description"
, [MI_EQUIP000].[MI_EQUIP000_SITE_C] "Site"
, [MI_EQUIP000].ENTY_KEY "ENTY_KEY"
FROM [MI_EQUIP000]
WHERE (? :ah :id=ctx) = (# :ahhome)
OR [MI_EQUIP000].ENTY_KEY IN (? :ah :id=ctx)

Query Caching

Queries are cached to improve performance. However, non-parameterized queries can slow down the performance. Therefore, consider using parameters to narrow down query results.

For example, consider the following query that does not use any parameters to filter the query results:

string sql = string.Format("SELECT MI_EQUIP000.MI_EQUIP000_SITE_C FROM MI_EQUIP000 WHERE MI_EQUIP000.MI_EQUIP000_EQUIP_ID_C = {0}", equip_id);
cmd.CommandText = sql;
cmd.ExecuteReader();
MetaSQL:
var qrymgr = new QueryManager();

string sql = string.Format("SELECT MI_EQUIP000.MI_EQUIP000_SITE_C FROM MI_EQUIP000 WHERE MI_EQUIP000.MI_EQUIP000_EQUIP_ID_C = {0}", equip_id);

var result = qrymgr.ExecuteQuerySync(sessionId, sql);

In this case, every equip_id value creates a new query to the list of cached queries resulting in a slower performance.

Therefore, consider rewriting the query such that 10,000 equipment records are queried in each iteration, as follows:

// Build the sql
string sql = "SELECT MI_EQUIP000.MI_EQUIP000_SITE_C FROM MI_EQUIP000 WHERE MI_EQUIP000.MI_EQUIP000_EQUIP_ID_C = @equip_id";

// Set up the command
cmd.CommandText = sql;
cmd.Parameters.Add("@equip_id", equip_id);

// Execute the query
var reader = cmd.ExecuteReader();
MetaSQL:
var qrymgr = new QueryManager();

// Build the sql
string sql = "SELECT MI_EQUIP000.MI_EQUIP000_SITE_C FROM MI_EQUIP000 WHERE MI_EQUIP000.MI_EQUIP000_EQUIP_ID_C = (? :s id=equip_id)";

// Set up the query container.
QueryContainer qc = qrymgr.RetrieveQueryContainer(sessionId, sql);
qc.ParmContainer.ParmDesigns[0].ParmPrompts.Add(equip_id);

// Execute the query
var result = qrymgr.ExecuteQuerySync(sessionId, qc);

Parameter Size Declaration

In addition to setting up parameters (as described in the preceding example), if you want to improve performance further, consider setting the parameter size.

For example, equip_id is of the NVARCHAR data type, and hence, the size of the MIEQUIP000EQUIPIDC field is 255 characters. Therefore, you can set the size of this parameter in the qeury, as follows:

// Build the sql
string sql = "SELECT MI_EQUIP000.MI_EQUIP000_SITE_C FROM MI_EQUIP000 WHERE MI_EQUIP000.MI_EQUIP000_EQUIP_ID_C = @equip_id";

// Build the parameter
var param = cmd.CreateParameter();
param.ParameterName = "@equip_id";
param.SqlDbType = SqlDbType.NVarChar;
param.Size = 255;
param.Value = equip_id;

// Set up the command
cmd.CommandText = sql;
cmd.params.add(param);

// Execute the query
var reader = cmd.ExecuteReader();

Note: You cannot specify the parameter size in MetaSQL.

select fmly_id, fmly_caption_tx, count(*) rldf_count
from MI_FAMILIES fmly
join MI_REL_DEFINITIONS rlfa on ENFA_PRED_FMLY_KEY = fmly.FMLY_KEY or ENFA_SUCC_FMLY_KEY = fmly.FMLY_KEY
group by fmly_id, fmly_caption_tx
having count(*) > 10
order by rldf_count

Analytic Functions

Using analytic functions, you can achieve aggregation of data over multiple groups in a single query.

Example:

SELECT DISTINCT [MI_MRBIANAL].[MI_CRITANAL_RSK_CAT_C] "Risk Category"
, Min([MI_MRBIANAL].[MI_CRITANAL_PROB_OF_FAIL_UP_C])OVER ( PARTITION BY [MI_MRBIANAL].[MI_CRITANAL_RSK_CAT_C] ) "Min Prob of Fail"
, Max([MI_MRBIANAL].[MI_CRITANAL_PROB_OF_FAIL_UP_C])OVER ( PARTITION BY [MI_MRBIANAL].[MI_CRITANAL_RSK_CAT_C] ) "Max Prob of Fail"
, Avg([MI_MRBIANAL].[MI_CRITANAL_INSPE_PRIOR_UP_N])OVER ( PARTITION BY [MI_MRBIANAL].[MI_CRITANAL_RSK_CAT_C] ) "Avg Insp Priority"
, Count([MI_EQUIP000].ENTY_ID)OVER ( PARTITION BY [MI_MRBIANAL].[MI_CRITANAL_RSK_CAT_C] ) "# Assets"
FROM {MIR_HSRBICMP} 
 JOIN [MI_CCRBICOM] ON {MIR_HSRBICMP}.SUCC_ENTY_KEY = [MI_CCRBICOM].ENTY_KEY 
 JOIN {MIR_HSRBICMP} Has_RBI_Components1 ON [MI_CCRBICOM].ENTY_KEY = Has_RBI_Components1.SUCC_ENTY_KEY 
 JOIN {MIR_RBICRAN} ON [MI_CCRBICOM].ENTY_KEY = {MIR_RBICRAN}.PRED_ENTY_KEY 
 JOIN [MI_EQUIP000] ON Has_RBI_Components1.PRED_ENTY_KEY = [MI_EQUIP000].ENTY_KEY 
 JOIN [MI_MRBIANAL] ON {MIR_RBICRAN}.SUCC_ENTY_KEY = [MI_MRBIANAL].ENTY_KEY
ORDER BY [MI_MRBIANAL].[MI_CRITANAL_RSK_CAT_C] Asc

We support the following analytic functions:

• SUM
• MAX / MIN
• AVG
• LEAD / LAG
• COUNT
• ROW_NUMBER
• CUME_DIST
• PERCENTRANK, PERCENTILEDISC, PERCENTILE_CONT
• NTILE

For more information, refer to Analytic Functions.

Case Statements

Simple:

SELECT [MI_ACTION].[MI_ACTION_ID_C] "Action ID"
, [MI_ACTION].[MI_ACTION_SHORT_DESC_C] "Name"
, CASE YEAR([MI_ACTION].[MI_ACTION_TARGE_COMPL_DATE_CHR]) 
     WHEN '2020' THEN 'Next Year'
     WHEN '2019' THEN 'This Year'
     WHEN '2018' THEN 'Last Year'
     WHEN '2017' THEN '2 Years Ago'
  ELSE 'Other' END "Year Due"
, [MI_ACTION].[MI_ACTION_RESOURCE_COST_N] "Cost"
, [MI_ACTION].[MI_ACTION_ANNUAL_COST_N] "Annual Cost"
, [MI_ACTION].[MI_ACTION_SHUTDOWN_L] "Shutdown Required"
, [MI_ACTION].[MI_ACTION_STATUS_C] "Status"
FROM [MI_ACTION]
ORDER BY [MI_ACTION].[MI_ACTION_TARGE_COMPL_DATE_CHR] Desc

Standard:

SELECT [MI_ACTION].[MI_ACTION_ID_C] "Action ID"
, [MI_ACTION].[MI_ACTION_SHORT_DESC_C] "Name"
, CASE
     WHEN YEAR([MI_ACTION].[MI_ACTION_TARGE_COMPL_DATE_CHR]) = '2020' THEN 'Next Year'
     WHEN YEAR([MI_ACTION].[MI_ACTION_TARGE_COMPL_DATE_CHR]) = '2019' THEN 'This Year'
     WHEN YEAR([MI_ACTION].[MI_ACTION_TARGE_COMPL_DATE_CHR]) = '2018' THEN 'Last Year'
     WHEN YEAR([MI_ACTION].[MI_ACTION_TARGE_COMPL_DATE_CHR]) = '2017' THEN '2 Years Ago'
  ELSE 'Other' END "Year Due"
, [MI_ACTION].[MI_ACTION_RESOURCE_COST_N] "Cost"
, [MI_ACTION].[MI_ACTION_ANNUAL_COST_N] "Annual Cost"
, [MI_ACTION].[MI_ACTION_SHUTDOWN_L] "Shutdown Required"
, [MI_ACTION].[MI_ACTION_STATUS_C] "Status"
FROM [MI_ACTION]
ORDER BY [MI_ACTION].[MI_ACTION_TARGE_COMPL_DATE_CHR] Desc

Correlated Subqueries

Correlated subqueries allow you to write a child query that retrieves fields from a parent query. For more information, refer to https://learn.microsoft.com/en-us/sql/relational-databases/performance/subqueries?view=sql-server-2017#correlated.