Best approach for querying multiple tables In SQL Server, I have a table, let's call it Source. The structure is

ID | SourceValue 
-----------------
1  |  'value1' 
2  |  'value1' 
3  |  'value2'


and so on. 

ID is a primary key and SourceValue is not unique. 

I also have several other tables which have SourceValues in their records. As it's not unique, there is no Foreign Key constraint. This column also isn't UNIQUE in other tables. Furthermore, these columns can hold values both of SourceValue format and other formats. Because of this, I need to include a DISTINCTed SourceValue data in the query. 

I need to return COUNT of every occurence of SourceValue in all tables other than Source.

Our approach was to create a view which holds DISTINCT SourceValues from the Source table, and then do the following:

SELECT q.SourceValue, SUM(q.Count) as Count
FROM (
  SELECT dvw.SourceValue, COUNT(dvw.SourceValue) AS Count
  FROM distinctValuesView AS dvw
  JOIN DependantTable1 dt1
  ON dvw.SourceValue = dt1.SourceValueColumn
  GROUP BY dvw.SourceValue

  UNION

  SELECT dvw.SourceValue, COUNT(dvw.SourceValue) AS Count
  FROM distinctValuesView AS dvw
  JOIN DependantTable2 dt2
  ON dvw.SourceValue = dt2.SomeOtherColumn
  GROUP BY dvw.SourceValue
) AS q
GROUP BY q.SourceValue


distinctValuesView is defined as 

SELECT DISTINCT SourceValue FROM Source


The problem is there are more than 20 DependantTables (transaction tables) and all these tables have tens of thousands of rows, with the Source table having more than 20.000 rows.

Naturally, this query is unacceptably slow (2 seconds execution time). Is there any other approach, or a way to speed it up?
I'm not sure why you join your dependent tables with the distinctValuesView. Is it to rule out sourceValues that does not exist in Source? In that case you may try to use EXISTS instead:

SELECT q.SourceValue, SUM(q.Count) as Count
FROM (
    SELECT dt1.SourceValue, COUNT(*) AS Count
    FROM DependantTable1 dt1
    WHERE EXISTS (
        SELECT 1 FROM Source s
        WHERE s.SourceValue = dt1.SourceValueColumn
    )
    GROUP BY dt1.SourceValue

    UNION ALL

    ...
) q
GROUP BY q.SourceValue


Note that I changed UNION to UNION ALL. I assume that if you have 4 occurrences of 'value1' in DependantTable1 and 4 occurrences in DependantTable2 the sum should be 8. If you use UNION the result will be 4

If most of your SourceValue exists in Source, you may try to apply the union first:

SELECT q.SourceValue, COUNT(*)
FROM (
    SELECT dt1.SourceValue
    FROM DependantTable1 dt1
    UNION ALL
    SELECT dt2.SourceValue
    FROM DependantTable1 dt2
) q
WHERE EXISTS (
    SELECT 1 FROM Source s
    WHERE s.SourceValue = q.SourceValueColumn
) 
GROUP BY q.SourceValue