15

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!

PAR
  • 195
  • 2
  • 2
  • 5
  • 2
    Depends on your database, and many other things. If it's MySQL, the answer is Yes it's slower, though. – nos Jan 07 '11 at 21:55

2 Answers2

7

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.

stian.net
  • 3,928
  • 4
  • 25
  • 38
  • 1
    As long as the view has been run before there should be no difference. In fact, the view may be slightly faster because it can have a cached query plan. – Matthew Jan 07 '11 at 22:07
  • 1
    It has been slower in my case. I had a synonym to a view in another database, so I didn't have to constantly join tables together to repeatedly get the same data in various reports. It became so slow, I went back to joining directly to the tables. I ran query analyzer on the view, on the sql, added every suggested index, all failed. Select * on the synonym took 3 seconds. Select * on the joined tables was near instantaneous. All DBs on the same server. So I'm perplexed. – ARLibertarian Jul 19 '19 at 17:24
5

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.

Ronnis
  • 12,593
  • 2
  • 32
  • 52