Best approach for querying multiple tables

by dzenesiz   Last Updated May 22, 2018 14:06 PM

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?



Answers 1


This seems a nice case to use a Indexed View.

With the indexed view, you will have a index on SourceValue and the COUNT (now 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.

Please check:

Create Indexed Views - https://docs.microsoft.com/en-us/sql/relational-databases/views/create-indexed-views?view=sql-server-2017

SQL Server Indexed Views: The Basics - https://www.red-gate.com/simple-talk/sql/learn-sql-server/sql-server-indexed-views-the-basics/

Evandro Muchinski
Evandro Muchinski
May 22, 2018 14:02 PM

Related Questions



Why is this query not using the index i created?

Updated September 07, 2017 00:06 AM

OLTP Indexed Views or SQL Calculation

Updated November 12, 2017 04:06 AM


Are views harmful for performance in PostgreSQL?

Updated January 09, 2018 11:06 AM