Performance: View or Query

Question

Suppose I have a SQL Query that isn't really "nice".

The SQL Query has a lot inner join with long conditions, and finally a simple "where" statement.

Now suppose I make a View that corresponds to that SQL Query, excepting the final "where" statement. So that instead of make that long query, I use the view and simply add the "where" statement.

My question is the following: Which one would have best performance?

I'm not sure if the "View" option corresonds to two sql statement, instead of the one sql statement of the first option. Or before executing the query, the "View" query is "pasted" in the more simple query and is "the same".

Answer 1

No difference. The view uses the same query as the query alone.

To be different it would need to store it in a view table view don't, they're just queries made to look easy from the outside.

Answer 2

Assuming the DBMS you're using has a cost-based query optimizer, you'll probably not get any better performance when a portion of the query is converted to a view. You can confirm this with your database's query explain tool by comparing the estimated cost of both versions of the query.

I recommend using the query explain utility to determine exactly where the query becomes expensive, since the location of a query's pain points are often unexpected. It may seem that all those joins are slowing things down, but they may be exploiting indexes that minimize the I/O and CPU cycles required to connect the rows. Many times the query bogs down because an unindexed column is referenced in a WHERE or ON clause.