View vs Join

Question

Post reply

View vs Join

Christopher Stobbs

SSC-Insane

Points: 21098
More actions
July 15, 2008 at 2:42 am

#193905

Hi all,
I have a view with 2 inner joins and 4 left joins returning 25 Columns, 8 of which are calculated.
My question is this.
I always thought that views acted like tables, meaning that if I called this view and even though I'm only using 2 columns it doesn't affect the performance of the view.
Therefor, should I still use the view for just returning 2 columns or should I extract the logic/query from the view and add it to my new query to get my results?
Thanks
Chris
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
SQL-4-Life

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 1

HI All,

Did some testing.

Join on View:

CPU time: 15Elapsed Time 27

Logical Reads 453 (from on 6 tables)

Join on Extracted Logic:

CPU time: 16 Elapsed Time 312

Logical Reads 457(from on 10 tables)

The logical reads on the last one is confusing cause my query only has 7 tables in the join ?

Is this once again because the view is already planned or what ever when it's created?

Thanks

Chris

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 2

Hi all,

WEll the results above are not 100% as I had the query plan that skewed my numbers.

Here is what I have now.

With View:

CPU Time:16

Elapsed Time:16

Logical Reads:451

Scan Count:2

Without View:

CPU Time:16

Elapsed Time:15

Logical Reads:447

Scan Count:3

So I'm still not sure which is better?

thanks

Chris

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 3

A view is just a stored query. They are cached and perform the same as if you just ran the query itself unless you have schema-bound and indexed it.

So, if you create a view and use it in a query or put the query the view is based on in a sub-query, they are going to perform the same. The optimizer will make sure of this. The big down side to using views is often they are re-used but not all of the joins or columns are needed. When you do this, you incur a bit of unnecessary overhead. Usually, if you re-create the logic you can get better results because you will not need to include all of the tables or columns in the view. On the other hand, re-creating logic repeatedly is bad because if you have to make a change, you have to make it in multiple places. So, seek balance.

Grant Fritchey SSC Guru Points: 398679 More actions · Answer 4

Take a look at the execution plan. Usually (and there are exceptions) you'll see that a query with a view and a query without a view, both of which reference the same tables, in the same order, with the same join criteria, will generate very similar or identical plans.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 5

Thanks for all the advice everyone 🙂

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life