Use case - CAST app

Introduction

The comparison has been done between PL/SQL Analyzer 8.3.8 and SQL Analyzer 3.4.0-beta3 (AIP 8.3.8 + datacolumnaccess 1.0.0-beta2), on the attached source code . CAST triplet is an use case 100% SQL, based on  CAIP 8.2.16 SQL sources, and it was selected to make a focus on performance.

Execution time

PL/SQL Analyzer SQL Analyzer

Status: Execution succeeded

Start: Fri Jun 21 11:37:56 CEST 2019
OS Version: Windows 8.1 (version 6.3)
Machine name: DOPLAP
Software Version: CAST 8.3.8 ( Build 1811 )
Task: Take a snapshot of the application
Total duration: 14m14s
Nb of Computed Quality Rules: 104


-- Statistics for application CastOnCast --
Analysis duration: 7m00s
Nb of modules: 1
Nb of active analysis units: 4
Nb of inactive analysis units: 0
Total nb of analysis units: 4
Nb of saved objects: 20359

Status: Execution succeeded

Start: Tue Jan 21 16:42:13 CET 2020
OS Version: Windows 8.1 (version 6.3)
Machine name: DOPLAP
Software Version: CAST 8.3.8 ( Build 1811 )
Task: Take a snapshot for each application
Total duration: 26m58s
Nb of Computed Quality Rules: 100


-- Statistics for application CastOnCast --
Analysis duration: 22m39s
Nb of modules: 1
Nb of active analysis units: 1
Nb of inactive analysis units: 0
Total nb of analysis units: 1
Nb of saved objects: 21910

TQI

<query>

analyzer tqi
SQL 2.49159947935289
Oracle 2.40230552230396

Number of data functions and transaction entry points per APPLICATION

<query>

analyzer cnt_datafunctions cntdfp_functionpoints cntdf_calibratedfunctionpoints cnt_transactions cnttfp_functionpoints cntt_calibratedfunctionpoints
SQL 1609 10013 10013 0 0 0
Oracle 1602 9924 9924 0 0 0

Number of objects by type

<query>

objecttype numberofobjects Compared with Oracle
Column 9129 =
Function 2812 =
Indexes Primary Keys Unique Constraints 1543

-44 

Duplicated PKs in Oracle
Package 6 =
Procedure 157 =
Schema 4 =
Synonym 10 =
Table 1692 =
Trigger 4 =
View 113 =
Check Constraint 4782 Object type not implemented on SQL
Instance 1 Object type not implemented on SQL
Sequence 29 Object type not implemented on SQL

<query>

kindoflink callertype calleetype Compared with Oracle
ACCESS Function Column 8 *)
ACCESS Function Table Detected only by PL/SQL Analyzer
ACCESS Procedure Column -14
ACCESS View Column -14
CALL Function Function -18
CALL Function Procedure =
CALL Function Trigger Detected only by SQL Analyzer
CALL Procedure Function 4 *)
CALL Procedure Procedure =
CALL Procedure Trigger Detected only by SQL Analyzer
CALL View Function =
USE Function Synonym Detected only by SQL Analyzer
USE Function Table 104 *)
USE Function View =
USE Procedure Synonym Detected only by SQL Analyzer
USE Procedure Table 30 *)
USE Procedure View =
USE Trigger Synonym Detected only by SQL Analyzer
USE View Synonym Detected only by SQL Analyzer
USE View Table =
USE View View =

*) We detect more than Oracle Analyzer

Compare equivalent quality rules

<query>

