CODEWITHSUNDEEP
sqlperformancesql-server-2008viewunion
Ashutosh Arya
Ashutosh Arya

Reputation: 1168

SQL Query Performance in case of simple union

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.

Upvotes: 10

Views: 34239

Answers (3)

Bohemian
Bohemian

Reputation: 424993

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.

Upvotes: 9

Kyle Wormsbacher
Kyle Wormsbacher

Reputation: 56

My best advice is to limit the number or records that are returned from the source tables to only what you really need and making sure your data is properly indexed.

Don't use * unless its an ad-hoc script (even then it would still be better to list the columns) include only the columns that you really need for the task at hand.

Use proper indexes based on the most frequently used queries. Remove any unused indexes. Remove any duplicate indexes.

Pluralsight has a wonderful course on MS SQL Server query performance tuning. http://www.pluralsight.com/training/Courses/TableOfContents/query-tuning-introduction

The course is presented by Vinod Kumar and Pinal Dave who are two very brilliant individuals.

You could also check out Pinal's article on SQL Server Performance to help point out areas that you may not have thought about.

http://blog.sqlauthority.com/sql-server-performance-tuning/

Upvotes: 0

Kasumi
Kasumi

Reputation: 921

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.

Upvotes: 1

Related Questions