SQLite Views

Are there any performance implications when using Views? Faster? Slower?

On a small dataset of just 3000 rows they were both at .008 second. I’m just wondering how it scales.

In most cases that I am aware of … there would be little performance gain.

A view (in my opinion) is most useful for encapulating a specific Query to be used by multiple processes

For example you could have a Table with data from all 50 USA states… and a View might return only California

This would keep you from having to code into multpile apps (or queries)

SELECT * FROM datatable WHERE state=“CALIFORNIA”

because that would be your view. So it is the same code, just a matter of WHERE it resides

Now MATERIALIZED VIEWS are different in how they work… but I am not sure who other than ORACLE supports them

I would suppose that this depends almost entirely on how the view is constructed and can’t be answered in generic terms. My guess is that a view doing something and a regular query doing the same thing would have little measurable performance difference.

[quote=37453:@Joseph Claeys]Are there any performance implications when using Views? Faster? Slower?

On a small dataset of just 3000 rows they were both at .008 second. I’m just wondering how it scales.[/quote]

Views just encapsulate the actual query/queries into a more convinient form. There is a microscopic speed penalty when the query gets rewritten (by database engine) using actual sql statements behind the view before executing it.

[quote=37455:@Dave S]
Now MATERIALIZED VIEWS are different in how they work… but I am not sure who other than ORACLE supports them[/quote]

postgresql >= 9.3

Does this indiscernible penalty grow exponentially or is it linear? Or is it a static overhead that would remain the same amount and thus become proportionally smaller?

Neither… it is a fixed amount… based on the query in the view… and sometimes may actually be faster for the view (by that microscopic amount) if the database engine stores the view precompiled.

Does SQLite precompile the view?

[quote=37453:@Joseph Claeys]Are there any performance implications when using Views? Faster? Slower?

On a small dataset of just 3000 rows they were both at .008 second. I’m just wondering how it scales.[/quote]

The other thing to worry about is that often times VIEWS are read only
The y_may_ be read / write but not always and it’s really going to vary depending on the underlying query, tables, db engine etc.

More often than not views are a nice way to encapsulate queries that you run into over & over & over in the business logic (like getting an order & all the order items on it) etc