Home » Uncategorized » Currently Reading:

Proving Views Can Hurt Performance

December 3, 2009 Uncategorized 4 Comments

Content rating: Beginner to experienced

For some of you, this will be a "no duh" blog.  But I've had this argument with people, so here we go.

Views can hurt performance. Not by their very existence, no.  Not by rule, no.  But in certain situations, yes. The situation is: If you're querying off of a view that's pulling much more data than you need (horizontally or vertically), and/or doing more processing than you need (outer joins, date functions, case statements), your query may have worse performance than just running it off the base tables.

As an example, I refer you to my previous blog, in which we restored Brent Ozar's Twittercache database. Get it and restore if you want to follow along.

Once you've done that, create this view in the TwitterCache database:

ALTER VIEW userTweetsHistory

AS
SELECT a.[text],

b.[USER_ID] ,

c.NAME ,

c.screen_name ,

c.cached_date ,

B.cached_date AS HistoryCachedDate,

b.followers_count

FROM Statuses a

JOIN UsersHistory b ON b.[user_id] = a.[user_id]

LEFT OUTER JOIN Users c ON c.screen_name = b.screen_name

AND CONVERT(VARCHAR(10), c.cached_date, 101) = CONVERT(VARCHAR(10), b.cached_date, 101)

WHERE c.screen_name NOT LIKE 'SQL%'

AND c.cached_date > '11/1/2009'

What we have here is a view…a view that no one would ever really need, but a view nonetheless.  I've seen many similar in my day, so don't try to tell me I'm making this unrealistically complicated.  What we want is the screen_name and cache_date for 'midnightDBA' where the follower_count is > 100. Turn on "Include Actual Execution Plan", and run these two statements together:

SELECT screen_name ,

HistoryCachedDate

FROM userTweetsHistory

WHERE screen_name = 'midnightdba'

AND followers_count > 100

SELECT screen_name ,

cached_date

FROM UsersHistory

WHERE screen_name = 'midnightdba'

AND followers_count > 100

The first reads from the view, the second from the base table.  The first query is 78% of the batch (on my box)…it takes FOUR TIMES AS MUCH EFFORT for SQL to run the query from the view as it does the query from the base table. Again, this is because the view is doing more than we need it to do for this query.

The bottom line is NOT that views are bad, or that views hurt performance.  The bottom line is that inappropriate views hurt performance.

Thanks for playing,

Jen McCown

http://www.MidnightDBA.com

Currently there are "4 comments" on this Article:

  1. [...] talked before about how inappropriate views hurt performance, and I will talk in the future specifically about how nested views hurt performance, but today I [...]

  2. [...] blogs on performance, views, and nested views: Proving Views Can Hurt Performance, Detangling Nested [...]

  3. Eric Wisdahl says:

    I’m not saying that the premise of the article is wrong, but the view goes to an inner join with the where clause, so the performance measurement between the two is not equal. One will be joining tables and only returning the records that match the criteria of the second table, the other strictly goes against a single table. Depending on the cardinality of the second table you also might return more records than you would expect if you were comparing just the values in the base table to that returned from the view. Of course, that more than proves your point there… know what the view should be used for and only use it for that purpose.

  4. blah blah says:

    Inappropriate views indeed. I’ve used views in access & sql server to consolidate business logic. But, if, say, I need to tweak the view for 2 separate things … I’ll make 2 separate views, and name the new one something unique to tell the difference (eg: V_RATEPLANS_ACTIVE, V_RATEPLANS_INACTIVE).

    I think some folks get this idea in their head that they need to make a base view, then use it to base other views that drill down into … and that kills performance.

Comment on this Article:







Minion Reindex by MidnightDBA is here!

 

Excellent Index Maintenance

Download Minion Reindex, log feature requests, read documentation, and sign up for the newsletter at MidnightSQL.com/Minion!


 

MidnightSQL Consulting

Need help? Got an emergency? Write us at Support@MidnightDBA.com!

We can schedule time to help with your backup/restore issues, high availability and disaster recovery setup, performance problems, and a great deal more. Very often, we're even available on the moment for downtime issues and emergencies.

For more information about MidnightSQL consulting, email us or check out www.MidnightSQL.com. Happy days!

Where are We?

November 3-7: PASS Summit, Seattle, WA

PASS Summit: Jen is presenting How to Interview a DBA: A Panel Debate on Thursday 11/6 1:30pm, room 401 (along with Adam Machanic, Sean McCown, Bob Pusateri, and Michelle Ufford).

PASS Summit: Sean is presenting Performance Tuning Your Backups on Wednesday 11/5 3:00pm, room 602-604.

December 11: Presenting "Powershell Cmdlets.." at Alaska SQL Server User Group

January 30: "Become an Enterprise DBA" precon at Austin SQL Saturday

Blog Posts by Category

DBAs@Midnight

How to Eat Pop-tarts
Watch DBAs@Midnight live on Fridays,m 11pm Central time

The best database career advice you’ve never heard!

DBARoadmap.com

The DBA Roadmap Seminar is 7 MP3 tracks (over 5 hours!) of insider guidance on your database career. We'll teach you how and what to study as a DBA, weigh in on controversial resume debates, teach you to recognize a worthy recruiter, and discuss the new professionalism of interviews. Also some bonus materials, PDF companion guides, and really spiffy intro music!

Once your $99 PayPal payment is submitted, you'll get the download link in e-mail! (Download is a 370Mb ZIP file.)

Become a DBA. Become a BETTER DBA. Use the Roadmap.

Visit www.DBARoadmap.com for info, forums, and more!

Add to Cart View Cart

Cunningham’s Law

"The best way to get the right answer on the Internet is not to ask a question, it's to post the wrong answer."
Relevant: http://xkcd.com/386/