View with Join performance

from a performance standpoint, is there a difference in creating a view that contains a (inner) join and when needed just reading the 1st table to get the foreign key and then reading the second table?

A “view” is basically just a “canned” SQL query for convience…
So its performance as a VIEW or including the code that made up the view in a larger query is the same…
(a view MIGHT have a slight (very slight) advandage the database engine “pre-compile” it…)
but for me, it just makes things cleaner, and reusable (like Methods in Xojo)

Note that views can often give you huge performance bottle necks also. Mostly then because of then not filtering out most performance significant criteria first when doing complex joins.

Without knowing what database you’re referring to its hard to say that “in general X will be true”
This is going to vary from database to database
Some can, like Dave mentioned, precompile or go one step further and “crystallize” a view (actually turn it into a table)
Others can optimize the view along with whatever subsequent query is done against the view as though you wrote the view “inline” This way the view doesn’t have specific penalties for using a view

That would be a “materialized view” in Oracle, which we used alot, especially when the real data was in a different database and/or server

Should have given more details but I AM looking for “In General”…

Right now I working on a fairly (actually quite) small SQLite database. Has 30+ tables, but only a couple will have more than a couple thousand rows… a couple with a few hundred and most will have far fewer on average.

I’m just trying to develop a “best practices” approach.


there is no “best” approach… this is governed by your requirements…

Basically if the query you would use to make the VIEW would be used in multiple places in your code… then make a view
if it is only in one place, then don’t make a view, unless doing so would make the code easier to read/understand/maintain

or use a CTE which is basically a query prepended with a query that can be used as if it defines a table right inline
which is akin to a view but doesn’t have to be a permanent fixture in your DB schema


Thanks guys, I think I have a clearer answer