Takeaway: I define a metric which indicates a code-smell for overly complex views, procedures and functions: “FROM” count.
In the programming world, developers have defined metrics for how complex a piece of code is. For example, Visual Studio defines several metrics that are meant to give developers some idea about how complex their code is getting. These measures won’t be perfect because code complexity is not something that can be measured directly. But many think that these measures indicate complexity often enough to be useful.
That’s what a code smell is meant for. My own definition is that a code smell is an anti-rule-of-thumb <cough>cursors!</cough>. It doesn’t necessarily indicate something’s rotten, but like a bad smell, it’s worth checking into.
Measuring Complexity in SQL Modules
And here’s my idea about measuring complexity for SQL. It’s based on the premise that complex procedures or views will have many queries/subqueries and that most queries have FROM clauses. So what are my procedures/views/functions that may be too complex? Let’s find out:
DECLARE @Odor NVARCHAR(30) = N'FROM'; with L0 as (select 1 as C union all select 1) --2 rows ,L1 as (select 1 as C from L0 as A, L0 as B) --4 rows ,L2 as (select 1 as C from L1 as A, L1 as B) --16 rows ,L3 as (select 1 as C from L2 as A, L2 as B) --256 rows ,L4 as (select 1 as C from L3 as A, L3 as B) --65536 rows ,Nums as (select row_number() over (order by (select 0)) as N from L4) SELECT OBJECT_SCHEMA_NAME(m.object_id) as SchemaName, OBJECT_NAME(m.object_id) as ObjectName, count(1) as OdorCount FROM Nums CROSS JOIN sys.sql_modules m WHERE Nums.N < LEN(m.definition) AND SUBSTRING(m.definition, Nums.N, LEN(@Odor)) = @Odor GROUP BY m.object_id ORDER BY count(1) desc, object_name(m.object_id) asc
I’ve found the metric slightly better than the simpler “procedure size” metric:
SELECT OBJECT_SCHEMA_NAME(m.object_id) as SchemaName, OBJECT_NAME(m.object_id) as ObjectName, LEN(m.definition) as ModuleSize FROM sys.sql_modules m ORDER BY LEN(m.definition) desc, object_name(m.object_id) asc
Try it out on your own environments and let me know if it identifies the monster procedures you know are lurking in your database.
Thanks to Adam Machanic for the substring counting syntax.
Update December 5, 2013: In the comments, George Mastros provided a simpler and faster version of this query which does the same thing:
DECLARE @Odor NVARCHAR(30) = N'FROM'; Select OBJECT_SCHEMA_NAME(object_id) As SchemaName, OBJECT_NAME(object_id) As ObjectName, (DataLength(definition) - DataLength(Replace(definition, @Odor, ''))) / DataLength(@Odor) As OdorCount From sys.sql_modules Order By OdorCount DESC;
This is much simpler and much faster. What’s extra interesting is that George’s query itself has an odor count of 2 while my original one had a count of 7. Thanks so much George!