CODEWITHSUNDEEP
sqlsql-serverquery-optimization
Satfactor
Satfactor

Reputation: 389

Union statement with two tables slow

I have two tables table_old and table_new. They have the same columns and indexes.

I have a view creating with this statement:

Create View vw_combined
As
    Select * from table_new where effectivedate >= '1/1/2025'
    union all
    Select * from table_old where effectivedate < '1/1/2025'

When I run a query

select top 100 * 
from table_new 
where effectivedate >= '1/10/2025' -- it returns immediately

select  top 100 * 
from table_old 
where effectivedate >= '1/10/2025' -- it returns immediately

But when I run against the view

select top 100 * 
from vw_combined 
where effectivedate >= '1/10/2025' -- it takes 8 minutes.

When I look at the plan for the view query, for table old, it is getting all the 100M rows. But when I look at the plan for the table queries, it looks at only 100 rows.

I have no control over the calling queries. I can only change the views.

How do I create a view with good performance?

I am using Microsoft SQL Server 2016 SP3.

Upvotes: 0

Views: 113

Answers (1)

KumarHarsh
KumarHarsh

Reputation: 5094

To tune your query and index we must know table schema,number of rows in each table. Also Effectivedate datatype is doubtful.You must use date in 'yyyy-MM-dd' format.

Indexed View was one of the option.But it is very difficult to use Indexed View in this scenario as there is lot of limitation in using Indexed View.

Once try Filtered Index on effectivedate of each table.

Indexed View Still you can apply following good practices.

i) Mention all require columns in list instead of *

ii) Use Order by clause when using Top and explanation already given in comment

Top without Order by

Last solution, can be to Create Proc and in proc write the query to put the data in third table since your where clause if hard coded.Make the proc take Dates parameter and you execute it in server once.

Upvotes: 0

Related Questions