qualityrulename objecttype Compared with Oracle
Always define column names when inserting values Function Exists only on SQL Analyzer
Always define column names when inserting values Procedure Exists only on SQL Analyzer
Average Coupling Artifacts Function 3
Average Coupling Artifacts Procedure =
Average Coupling Artifacts View =
Average Reuse by Call Function -5
Average Reuse by Call Procedure -3
Average Reuse by Call Trigger -3
Average Reuse by Call View =
Average Size Artifacts Function -682
Average Size Artifacts Procedure Detected only by PL/SQL Analyzer
Average Size Artifacts View Detected only by PL/SQL Analyzer
Avoid SELECT *" queries" Function -12
Avoid SELECT *" queries" Procedure -2
Avoid Artifacts with High Cyclomatic Complexity Function -32
Avoid Artifacts with High Cyclomatic Complexity Procedure -4
Avoid Artifacts with High Depth of Code Function -3
Avoid Artifacts with High Depth of Nested Subqueries Function Detected only by SQL Analyzer
Avoid Artifacts with High Depth of Nested Subqueries Procedure Detected only by SQL Analyzer
Avoid Artifacts with High Depth of Nested Subqueries View Detected only by SQL Analyzer
Avoid Artifacts with High Essential Complexity Function 610
Avoid Artifacts with High Essential Complexity Procedure 20
Avoid Artifacts with High Essential Complexity View Detected only by SQL Analyzer
Avoid Artifacts with High Fan-In Function =
Avoid Artifacts with High Fan-In Procedure =
Avoid Artifacts with High Fan-In Trigger Detected only by SQL Analyzer
Avoid Artifacts with High Fan-In View =
Avoid Artifacts with High Fan-Out Function -4
Avoid Artifacts with High Fan-Out Procedure =
Avoid Artifacts with High Integration Complexity Function 36
Avoid Artifacts with High Integration Complexity Procedure -2
Avoid Artifacts with High RAW SQL Complexity Function Detected only by PL/SQL Analyzer
Avoid Artifacts with High RAW SQL Complexity Procedure Detected only by PL/SQL Analyzer
Avoid Artifacts with SQL statement including subqueries Function -345
Avoid Artifacts with SQL statement including subqueries Procedure -23
Avoid Artifacts with SQL statement including subqueries View =
Avoid Artifacts with a Complex SELECT Clause Function The rationale describe a client server context, there is no value to apply-it on SQL code
Avoid Artifacts with a Complex SELECT Clause Procedure The rationale describe a client server context, there is no value to apply-it on SQL code
Avoid Artifacts with high Commented-out Code Lines/Code Lines ratio Function -114
Avoid Artifacts with high Commented-out Code Lines/Code Lines ratio Procedure 2
Avoid Artifacts with high Commented-out Code Lines/Code Lines ratio View Detected only by PL/SQL Analyzer
Avoid Artifacts with lines longer than X characters Function -185
Avoid Artifacts with lines longer than X characters Procedure -7
Avoid Artifacts with lines longer than X characters View -5
Avoid Artifacts with queries on too many Tables and/or Views Function -1
Avoid Artifacts with queries on too many Tables and/or Views Procedure -1
Avoid Artifacts with queries on too many Tables and/or Views View Detected only by SQL Analyzer
Avoid Artifacts with too many parameters Function 6
Avoid Cursors inside a loop Function -7
Avoid SQL queries with implicit conversions in the WHERE clause Function Exists only on PL/SQL Analyzer
Avoid SQL queries with implicit conversions in the WHERE clause Procedure Exists only on PL/SQL Analyzer
Avoid SQL queries with implicit conversions in the WHERE clause View Exists only on PL/SQL Analyzer
Avoid Tables not using referential integrity Table =
Avoid Tables with more than 20 columns on an OLTP system Table =
Avoid Tables without Primary Key Table =
Avoid Too Many Copy Pasted Artifacts Function 342
Avoid Too Many Copy Pasted Artifacts Procedure -17
Avoid Too Many Copy Pasted Artifacts View 1
Avoid artifacts having recursive calls Function 2
Avoid artifacts having recursive calls Procedure =
Avoid empty catch blocks Function Exists only on SQL Analyzer
Avoid empty catch blocks SQL Analyzer Project Exists only on SQL Analyzer
Avoid empty catch blocks Trigger Exists only on SQL Analyzer
Avoid empty catch blocks Universal Project Exists only on SQL Analyzer
Avoid exists and not exists independent clauses Function Detected only by SQL Analyzer
Avoid exists and not exists independent clauses Procedure Detected only by SQL Analyzer
Avoid explicit comparison with NULL Function Exists only on SQL Analyzer
Avoid having multiple Artifacts inserting data on the same SQL Table Table 683
Avoid having multiple Artifacts updating data on the same SQL Table Table 29
Avoid having multiple artifacts deleting data on the same SQL table Table 2302
Avoid large Artifacts - too many Lines of Code Function =
Avoid large Artifacts - too many Lines of Code Procedure =
Avoid large Artifacts - too many Lines of Code View 1
Avoid large Tables - too many columns Table =
Avoid long Table or View names Table =
Avoid long Table or View names View =
Avoid non-SARGable queries Function Exists only on SQL Analyzer
Avoid non-SARGable queries Procedure Exists only on SQL Analyzer
Avoid non-SARGable queries View Exists only on SQL Analyzer
Avoid non-indexed SQL queries Function =
Avoid non-indexed SQL queries Procedure 32
Avoid non-indexed SQL queries View 9
Avoid orphaned synonyms Synonym =
Avoid queries using old style join convention instead of ANSI-Standard joins Function 1
Avoid queries using old style join convention instead of ANSI-Standard joins Procedure =
Avoid queries using old style join convention instead of ANSI-Standard joins View =
Avoid redundant indexes Table 2
Avoid too many Indexes on one Table Table =
Avoid triggers, functions and procedures with a very low comment/code ratio Function 95
Avoid triggers, functions and procedures with a very low comment/code ratio Procedure 4
Avoid triggers, functions and procedures with a very low comment/code ratio Trigger =
Avoid undocumented Triggers, Functions and Procedures Function =
Avoid undocumented Triggers, Functions and Procedures Procedure =
Avoid undocumented Triggers, Functions and Procedures Trigger =
Avoid unreferenced Functions Function -1
Avoid unreferenced Functions Procedure =
Avoid unreferenced Tables Table -11
Avoid unreferenced views View =
Avoid using nullable" Columns except in the last position in a Table" Table Exists only on PL/SQL Analyzer
Avoid using GOTO statement Function =
Avoid using GOTO statement Procedure =
Avoid using SQL queries inside a loop Function 3
Avoid using SQL queries inside a loop Procedure =
Avoid using dynamic SQL in SQL Artifacts Function =
Avoid using dynamic SQL in SQL Artifacts Procedure =
Avoid using the GROUP BY clause Function -2
Avoid using the GROUP BY clause Procedure =
Column references should be qualified Function Exists only on SQL Analyzer
Column references should be qualified Procedure Exists only on SQL Analyzer
Column references should be qualified View Exists only on SQL Analyzer
Cyclomatic Complexity Distribution Function -335
Cyclomatic Complexity Distribution Procedure 2
Cyclomatic Complexity Distribution Trigger =
Cyclomatic Complexity Distribution View Detected only by SQL Analyzer
DISTINCT should not be used in SQL SELECT statements Function Exists only on SQL Analyzer
DISTINCT should not be used in SQL SELECT statements Procedure Exists only on SQL Analyzer
DISTINCT should not be used in SQL SELECT statements View Exists only on SQL Analyzer
Do not mix ANSI and non-ANSI JOIN syntax in the same query Function -42
Do not mix ANSI and non-ANSI JOIN syntax in the same query Procedure Detected only by PL/SQL Analyzer
Do not mix ANSI and non-ANSI JOIN syntax in the same query View =
High Complexity Artifacts Function -32
High Complexity Artifacts Procedure -2
High Coupling Artifacts Function 1
High Coupling Artifacts Procedure Detected only by SQL Analyzer
High Coupling Artifacts Trigger Detected only by SQL Analyzer
High Coupling Artifacts View =
High Reuse by Call Function 1
High Reuse by Call Procedure =
High Reuse by Call Trigger Detected only by SQL Analyzer
High Reuse by Call View =
High SQL Complexity Artifacts Function Detected only by PL/SQL Analyzer
High SQL Complexity Artifacts Procedure Detected only by PL/SQL Analyzer
LIKE operator should not start with a wildcard character Function Exists only on SQL Analyzer
LIKE operator should not start with a wildcard character View Exists only on SQL Analyzer
Large Size Artifacts Function Detected only by PL/SQL Analyzer
Large Size Artifacts Procedure Detected only by PL/SQL Analyzer
Low Complexity Artifacts Function 375
Low Complexity Artifacts Procedure 2
Low Complexity Artifacts View Detected only by SQL Analyzer
Low Coupling Artifacts Function 1
Low Coupling Artifacts Procedure =
Low Coupling Artifacts Trigger Detected only by SQL Analyzer
Low Coupling Artifacts View =
Low SQL Complexity Artifacts Function 491
Low SQL Complexity Artifacts Procedure -14
Low SQL Complexity Artifacts View 2
Moderate Complexity Artifacts Function -8
Moderate Complexity Artifacts Procedure Detected only by PL/SQL Analyzer
Moderate SQL Complexity Artifacts Function Detected only by PL/SQL Analyzer
Moderate SQL Complexity Artifacts Procedure Detected only by PL/SQL Analyzer
Moderate SQL Complexity Artifacts View Detected only by PL/SQL Analyzer
Never use SQL queries with a cartesian product Function -5
Never use SQL queries with a cartesian product View -1
Never use WHEN OTHER THEN NULL Function Exists only on SQL Analyzer
Number of Code Lines Function =
Number of Code Lines Procedure =
Number of Code Lines Trigger =
Number of Code Lines View =
Number of Datablocks Function =
Number of Datablocks Procedure =
Number of Forms Function =
Number of Forms Procedure =
Number of Forms Trigger =
Number of Forms View =
Number of Functions Table =
Number of Tables View =
Number of Views Trigger =
Prefer PRIVATE to PUBLIC synonym Synonym =
Prefer UNION ALL to UNION Function -2
Prefer UNION ALL to UNION Procedure =
Prefer UNION ALL to UNION View =
Replace OR conditions testing equality on the same identifier in SQL WHERE clauses by an IN test condition Function Detected only by SQL Analyzer
Replace OR conditions testing equality on the same identifier in SQL WHERE clauses by an IN test condition Procedure Detected only by SQL Analyzer
Reuse by Call Distribution Function -4
Reuse by Call Distribution Procedure Detected only by SQL Analyzer
Reuse by Call Distribution Trigger Detected only by SQL Analyzer
Reuse by Call Distribution View =
SQL Complexity Distribution Function 327
SQL Complexity Distribution Procedure 24
SQL Complexity Distribution Trigger =
SQL Complexity Distribution View 23
Small Size Artifacts Function -1849
Small Size Artifacts Procedure -115
Small Size Artifacts Trigger Detected only by PL/SQL Analyzer
Small Size Artifacts View -92
Specify column names instead of column numbers in ORDER BY clauses Function Exists only on SQL Analyzer
Table naming convention - character set control Table Detected only by SQL Analyzer
Tables aliases should not end with a numeric suffix Function Exists only on SQL Analyzer
Tables aliases should not end with a numeric suffix Procedure Exists only on SQL Analyzer
Tables aliases should not end with a numeric suffix View Exists only on SQL Analyzer
Tables should be aliased Function Exists only on SQL Analyzer
Tables should be aliased Procedure Exists only on SQL Analyzer
Tables should be aliased View Exists only on SQL Analyzer
Use MINUS or EXCEPT operator instead of NOT EXISTS and NOT IN subqueries Function Exists only on SQL Analyzer
Use MINUS or EXCEPT operator instead of NOT EXISTS and NOT IN subqueries Procedure Exists only on SQL Analyzer
Use MINUS or EXCEPT operator instead of NOT EXISTS and NOT IN subqueries View Exists only on SQL Analyzer
Use WHEN OTHERS in exception management Function 338
Use WHEN OTHERS in exception management Procedure Detected only by SQL Analyzer
Use WHEN OTHERS in exception management Trigger =
Use at most one statement per line Function -152
Use at most one statement per line Procedure -2
VARCHAR2 and NVARCHAR2 should be used Function 5
VARCHAR2 and NVARCHAR2 should be used Package -2
VARCHAR2 and NVARCHAR2 should be used Procedure 1
VARCHAR2 and NVARCHAR2 should be used Table =
Very High Reuse by Call Function 8
Very High Reuse by Call Procedure =
Very High Reuse by Call View =