Sqlite3 views affect performance?

Question

Trying to better understand the database in general and sqlite3 in particular:

Are views in sqlite3 mainly an organizational feature that allows you to break down complex queries into a series of smaller ones; or do views really affect the performance of queries that use them?

I noticed that the views are stored in the database itself as part of the schema. The views stored on disk are updated dynamically updated as dependent tables; or are they priced on demand?

Thanks.

Answer 1

Views will always be executed on request (sqlite3 or otherwise), so the results they return are never saved.

As far as performance is concerned, while I cannot speak directly with sqlite3, usually using views will have a slightly lower overhead since the query parser / scheduler does not have to re-process the raw sql at each execution. He can parse it once, save his execution strategy, and then use it every time the request is executed.

The productivity gains you see with this will usually be small, in the grand scheme of things. It really only helps if it is a quick request that you execute frequently. If his slow request is infrequent, the overhead associated with parsing the request is not significant. Presentations, of course, provide a level of organization that is good.