October 14, 2020 at 3:18 pm
Good Afternoon All,
I want to be able to count the number of joins used in views. We have a lot of views that are legacy and are awful. I was hoping to provide some kind of insight into what views could be improved or not used anymore -- using the number of joins as an indicator of performance.
I know that's not a fool proof way but in this environment poor performance is correllated with monstrous views (10+ joins each, and somtimes views that join two other monstrous views). The majority that we've found were used because its easier.
So my question is how to get a top level view of this without inspecting each view by hand. Is this possible? We have the query store enabled as well, should that be a route to take.
Cheers
Alex
October 15, 2020 at 4:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
October 19, 2020 at 1:56 pm
This seems to almost work, but the counts are a bit higher than the actual number of 'JOIN' strings.
No time to improve it right now, but might be "good enough" for you to get started. You may have multiple rows for the same view because the view code is stored in multiple records.
select name,
(LEN(text) - LEN(REPLACE(text,'JOIN ','')))/COALESCE(NULLIF(LEN('JOIN '), 0), 1) as 'Count'
from syscomments com
join sysobjects obj on com.id = obj.id
where text like '%JOIN %' and
TYPE = 'V'
order by name
October 20, 2020 at 9:24 pm
I think should be pretty accurate, at least without going to a lot more trouble to write. Yes, it uses recursion, because we need to find every JOIN. If somehow you have more than 100 JOINs in a view, you'll need to add a MAXRECURSION option/clause.
;WITH cte_find_joins AS (
SELECT v.object_id, view_def, PATINDEX('%[^A-Z0-9$#_]JOIN[^A-Z0-9$#_]%', view_def) AS view_join
FROM sys.views v
CROSS APPLY ( SELECT OBJECT_DEFINITION(v.object_id) As view_def ) AS view_def
WHERE PATINDEX('%[^A-Z0-9$#_]JOIN[^A-Z0-9$#_]%', view_def) > 0
UNION ALL
SELECT object_id, view_def,
view_join + PATINDEX('%[^A-Z0-9$#_]JOIN[^A-Z0-9$#_]%', SUBSTRING(view_def, view_join + 6, 2000000000))
FROM cte_find_joins
WHERE PATINDEX('%[^A-Z0-9$#_]JOIN[^A-Z0-9$#_]%', SUBSTRING(view_def, view_join + 6, 2000000000)) > 0
)
SELECT OBJECT_NAME(object_id) AS view_name, COUNT(*) AS join_count
FROM cte_find_joins
GROUP BY object_id
ORDER BY view_name
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
October 20, 2020 at 10:58 pm
neither of the options above will capture cases where the "joins" are old style.
and why not use the system dependency tables for it?
SELECT schema_name(o.schema_id) as referencing_schema_name
, OBJECT_NAME(sed.referencing_id) AS referencing_entity_name
, count(*) as Number_Of_Joins
, sum(case when o1.type = 'IF' then 1 else 0 end) as count_Inline_Function
, sum(case when o1.type = 'S' then 1 else 0 end) as count_System_Table
, sum(case when o1.type = 'SN' then 1 else 0 end) as count_Synonym
, sum(case when o1.type = 'U' then 1 else 0 end) as count_User_Table
, sum(case when o1.type = 'V' then 1 else 0 end) as count_View
, sum(case when o1.type = 'TF' then 1 else 0 end) as count_Table_Valued_Function
FROM sys.sql_expression_dependencies sed
INNER JOIN sys.objects o
ON sed.referencing_id = o.object_id
left outer join sys.objects o1 -- only works for locally referenced objects
on sed.referenced_id = o1.object_id
and o1.type in (
-- only look for these types - they would most likely be used in joins
'IF' -- SQL inline table-valued function
, 'S' -- System base table
, 'SN' -- Synonym
, 'U' -- Table (user-defined)
, 'V' -- View
, 'TF' -- SQL table-valued-function
)
where o.type_desc = 'View'
group by schema_name(o.schema_id)
, OBJECT_NAME(sed.referencing_id)
October 21, 2020 at 12:55 pm
Wouldn't that count every reference as a "join", including a select from a single table? And any other single select from a table.
SELECT ...
FROM dbo.table1
SELECT ...
FROM dbo.table1
WHERE EXISTS(SELECT 1 FROM sys.tables WHERE name = 'table2') /*note that this is not a join*/
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
October 21, 2020 at 1:54 pm
Wouldn't that count every reference as a "join", including a select from a single table? And any other single select from a table.
SELECT ...
FROM dbo.table1
SELECT ...
FROM dbo.table1
WHERE EXISTS(SELECT 1 FROM sys.tables WHERE name = 'table2') /*note that this is not a join*/
for first case yes - but sql I posted can easily exclude those (having count(*) > 1)
as for the second case - I would consider that to be a join anyway - just not a "direct" join, but it is linking 2 tables
consider the following
select (select name from reftable where refid = a.id1) as id_1
, (select name from reftable where refid = a.id2) as id_2
, (select name from reftable where refid = a.id3) as id_3
, (select name from reftable where refid = a.id4) as id_4
from invoices a
that is 4 joins - my query would would list 2 tables used - the ones above would list only 1 table - so neither is good in this case
a combination of my code plus a string search for
"join, union, outer apply, cross apply, exists, in" -- not all of these will be other tables but they can be - lots of false positives specially for the "in, outer apply"
could give a better result - but string search alone even including the ones I mention here is not enough.
October 21, 2020 at 2:37 pm
That's why I put the note: my EXISTS example does NOT reference an outer table. A rare situation perhaps, but not impossible, more likely:
WHERE EXISTS(SELECT 1 FROM sys.tables WHERE name = @variable1)
This query just references a parameter / variable, NOT another query, therefore it is NO type of join.
but sql I posted can easily exclude those (having count(*) > 1)
But then how do you show views with a single JOIN statement?
What about joins to queries that use several tables? It's only 1 join but it will be lots of object references:
SELECT ...
FROM ...
INNER JOIN (
SELECT key_col, SUM(some_amount) AS ..., SUM(some_other_amount) AS ...
FROM table1...
INNER JOIN table2...
INNER JOIN table3...
INNER JOIN table4...
) AS totals1
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
October 21, 2020 at 2:54 pm
That's why I put the note: my EXISTS example does NOT reference an outer table. A rare situation perhaps, but not impossible, more likely:
WHERE EXISTS(SELECT 1 FROM sys.tables WHERE name = @variable1)
This query just references a parameter / variable, NOT another query, therefore it is NO type of join.
but sql I posted can easily exclude those (having count(*) > 1)
But then how do you show views with a single JOIN statement?
What about joins to queries that use several tables? It's only 1 join but it will be lots of object references:
SELECT ...
FROM ...
INNER JOIN (
SELECT key_col, SUM(some_amount) AS ..., SUM(some_other_amount) AS ...
FROM table1...
INNER JOIN table2...
INNER JOIN table3...
INNER JOIN table4...
) AS totals1
Missing the point on this last one - you have multiple joins, not just 1 join.
in this case both the "string" approach and my method would give multiple counts.
Main difference as I said is that the system view reports objects used, not number of times the object is used.
so queries with multiple joins to the same table will unfortunately be underreported with my code.
but with string approach, and if using old style joins, then no joins will be reported either on this particular case.
As I said no perfect solution and neither approach will work for all cases.
October 22, 2020 at 2:54 am
I would like to remind you that views themselves are not a performance issue. The query can be the culprit.
You could inline every single view -factoring out every single one- and the performance would not change.
In other words, a badly written view performs exactly the same as a badly written query.
October 22, 2020 at 6:02 am
This was removed by the editor as SPAM
October 22, 2020 at 10:06 am
> Missing the point on this last one - you have multiple joins, not just 1 join. <<
It depends on how you look at it. The view itself has only one JOIN. My query will also miscount that one.
As to old-style joins, it would be extraordinarily complex to try to determine those dynamically (and in any reasonable time period). Besides, they shouldn't have been used for the past 20 years at least, so hopefully they're not an issue for nearly all shops.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply