Queries and Views

[MSPhobe]

Suppose you had a table, "Books" with the following data, in fields "Title" and "Comments"

The Hobbit / Second edition
Goblet of Fire
Yearling / Pristine dust jacket
The Spire
Winnie-The-Pooh
Seven Pillars of Wisdom
Moby Dick / Rockwell illustrations

It is easy enough to create a query that will return all records where the book name begins with "The"... call it "Q-StartsTHE"
(Returns The Hobbit and The Spire)

and it is easy to create a query which returns all records where the comment is not null, not empty... call it "Q-CommentNotNull"
(Returns Hobbit, Yearling, Moby Dick)

With the "Create query- design view" it is easy enough (if you notice the "Queries" radio button at the top of the "Add tables/queries) dialog!) to create a "meta query" from the first two queries, on to return records which are part of the result of both queries. I.e., with this data, returns just The Hobbit's record.

===
OR, the job can be done using "views". You'd make one equivalent to each of the "starter" queries above, and then build a query based on the views, which will be on offer as "Tables" in the "Add table/query" dialog

(To make a view, start at the TABLES part of the OOB main project window... there it is, alongside "Create Table.."/"Use wizard.. table.)

-----------------------------
Can someone tell me what, if any, pros and cons exist between the two alternatives? Assuming that I am only ever going to make databases running on the embedded HSQL engine. (I realise that if you get a bit fancier, and have users, and can grant them permission to see some things and not others, views can be useful.)

[eremmel]

In the end there is not much difference in respect to embedded HSQLDB. Your query/view definition will be substituted in your final query and the total expanded query will be processed.

Queries are stored in your Base document and views are stored in the database. When using an external database, views might be visible to other database users that are not using the Base document.

With views you can do fancy things depending on the database, but that is not what you are looking for.

[MSPhobe]

Helpful! Thank you! I know a bit about how views have a place when an external server is involved. Especially if it is a multi-user, database-on-a-separate-machine, accessed across LAN or WAN situation. THERE I can see that the designer needs to choose wisely between them.

Anyone know of a reason/ situation where one or the other is better if just using OO Base, with embedded HSQL, on a single machnine, one-user-at-a-time only access?

[eremmel]

I like to suggest to make an artificial difference between views and queries based on reuse:
Use queries to break complex queries into sub-queries, where reuse between queries is not expected.
Use views to build queries that are likely be used in multiple queries: E.g total the number of books per author.

In your example you have two valid queries that both are meaningful, combining them with a UNION gives you the super-set of both queries and joining them gives you the common ones of both sets. When you expect to have multiple combinations an build queries on top of these I would go for views in the case of embedded HSQLDB.

On the other hand a query which selects books with comments is more general than a query that select only books with a title that starts with 'The'. The former is a better candidate for a view than the latter. So it is to a large degree a matter of taste.

[keme]

... what eremmel said.

Edit: Please note: My following statement may well be theorethical hogwash. I kept it for completeness of the thread, as it is referenced in future posts. See the continued discussion below.

Also, when you have a query based on subqueries, and one subquery is repeated in the main query, there may be a benefit to using views. The view is fetched only once, but the subquery is generated anew with each instance.

I do not know whether this is always the case, nor have I tested it myself. There might also be cases where persistence is not desirable. I haven't investigated it to any extent...
https://dba.stackexchange.com/questions ... s-subquery

[eremmel]

@keme, we are getting now in the very detailed area's of native SQL implementations. Despite the the main answer of the question you referenced, I've never seen this happen in any SQL execution plan, except when it comes down to materialized views. In my work as software performance engineer (20+ years, yes I should update my picture), I've been testing with views and CTEs on major commercial databases but noticed that only materialization worked out.
So if you have more reference material, please share.
Thanks, Erik

[keme]

@keme, we are getting now in the very detailed area's of native SQL implementations. Despite the the main answer of the question you referenced, I've never seen this happen in any SQL execution plan, except when it comes down to materialized views. In my work as software performance engineer (20+ years, yes I should update my picture), I've been testing with views and CTEs on major commercial databases but noticed that only materialization worked out.
So if you have more reference material, please share.
Thanks, Erik

Nope. Only something I recalled from I'm not sure when - university studies or some course later in life. Something which got confirmed by search as documented by the link I provided above.

For practical purposes I trust your opinion more than lectures and more than (rather loose) web forum statements. You have proven your worth more than once.

I stand corrected (and happily so). Thanks!