33 Million Row View Without Index

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

SQL Server Administration (2005)

33 Million Row View Without Index

Author

Topic

Analyzer
Posting Yak Master

115 Posts

Posted - 2009-04-27 : 08:59:13

Been given a poorly designed application which uses a view within several queries.

There are no unique keys or columns on the view or even within the base tables to create the initial clustered index + the base tables can change so I cannot use SCHEMA BINDING. Using ROWCOUNT(BIG) I can see the view returns 33 million rows and the WHERE clause of the query is not part of the view.

Is the query performing a scan of the view or would the optimizer use underlying base table indexes?

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2009-04-27 : 12:04:54

A view, unless it is an indexed view, does not store any data. All a view is is a saved select statement. When someone queries the view, SQL expands the view's definition into the query and then runs the resulting statement.

So, say we have a view like this.
CREATE VIEW MyView AS
SELECT col1, col2, col3 FROM SomeTable WHERE ColX>20
GO

and then someone queries the view:
SELECT col2, col3 FROM MyView WHERE Col1 = 42

SQL takes that statement and, as part of the parsing and binding, replaces the name of the view with the definition, as such
SELECT col2, col3 FROM (SELECT col1, col2, col3 FROM SomeTable WHERE ColX>20) MyView WHERE Col1 = 42
It's then going to compile and optimise that statement.

Grant wrote a bit about this recently - http://scarydba.wordpress.com/2009/04/24/unpacking-the-view/

--
Gail Shaw
SQL Server MVP

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-04-27 : 16:51:52

Good article Gail.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2009-04-27 : 17:12:54

Just to be clear, I didn't write that one. That's Grant Frichley's blog.

--
Gail Shaw
SQL Server MVP

Analyzer
Posting Yak Master

115 Posts

Posted - 2009-04-29 : 03:45:34

Thanks GilaMonster. Article pretty much breaks it down. The query looks up a poorley designed set of table schemas making the possibility if indexing the view worthless. I'll push back and recommend tuning the view.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources