Is a query slower than executing SQL directly?

Question

Scenario:

I have 3 tables that need to be joined together, a where clause to limit the result set, and only a few columns from each selected table. Simply. However, the request to do this is not very nice, and when using ORM between the database and the application, it is like trying to put a square anchor in a circular hole.

My way around this is to create a view that spans the query, and now my application model maps directly to the view in the database; no longer crazy display of the ORM layer.

Question: Assuming that there are no other factors, will the query against the view incur any additional performance penalties that I would not hit if I executed the SQL statement directly? . This is not an indexed view, accept the same as the sentence, keep it simple.

I am convinced that the submission suffers from the additional overhead of "creating." My understanding is that for everything else the same thing, two should have the same performance.

Please clarify. Thank!

Answer 1

From MSDN: View Resolution

When an SQL statement refers to an unindexed view, the parser and query optimizer analyzes the source of both the SQL statement and the view, and then resolves them into a single execution plan. There is more than one plan for the SQL statement and a separate plan for presentation.

There should be no other performance. Views help organize, not improve, performance. If you are not using indexed views.

Only the definition of the non-indexed view is saved, not the view row. The query optimizer incorporates the logic from the definition of the view into the execution plan that it builds for the SQL statement that references the unindexed view.

Answer 2

In Oracle, performance is the same. A view is indeed a named sql statement. But an amateur.

When you start nested views and join views with another table or views, things get very complicated. If Oracle cannot push your filters down the view into the table, it often needs to materialize (build a temporary table) the parts of the query, and this happens when you get poor performance.