November 4, 2009 at 8:13 am
Until recently I haven't had a great deal to do with Views, but I think I'm working under a misunderstanding of them.
I have been told to think of them as temporary tables, created upon demand and the implication was that if I apply a Where clause against a view then the view runs and then the Where clause is applied, but I'm not convinced.
For example - Northwind, Orders Qry View. If I just run the View it extracts 830 rows. If I try:
SELECT * FROM Orders Qry WHERE employeeID = 6, then I get 67 rows.
Has the View extracted the full 830 rows first, and then filtered those results to give me 67, or has it updated the query that produces the View to make a View that only gives 67 rows? I suspect it is the latter, having looked at the stats.
Thanks.
BrainDonor.
November 4, 2009 at 8:21 am
The view is simply a pre-built select statement. When you query a view, it simply gets added to the definition of the outer query.
That means, no, it doesn't pull all the rows and then filter them. The query engine is smarter than that.
The problem with performance in views is that they often contain joins and columns that aren't needed for the outer query. They still have to do that work, regardless of how many/few rows they end up with.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 4, 2009 at 8:21 am
You can easily answer this question yourself. Before running the query in SSMS, set it to also return the Actual Execution Plan and review the plan. You should see that the WHERE criteria is applied as the sql behind the view is executed.
I'd try it myself, but there isn't enough in your original post for me to do so.
November 4, 2009 at 8:22 am
Gus, you beat me and you provided additional info that is quite appropriate. Three thumbs up!
November 4, 2009 at 8:44 am
Thanks for that, both of you. It was looking at the execution plan and stats that made me suspect there's a misunderstanding about Views here. I found it hard to believe that SQL would be daft enough to run the full query and then filter it to my needs, but I've only recently started getting to grips with such exciting things as execution plans and statistics, so wasn't sure I'd just misunderstood something.
Thanks for your time.
November 4, 2009 at 8:46 am
You're welcome.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 4, 2009 at 8:49 am
Yes, your welcome!
November 4, 2009 at 11:06 am
If you're just starting with views take a look at this link from MS where it explains the use of INDEXED VIEWS. (http://msdn.microsoft.com/en-us/library/dd171921.aspx)
Pedro
November 4, 2009 at 11:46 am
PiMané (11/4/2009)
If you're just starting with views take a look at this link from MS where it explains the use of INDEXED VIEWS. (http://msdn.microsoft.com/en-us/library/dd171921.aspx)Pedro
Indexed Views aren't necessarily the answer. There are restrictions on creating them and they aren't always the best alternative.
November 4, 2009 at 10:36 pm
GSquared (11/4/2009)
That means, no, it doesn't pull all the rows and then filter them. The query engine is smarter than that.
Ummm... not always true, Gus. If a view has, say, an aggregate SUM column in it and the filter in the WHERE clause in the outer query is...
WHERE aggSUMcolumn > 0
... then the whole view must materialize in order to determine when that aggregate is > 0.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 4, 2009 at 10:56 pm
Lynn Pettis (11/4/2009)
PiMané (11/4/2009)
If you're just starting with views take a look at this link from MS where it explains the use of INDEXED VIEWS. (http://msdn.microsoft.com/en-us/library/dd171921.aspx)Pedro
Indexed Views aren't necessarily the answer. There are restrictions on creating them and they aren't always the best alternative.
I agree, indexed views can cause all kinds of residual effects that you hadn't planned on, so use them sparingly carefully..
CEWII
November 5, 2009 at 6:49 am
Jeff Moden (11/4/2009)
GSquared (11/4/2009)
That means, no, it doesn't pull all the rows and then filter them. The query engine is smarter than that.Ummm... not always true, Gus. If a view has, say, an aggregate SUM column in it and the filter in the WHERE clause in the outer query is...
WHERE aggSUMcolumn > 0
... then the whole view must materialize in order to determine when that aggregate is > 0.
True.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 5, 2009 at 7:53 am
Indexed views can be very "tricky" but if you know exactly what you need and be sure you schema isn't going to change (not very likely on a product in development, but very likely on a "closed" app), indexed views can increase performance a lot.
You have to have the right "support" for it (nice disks, raid, ...) but when configured correctly and used properly can be very useful.
You have to know and read carefully all the docs so you can use it properly, so "Kids, don't try this in you SQL!!"
Pedro
November 5, 2009 at 7:57 am
Yeah, indexed views are definitely in the category of "Do not try this at home. Trained professional in a closed database." But, if you know what you're doing, they can be very, very useful.
I've used a few for pre-aggregated or denormalized data, or for partitioned tables, and they've come in very, very handy.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply