I want to rewrite the query to get the data from all the tables, currently I have 12 tables which contains exact same column names but have different table name and content. To get all the records I am currently union them like below.

select ct.id, ct.code, ct.name, ct.created_date, ct.updated_date, 
mt.id, mt.section, mt.description, mt.image_path
from client_1_tbl as ct
left outer join master_tbl as mt ON ct.id = mt.client_id
where ct.code in ('101','102','103')

UNION

select ct.id,ct.code, ct.name, ct.created_date, ct.updated_date, 
mt.id, mt.section, mt.description, mt.image_path
from client_2_tbl as ct
left outer join master_tbl as mt ON ct.id = mt.client_id
where ct.code in ('201','202','203')

UNION

select ct.id, ct.code, ct.name, ct.created_date, ct.updated_date, 
mt.id, mt.section, mt.description, mt.image_path
from client_3_tbl as ct
left outer join master_tbl as mt ON ct.id = mt.client_id
where ct.code in ('301','302','303')

Here I have given example of tables how I am using them, as you can see I have a master table and joining it with client table, but since client_1, client_2, client_3, ...etc contains different table names so I am just union them to get all the records. But now these client tables are started increased to 12 client tables, due to which this query is taking so much time.

Kindly please let me know if is there any better way to write this query. Since the client tables will be keep on increasing for every release and i need to optimise this query to have minimal changes.

CodePudding user response：

1. Use UNION ALL as it almost invariably performs better than UNION.
2. You can sub-query you client data and then join you master data in a single join (this probably won't improve performance, but might be neater for you).
3. For any further performance advice you need to provide all the data requested, table definitions, index definitions and the execution plan.

select
    ct.id, ct.code, ct.[name], ct.created_date, ct.updated_date 
    , mt.id, mt.section, mt.[description], mt.image_path
from (
    select ct.id, ct.code, ct.[name], ct.created_date, ct.updated_date, 
    from client_1_tbl as ct
    where ct.code in ('101','102','103')

    -- Use a union all for better performance
    union all

    select ct.id,ct.code, ct.[name], ct.created_date, ct.updated_date, 
    from client_2_tbl as ct
    where ct.code in ('201','202','203')

    -- etc ...
) ct
-- Join here to avoid multiple joins
left outer join master_tbl as mt ON ct.id = mt.client_id;

I should point out though, that in most cases when you have a multi-tenanted database, you should be using a single table and storing the owner of the client in another column. Then you don't run into these scaling issues.