Recently me and my development team have been looking at performance and how to improve it.

complexityOne area of improvement we have identified is with our stored procedures, which we plan to rewrite. But how do we identify which are the easiest to rewrite and which are the hardest?

My first thought was maybe to count the number of times each keyword is used and rank them somehow. eg each JOIN gets 5, OUTER APPLY gets 20, each term in WHERE gets 1, and then combine that with the length of the query and how many parameters.

However doing a bit of googling I came across the following blog and sql script. This script analyses the stored procedures in terms of number of lines of code, number of parameters and number of dependencies.

The complexity is divided into SIMPLE, MEDIUM and COMPLEX.

WHEN NumberOfLines * NumberOfDependencies * NumberOfParameters < 5000 THEN ‘Simple’
WHEN NumberOfLines * NumberOfDependencies * NumberOfParameters < 10000 THEN ‘Medium’
ELSE ‘Complex’

reportscreenshot1

This gives a fairly good estimate of which stored procedures are the most complex and which would probably take the longest time to rewrite.

This post has already been read 977 times!

Simon Foster on GithubSimon Foster on LinkedinSimon Foster on Twitter
Simon Foster
Web Developer
I have worked in SysAdmin and IT Management but now work as a Web Developer. I love everything IT related and I am trying to learn as much as I can especially about DevOps. Why not follow me on twitter?