A performance question re: views

[aolb]

Does a query selecting from a view, using a ‘WHERE’ clause at the end of that query have all the results from that view returned before applying that ‘WHERE’ clause or is the compiler cleaver enough to apply that ‘WHERE’ clause to the view query?

In other words is it better to use a view in a query or just write the query from scratch?

I.E.

Select *
From view
Where blah = blah

Or

Select *
From tableA inner join tableB on blah = blah
Where blah = blah

What I want to do is to use views in my stored procedures to make maintenance easier but if this affects performance then I will have to write these queries with tables.

 

[donutman]

With the example that you gave, I doubt that it would make much difference. However, I'm sure that in most cases it's better if the optimizer has all the info up front, so put the where in the view. Remember views can't use parameters, so that may limit this use. Also there may be times when you know better than the optimizer and it might be best to force it to create a temporary result set. This would be like "hints" to the optimizer.
Bottom line, my guess is it depends. If you have very large tables, test it. If you don't, then stop obsessing.
-Karl

 

[TJRTech]

I think I understand your question, but let me just soapbox for a while and see where it goes...

When designing views, it is usually advantageous to keep the WHERE clause out, especially if using the JOIN sytax to join tables (e.g. not doing the joins in the where clause).

I say this because one of the biggest benefits a view can provide is to "hide" the complexity of a data model, PK and FK relationships, joins, and the like; and provide an abstracted, "single entity" or "related entities" 'view' into your data.

For example, you may have the following tables:
PERSON,
ORGANIZATION,
ADDRESS,
CONTACT_INFO

And, you might have a whole bunch more associative tables that link people to orgs, etc.

In this example, requiring that a developer knows your table structures, their entity relationships etc can be daunting. Furthermore, forcing them to know these things and having them code joins means that you are less able to easily change your data model without a ripple effect.

Instead, introduce a bunch of views:

vwPeople
vwOrganizations
vwOrganizationMembers
vwPeopleAddresses
vwPeopleContactInformation

And, have each of the views expose all the same columns as their combined tables, and simply join the tables in the views, but keep the WHERE clause non-existant so that the views are as generic as possible.

You *MIGHT* create some "specialized" views that have filters/where clauses based on Business Rules. For example, maybe you have an Organization named "HR". Instead of having to always specify in your select from vwOrganizationMembers a where clause that includes HR, you might just create a view called: vwHRMembers
and that view includes a where clause that selects "HR" members.

I guess what I am trying to say is that the more specialized the view and the more "criteria" you include in the WHERE clause the less likely it is to be a "universally useful" view.

Regards,
Tom Rogers

 

[aolb]

You are correct. The reasons I want to use views is to query against the data in my database are:

1. Hide complexity
2. Improve maintainability of the interfaced by not making the interface dependant on the database tables structure
3. Reuse of SQL and simplified queries

My question is there any performance issues if I write loads of views for my data and use them in my queries?

There is not just my query with writing a query using views and filtering with a ‘WHERE’ clause, there is also the issue of a view selecting 20 columns and I only want 2 of them.

These queries are for a user interface and there are a couple of tables with over 100,000 records, though most tables I will be querying against will have in excess of 10,000 records.

I was hoping for a definitive answer to my query rather than having to try one option and then try the another.

 

[TJRTech]

Don't think of the VIEW "virtual table" that is generated on the fly and that you then SELECT data from, getting only the columns you want and only the data you want.

It doesn't work that way.

Instead, think more akin to your client applications SELECT statement and the VIEW getting MORPHED together by the query processor such that:

a) ONLY the columns you select are every processed or returned (others that are part of the view are ignored, unless in the JOIN or WHERE clause of the original view)

b) ONLY the rows that are found by the VIEW *AND* your SELECT statement, combined, are run through the query processor.


The above is my understanding of how things work, and what I have seen in reviewing execution and execution plans that use views and don't use views in Query Analyzer.

Regards,
TR

 

[aolb]

Thanks for the feedback to my question.

I have decided to use views sparingly in my procedures because I have been told that the query optimiser works fine with simple queries but does not work that well with complicated queries.

If someone knows different I would be interested to hear.

This is the responce I got from another site:

The question is, is there any performance issues if I write loads of views for my data and use them in my queries instead of tables?

e.g. If I have a view that I use in a query that has 20 columns and I want only 2 of those and to I want to also filter the results with a ‘WHERE’ clause, will the view bring back everything before selecting the 2 columns I want and before applying the ‘WHERE’ clause or is the compiler sophisticated enough to rewrite my query efficiently?

It depends on the optimiser and the query structure. You may get lucky, or you may not. If you want to be sure, you need to measure it against an optimised query, and if you're going to all the trouble of producing an optimised query in the first place, you might as well use that. For what it's worth, in my experience, query optimisers work pretty well on single queries, but when you start chaining queries together they can get very confused, and when this happens you can end up with unacceptable run-times. Always look at the query stats, and if you see something dubious, like a physical read for every row returned, you can be pretty sure you have a problem.

 

[TJRTech]

Chaining queries together" can be done either with unions or sub-selects, and those can perform much more poorly than a simple query whether in a view or not.

My suggestion to you is to use as many views as possible for the reasons you described.

And, for each view you are developing, test its performance using Query Analyzer and its Execution Plan capability; and compare queries against the view vs queries straight against the tables; and where the view creates only a modest and acceptable overhead, use it instead of the SQL query.

TR

 

[aolb]

Thanks for your help, I will look into it.

 

[AshleyRickards]

You could also use Indexed Views to increase performance.

HTH

Ashley L Rickards
SQL DBA

 

[TJRTech]

Yes, thanks Ashley, I was going to mention that. But, with indexes views you need to make sure you create your underlying tables with the appropriate settings.

For more on Indexed Views see "SQL Books Online" (BOL) and this web article:

http://www.sql-server-performance.com/indexed_views.asp

Regards,
TR