CODEWITHSUNDEEP
viewsql-server-2008-r2
user170934
user170934

Reputation:

SQL Server VIEW Performance

If I have a VIEW with a bunch of INNER JOINs but I query against that VIEW SELECTing only columns that come from the main table, will SQL Server ignore the unnecessary joins in the VIEW while executing or do those joins still need to happen for some reason?

If it makes a different, this is on SQL Server 2008 R2. I know in either case that this is already not a great solution but but I'm attempting to find the lesser of 2 evils.

Upvotes: 6

Views: 4339

Answers (3)

Paul Sasik
Paul Sasik

Reputation: 81489

Even if the optimizer ignores unnecessary joins you should just create another view to handle your particular case. Use and abuse of views (such as this case) can get out of hand and lead to obfuscation, confusion and very significant performance issues.

You might even consider refactoring the view that you're planning on using by having it join a set of "smaller" views to deliver the same data set that it does now... if it makes sense to do that of course.

Upvotes: 1

JNK
JNK

Reputation: 65167

If you don't pull fields from those tables, it may be faster to use an EXISTS clause - this will also prevent duplicates from the JOINed table cause dupes in your results.

Upvotes: 1

Martin Smith
Martin Smith

Reputation: 453358

It might ignore the joins if they don't actually change the semantics. One example of this might be if you have a trusted foreign key constraint between the tables and you are only selecting columns from the referencing table (See example 9 in this article).

You would need to check the execution plan to be sure for your specific case.

Upvotes: 4

Related Questions