CODEWITHSUNDEEP
sqlsql-serverperformance
Nate
Nate

Reputation: 30656

Performance when querying a View

I'm wondering if this is a bad practice or if in general this is the correct approach.

Lets say that I've created a view that combines a few attributes from a few tables.

My question, what do I need to do so I can query against this view as if it were a table without worrying about performance?

All attributes in the original tables are indexed, my concern is that the result view will have hundreds of thousands of records, which I will want to narrow down quite a bit based on user input.

What I'd like to avoid, is having multiple versions of the code that generates this view floating around with a few extra "where" conditions to facilitate the user input filtering.

For example, assume my view has this header VIEW(Name, Type, DateEntered) this may have 100,000+ rows (possibly millions). I'd like to be able to make this view in SQL Server, and then in my application write querlies like this:

SELECT Name, Type, DateEntered FROM MyView WHERE DateEntered BETWEEN @date1 and @date2;

Basically, I am denormalizing my data for a series of reports that need to be run, and I'd like to centralize where I pull the data from, maybe I'm not looking at this problem from the right angle though, so I'm open to alternative ways to attack this.

Upvotes: 2

Views: 349

Answers (6)

Marcus Adams
Marcus Adams

Reputation: 53880

The view will make use of the index in your WHERE clause to filter the results.

Views aren't stored result sets. They're stored queries, so you'll have the performance gained from your indexes each time you query the view.

Upvotes: 1

Cade Roux
Cade Roux

Reputation: 89741

It should, in general, perform no worse than the inline code.

Note that it is possible to make views which hide very complex processing (joins, pivots, stacked CTEs, etc), and you may never want anyone to be able to SELECT * FROM view on such a view for all time or all products or whatever. If you have standard filter criteria, you can use an inline table-valued function (effectively a parameterized view), which would require all users to supply the expected parameters.

In your case, for instance, they would always have to do:

SELECT Name, Type, DateEntered
FROM MyITVF(@date1, @date2);

To share the view logic between multiple ITVFs, you can build many inline table-valued functions on top of the view, but not give access to the underlying tables or views to users.

Upvotes: 0

gbn
gbn

Reputation: 432667

If not indexed then...

When you query a view, it's ignored. The view is expanded into the main query. It is the same as querying the main tables directly.

What will kill you is view on top of view on top of view, in my experience.

Upvotes: 0

Quassnoi
Quassnoi

Reputation: 425813

My question, what do I need to do so I can query against this view as if it were a table without worrying about performance?

SQL Server is very good in view unnesting.

Your queries will be as efficient as if the view's query were used in the query itself.

This means that

CREATE VIEW myview AS
SELECT  *
FROM    /* complex joins */

SELECT  *
FROM    mytable
JOIN    myiew
ON      …

and

SELECT  *
FROM    mytable
JOIN    (
        SELECT  *
        FROM    /* complex joins */
        ) myview
ON      …

will have the same performance.

Upvotes: 3

Oded
Oded

Reputation: 499362

SQL Server 2005 has indexed views - these provide indexes on views. That should help with performance. If the underlying tables already have good indexes on the queried fields, these will be used - you should only add indexed views when this is not the case.

These are known in other database systems as materialized views.

Upvotes: 1

Klaus Byskov Pedersen
Klaus Byskov Pedersen

Reputation: 121057

Why would it perform badly? I, mean you can think of a view as a compiled select statement. It makes use of existing indexes on the underlying tables, even when you add extra where clauses. In my opinion it is a good approach. In any case it's better than having virtually the same select statement scattered all over your application (from a design and maintainability point of view at least).

Upvotes: 0

Related Questions