Click or drag to resize

SA0147 : The DML statement has complexity above the threshold

The topic describes the SA0147 analysis rule.

Message

The DML statement has complexity above the threshold

Description

The rule calculates the complexity of each SELECT,INSERT,UPDATE,DELETE and MERGE statement and reports if it is above a threshold value.

The rule gives a way to measure the complexity of a single SQL statement based on the existence of specific elements in it.

The following query elements are considered when the query complexity is calculated:

  • Each joined table source

  • Each table, query and join hint

  • Existence of GROUP BY clause

  • Existence of ORDER BY clause

  • Existence of HAVING clause

  • Existence of COMPUTE or COMPUTE BY clause

  • Each UNION clauses

  • Each Common Table Expression

  • Each boolean expression in WHERE,JOIN and HAVING clauses

  • The target table in INSERT,UPDATE,DELETE and MERGE statements

You can set the complexity for each of the above elements using the rule parameters.

Scope

The rule has a Batch scope and is applied only on the SQL script.

Parameters
NameDescriptionDefault Value
ComplexityThreshold

The complexity threshold value which will make the rule report a warning message.

20

JoinComplexity

The complexity value assigned for each joined table in the statement.

1

HintComplexity

The complexity value assigned for each hint found in the statement.

1

GroupByComplexity

The complexity value assigned for the GROUP BY caluse.

1

OrderByComplexity

The complexity value assigned for the ORDER BY caluse.

1

HavingComplexity

The complexity value assigned for the HAVING clause.

1

ComputeComplexity

The complexity value assigned for the COMPUTE and COMPUTE BY clauses.

1

UnionComplexity

The complexity value assigned for each UNION, INTERSECT and EXCEPT in the statement.

1

CteComplexity

The complexity value assigned for each Common Table Expression.

1

BooleanExpressionComplexity

The complexity value assigned for each boolean expression.

1

TargetComplexity

The complexity value assigned for the target table of the SELECT INTO, INSERT, UPDATE, DELETE and MERGE statements.

1

Remarks

The rule does not need Analysis Context or SQL Connection.

Categories

Design Rules

Additional Information

There is no additional info for this rule.

Example Test Script
SQL
 1--- Query Hints ---
 2SELECT * 
 3FROM Sales.Customer AS c
 4INNER JOIN Sales.vStoreWithAddresses AS sa 
 5    ON c.CustomerID = sa.BusinessEntityID
 6WHERE TerritoryID = 5
 7OPTION (MERGE JOIN);
 8
 9SELECT *
10FROM HumanResources.Employee AS e1
11UNION
12SELECT *
13FROM HumanResources.Employee AS e2
14OPTION (MERGE UNION) 
15
16SELECT ProductID, OrderQty, SUM(LineTotal) AS Total
17FROM Sales.SalesOrderDetail
18WHERE UnitPrice < $5.00
19GROUP BY ProductID, OrderQty
20ORDER BY ProductID, OrderQty
21OPTION (HASH GROUP, FAST 10); 
22
23DELETE FROM Sales.SalesPersonQuotaHistory 
24FROM Sales.SalesPersonQuotaHistory AS spqh
25    INNER LOOP JOIN Sales.SalesPerson AS sp
26    ON spqh.BusinessEntityID = sp.BusinessEntityID
27WHERE sp.SalesYTD > 2500000.00;
28
29SELECT SalesPersonID, COUNT(SalesOrderID) AS TotalSales, SalesYear 
30INTO NewTable1
31FROM Sales 
32WHERE SalesPersonID IN (1,2,3,4,5) or SalesPersonName like '%rov'
33GROUP BY SalesYear, SalesPersonID
34ORDER BY SalesPersonID, SalesYear

Analysis Results

No violations found.

See Also

Other Resources