locked
Complexity of sql server stored procedure RRS feed

  • Question

  • Hi,

     

    Thanks for reading this post.

     

    We have a requirement where we need to identify the complexity of the SQL server stored procedure based on the parameters such as, no. of multiple joins,no. of inline sp calls,

    no. of if/else constructs etc.Is there any tool that can be used to analyze the SP based on the above parameters? Any help regarding this would be highly appreciated.

     

    Thanks in advance.

    Wednesday, January 4, 2012 1:37 PM

Answers

  • I don't have an answer to your question.

    You might be interested in the book "Making Software: What Really Works, and Why We Believe It". The authors of the book researched all kinds of approaches to software development, and also assessed the usefulness of different metrics related to software complexity.

    To spoil your appetite: their conclusion on that topic is that determining complexity based on branches and such is not (significantly) better than simply counting lines of code (excluding blank and comment lines). You could use the book to convince your boss that analyzing SPs this way is not worth the effort and is better spent on other things.

     


    Gert-Jan
    • Proposed as answer by Naomi N Thursday, January 5, 2012 10:39 PM
    • Marked as answer by Kalman Toth Friday, January 20, 2012 4:40 PM
    Thursday, January 5, 2012 9:03 PM

All replies

  • hmm, I don't know any tool which calculates one. But you should use the estimated/actual execution plan as basis for any metric. Maybe you only need a visualization tool like SQL Sentry Plan Explorer or SSMS Tool Pack's Execution Plan Analyzer.

    Wednesday, January 4, 2012 2:03 PM
  • Google up Function Point Analysis. I am not sure that they have SQL, but it is worth a look.
     
    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL
    Thursday, January 5, 2012 3:56 AM
  • I don't have an answer to your question.

    You might be interested in the book "Making Software: What Really Works, and Why We Believe It". The authors of the book researched all kinds of approaches to software development, and also assessed the usefulness of different metrics related to software complexity.

    To spoil your appetite: their conclusion on that topic is that determining complexity based on branches and such is not (significantly) better than simply counting lines of code (excluding blank and comment lines). You could use the book to convince your boss that analyzing SPs this way is not worth the effort and is better spent on other things.

     


    Gert-Jan
    • Proposed as answer by Naomi N Thursday, January 5, 2012 10:39 PM
    • Marked as answer by Kalman Toth Friday, January 20, 2012 4:40 PM
    Thursday, January 5, 2012 9:03 PM