Alternative to UNION with indexing

Posted on

Question :

I’m on SQL Server 2012 and I have tables for different companies each one with its own customers:

CompanyA_Customers:

ID | Name
---------
11 | Foo
12 | Bar

CompanyB_Customers:

ID | Name
---------
11 | Baz
12 | Qux

And I need a single table to query them, so I did this:

CREATE VIEW Customers AS
    SELECT 'A' AS Company, ID, Name FROM CompanyA_Customers
UNION ALL
    SELECT 'B' AS Company, ID, Name FROM CompanyB_Customers

but I suffer poor performance since as far as I know on a UNION view I can’t define any index.

Are there any alternatives to have better performance?

Answer :

IMHO (and as Paparazzi has pointed out on comments) the easiest way to accomplish it and get better performance is using a single table by adding a CompanyID field.

CREATE TABLE Customers
(
    CompanyID   <some_type> REFERENCES Companies (CompanyID),
    CustomerID  <some_type> REFERENCES Customers (CustomerID),
    Name        text,
    PRIMARY KEY (CompanyID, CustomerID)
);

If you cannot modify your actual table schema, you could use two materialized views, one for each company, and add a clustered index on (company_id, id) or (id, company_id) depending on your queries.

I’ve set up a fiddle here

select company_id, id, name
from (
      select company_id, id, name from dbo.vcustA
      union all
      select company_id, id, name from dbo.vcustB
     ) x
where id = 2;

Execution plan for two materialized views using UNION:

enter image description here

But in fact, this is worst than create a new table with all records of both tables. Instead of use two materialized views, I’d suggest to create a new table and, using triggers, add/update/delete all records from customersA and customersB. Or set up a daily job that synchronize this information.

You can create a partitioned table to horizontally partition you data.

Partitioned tables is an Enterprise edition feature, if you are on a lesser edition you could alternatively create a partitioned view, see this link for an example https://auntkathisql.com/2015/08/26/what-is-a-partitioned-view/

If you are using SQL Server 2016, you could also put all the data in one table and use Row-Level Security to expose the correct data to the relevant customers.

Leave a Reply

Your email address will not be published.