Performance of a view compared to a query (with union statements)

Question

I need to often run a query that aggregates data from different tables, something like

select
Name, Code, Date From TableA
union
select
Surname, TheCode, TheDate From TableB
union
[...] -- this stands for some more select statements
union
select
NickName, MyCode, getdate() from tableC

(the example is simplicistic, but it is like, this: I cannot refactor the database to put al the data I need in a single table!).

THis query can return 100.000 records, even if typically it will be 400-500 because of the WHERE conditions.

I am considering using a view to simplify the queries, but does this make sense? Is querying the view faster because the view is pre-calculated (I am not sure about this) or the view is executed as it is queried? I mean: if 10 users reuqest the same data running 10 queries against tables with joins ot running 10 queries against a view is it the same or is it better to have a view? (I mean if the view is executed only once it is better, if not it is exactly the same).

Moreover I cannot use an indexed view since I am using UNION STATEMENTS.

The advantage of a view would be that I can also easily do a select count (*) against the view, while to do this with tables I should write almost 2 different queries: one (the one I wrote above) for getting the records, and another one (modifying the one above) for the count.

Answer 1

Use UNION ALL, it does not do DISTINCT (UNION does it)

It says the same.

Answer 2

The 'precalculated' nature of an unindexed view basically just means the server will have the query plan already in the cache, whereas there is some chance that a select statement will not be in the query cache (usually not an issue).

You might consider putting your query in a function and calling it plain to get the records or using a slightly different select statement for the count. I don't see a big advantage to a view in this case.

Answer 3

You don't mention which version or edition of MSSQL you have, but you may be looking for "partitioned views" or even "partitioned tables" (see Books Online for full details). Whether they have any benefit for you or not depends on how much data you have, and testing would be the best way to find out.

And to answer your general question, when you have a view in a query the view name is replaced with the view definition when you query it, so MSSQL never 'sees' the view anyway. The exception is indexed views, where the query planner can take the data from the view, not the table. But indexed views have disadvantages too. Books Online has a lot of relevant information: look for "View Resolution", "Resolving Indexes on Views", "Creating Indexed Views" etc.