Is querying over a view slower than executing SQL directly?

Question

Scenario:

I have 3 tables needing to be joined together, a where clause to limit the result set, and only a few columns from each table being selected. Simple. However, the query to do this isn't very pretty, and when using an ORM between the database and the application, its like trying to put a square peg into a round hole.

My way to get around this is to create a view that embraces the query and now my application model maps directly to a view in the database; no more crazy mapping the ORM layer.

Question: Assuming no other factors come into play here, will the query against the view incur any additional performance penalties that I wouldn't have hit if I executed the SQL statement directly? - This is not an indexed view, assume the same where clause, keep this simple.

I am being led to believe that a view suffers from extra overhead of "being built". My understanding is that with all else the same, the two should have identical performance.

Please clarify. Thanks!

Answer 1

In Oracle, the performance is the same. A view is really a named sql statement. But fancier.

When you start nesting views, and joining views with other table or views, things get complicated real quick. If Oracle can't push your filters down the view to the table, it often has to materialize (build a temp table of) parts of the query, and this is when you get the bad performance.

Answer 2

From MSDN: View resolution

When an SQL statement references a nonindexed view, the parser and query optimizer analyze the source of both the SQL statement and the view and then resolve them into a single execution plan. There is not one plan for the SQL statement and a separate plan for the view.

There should not be any different performance. Views helps you organize, not any performance enhancement. Unless you are using indexed views.

Only the definition of a nonindexed view is stored, not the rows of the view. The query optimizer incorporates the logic from the view definition into the execution plan it builds for the SQL statement that references the nonindexed view.