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.
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
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?
This seems a nice case to use a Indexed View.
With the indexed view, you will have a index on
SourceValue and the
COUNT_BIG(*)), will be materialized. Your query will run very fast.
I would be happy to write the script that could solve this problem, but I don't have your create scripts, so that would take too much time. I can complement this answer, if you add an end-to-end script to simulate the problem on the question.
SQL Server Indexed Views: The Basics - https://www.red-gate.com/simple-talk/sql/learn-sql-server/sql-server-indexed-views-the-basics/