SQL Query Performance in case of simple union

Question

I have a simple query tuning question, it is

can We improve the performance of a view which have definition as

SELECT * FROM A UNION ALL SELECT * FROM B

and this is performing so poorly that it is taking 12 seconds for 6.5k Records

Any help is appreciated.

Solution 1

The problem with a union view like that is that the original columns of the tables are not accessible to the optimizer via the view, so when you use the view like this:

select * from myview where foo = 5

the where clause is a filter on the entire rowset delivered by the view, so all the rows from both tables are processed after the union, so no indexes will be used.

To have any hope at performance, you have to somehow get the condition you want inside the view and applied to each table, but keep it variable so you can apply different criteria when you use it.

I found a work-around that does this. It's a little bit hacky, and doesn't work for concurrent use, but it works! Try this:

 create table myview_criteria(val int);
 insert into myview_criteria values (0); -- it should have exactly one row

 create view myview as
 SELECT * FROM A
 WHERE foo = (select val from myview_criteria)
 UNION ALL
 SELECT * FROM B
 WHERE foo = (select val from myview_criteria);

then to use it:

update myview_criteria set val = 5;
select * from myview;

Assuming there's an index on foo, that index will be used.

Caution here, because obviously this technique won't work when multiple executions are done concurrently.

Solution 2

is there a reason u keep these two not in the same table, no matter what you do, eventually it will be horrible. If possible consider a migration to one table. If not, you can always "materialize a view" by inserting them all into the same thing.

---

with that being said, you are not going to be selecting * on a union, if there are specific conditions on top of the view, performance can be improve by the indexing that column.

It would help if you tell everyone what specific db this is for.

Comments

[Tom Lint]

Wouldn't it be better to use a (Inline) Table-Valued Function, instead?

[Bohemian]

@TomLint could be, but this works for any database (most of which don’t have such things as table-valued functions)