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
Technical Criterion
Efficiency - SQL and Data Handling Performance
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.