Where clause inside / outside of view

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Where clause inside / outside of view

Author

Topic

gambool
Starting Member

1 Post

Posted - 2011-01-21 : 09:56:42

Hi,

I have a view which is used in an web application. Certain pages use this view multiple times, each time filtering by UserID and LessonID.

-- -----------------------------
alter view vwUsersStatus as

select
row_number() over(partition by eu.UserID, e.LessonID order by sr.Rank desc, eu.Score desc, eu.Status desc) as RowNumber,
eu.UserID,
e.LessonID,
ved.StartDateTime as EventStartDate,
eu.Score,
case when eu.Status is null then 'NotStarted' else eu.Status end Status,
sr.Rank
from
EventUsers eu
join Events e on eu.EventID = e.EventID
join vwEventDates ved on e.EventID = ved.EventID
left join StatusRankings sr on e.Status = sr.Status
-- -----------------------------

e.g.
-- -----------------------------
select * from vwUsersStatus where UserID = 100 and LessonID = 200

Used in this way, the view takes around 2s to execute. So, if a page uses the view 20 times, that's 40s. Not good. However, if I run the SQL directly, it returns instantly.

e.g.
-- -----------------------------
select
row_number() over(partition by eu.UserID, e.LessonID order by sr.Rank desc, eu.Score desc, eu.Status desc) as RowNumber,
eu.UserID,
e.LessonID,
ved.StartDateTime as EventStartDate,
eu.Score,
case when eu.Status is null then 'NotStarted' else eu.Status end Status,
sr.Rank
from
EventUsers eu
join Events e on eu.EventID = e.EventID
join vwEventDates ved on e.EventID = ved.EventID
left join StatusRankings sr on e.Status = sr.Status
where
eu.UserID = 100 and e.LessonID = 200
-- -----------------------------

The execution plan for the direct SQL looks nice and tidy, mostly an index seek (51%) and a hatch match (41%). However the execution plan for the view uses an index scan(22%), as well as lots of additional costs (Sort 35%, Parallelism 21%).

So, what I want to know is why there is such a huge difference between what is effectively having the where clause being inside the view and outside?

I hope I've explained this well enough, however let me know if I need to clarify anything.

Thanks.

Lee.

KenW
Constraint Violating Yak Guru

391 Posts

Posted - 2011-01-21 : 14:52:19

It's a lot more than having the WHERE inside or outside the view.

A view requires a select from all the rows for those matching the view's criteria. That entire rowset is then subject to the filtering of your SELECT from that view. IOW, you have the full SELECT inside the view (which can use indexes for all or part), and then another SELECT (without the benefit of indexes to help) from the results of the view's SELECT.

The direct SQL, OTOH, is a single operation where one pass through the data (most likely using indexes for all or part of the work) retrieves the final rowset.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources