Friday, May 23, 2014

Complexity. How it is calculated.

What are the magic pink numbers you can see in Code Outline window?Code Outline complexity numbers
The answer is very simple - these are Code Complexity numbers. Each number represents complexity of outlined node, something like Cost in query execution plan. The same numbers you can see after Complexity check - but for whole procedure, not for each statement.
So how Complexity is calculated?

Here is a schematic calculation algorithm:

  1. Each statement breaks into very small parts.
  2. Complexity of each statement consists of complexity of all its parts.
  3. Each part has some constant complexity (weight), usually 0.01. Why so small weight? Each statement can break into a huge number of parts.
  4. Each identifier has weight of one. So the more identifiers you use (tables, functions, columns) the more complex your query is.
  5. Some statements or its part can have a big weight (eg EXEC(query) has weight of ten).
  6. For complex parts of statement (eg - correlated subquery or query under EXISTS clause) its complexity is multiplied by "Complex factor" (for derived_table by factor of two. Rowset functions are multiplied by factor of ten). 
  7. First <table_source> in FROM clause have multiplication factor of one, each next have multiplication factor of one and a half.

1 comment:

  1. Do you have a table with different categories for the complexity values? When we should worry about a piece of code that has too much complexity?
    Thanks in advance