Avoid Artifacts with High Depth of Nested Subqueries | CAST Appmarq

Avoid Artifacts with High Depth of Nested Subqueries


Rule Definition
Subqueries can possibly cause performance issues. High depth of nested subqueries is a greater source of performance issues.

Remediation
Review the source code and refactor in order to limit the number of suqueries.
Violation Code Sample
SELECT column-names
      FROM table-name1
     WHERE value IN (SELECT column-name FROM table-name2 
     WHERE value IN (SELECT column-name  FROM table-name3 
     WHERE value IN (SELECT column-name  FROM table-name4
     WHERE value IN (SELECT column-name   FROM table-name5 
     WHERE condition))))
Reference
http://it-cisq.org/ CISQ: ASCPEM-PRF-04
Related Technologies
DB2 Server Forms Visual Basic .Net Cobol PL/SQL JEE Microsoft T-SQL Sybase T-SQL DB2 z/OS SHELL SQLScript

Health Factor

  Total Quality Index

Technical Criterion
CWE-1049 - Excessive Data Query Operations in a Large Data Table

About CAST Appmarq

CAST Appmarq is by far the biggest repository of data about real IT systems. It's built on thousands of analyzed applications, made of 35 different technologies, by over 300 business organizations across major verticals. It provides IT Leaders with factual key analytics to let them know if their applications are on track.

Benchmark Statistics

Global Compliance

99.92%

Total Violations
3,743
Total Opportunities
4,832,048
Average Violations / App.
1.53
The compliance score represents 1 minus the ratio between the number of times a rule has been violated compared to the number of opportunities in a set of applications that the rule could have been violated.

Industry Insights

Select from drop-down

99.50%

Insurance

99.62%

Software ISV

99.72%

Want more stats? Register now!