Estimation

Calculate TSQL Stored Procedure Complexity

Aalam Rangi9 Comments

I had a recent project where we planned to re-factor an existing application’s .Net and TSQL code. I wanted to find the complexity of the stored procedures and arrive at an estimate of the effort required to review all them. I had in mind that something like number of lines of code, number of dependencies and parameters in the stored procedure would give a good starting  point to create such an estimate.

I patched together a script that produced a report similar to the example below. I used that output and put in some more formulas to assign a complexity level (simple, medium or complex) and the approximate number of minutes required to review that procedure. I have not included those calculations here because they depend entirely on the estimator’s perception of complexity and the developer’s skill level.

The number of lines of code is a subjective metric here because it depends on the developer’s coding style and use of whitespace. Even then, it could be a useful reference point.

Complexity Report Screenshot

In case this seems useful then the script to create that report is available for download at Technet.

Download and rate the script at: http://gallery.technet.microsoft.com/Calculate-TSQL-Stored-831b683a

CHANGE HISTORY:
2012/Dec/01 – Version 1
2013/Feb/15 – Version 1.1 Added the CASE statement for complexity.
2014/Apr/29 – Version 1.2 Added Database and Schema name to the report.
Included objects that may not be refering to other objects.
Included views, functions and triggers.

Please feel free to share you feedback in the comments.

SSIS Package Development Effort Estimation

Aalam RangiLeave a comment

I blogged about a DTS to SSIS conversion effort estimation project that I had a chance to work upon previously. I had created an Excel workbook to help in that estimation.

That workbook was not just limited to the DTS to SSIS conversion estimates. It can be used for regular SSIS development project estimations too. I’ve updated the workbook a bit to reflect its generic nature.

The Excel workbook can help in estimating the effort required to create new SSIS packages. The new SSIS could either be a conversion of an existing DTS package or a fresh requirement. The estimation methodology would be similar in both cases.

Benefits of Using this Estimation Methodology

  • Standardize the estimates by using the same rules instead of guessing randomly, which could return widely varying figures depending on whom you talk to.
  • Standardizing also means that the estimation process for small, medium or large projects is the same. The importance of having a standardized process is directly proportional to the project size, to reduce the variance.
  • The estimates can be fine-tuned over time with real-world feedback so that the estimates get better with each new project.
  • Flexible to include the developer skill factor.

Two Steps to Fill the Estimation Workbook

  1. Put in the estimated number of minutes required for –
    a) creating each type of component in SSIS. This time could vary depending on the developer’s experience and skill. Sample values are included in the workbook.
    b) standard time additions for activities like analysis, error handling, logging and documentation. Sample values are included in the workbook.
  2. Update the workbook with the details like the type and number of all tasks, connections, transformations etc. that would be used in the SSIS package.
    a) If the basis is a DTS package then open the DTS and count the tasks, connections etc.
    b) If the basis is a fresh requirement then put the details according to those requirements. This will also help you to plan the package development.

A brief Help section is also included in the workbook.

Download

Download at: http://gallery.technet.microsoft.com/DTS-to-SSIS-Conversion-7153a957