Creating a database of what's in my database?

[akakjs]

Speaking as a DB n00b.. but what's wrong with using views?

I use one in a database I have created to GREATLY simplify some of my SQL queries. Admittedly this is a very small database compared to the huge ugly monsters some of you guys obviously deal with.

Nothing per-say; they just hide detail, which means you can't optimise you queries as well as you could if you saw all the tables and only select the ones you need. To be honest if you use them properly there's nothing wrong with them, they just tempt you into using them when you shouldn't which results in slower queries.

akakjs

 

[rpstewart]

Speaking as a DB n00b.. but what's wrong with using views?

I use one in a database I have created to GREATLY simplify some of my SQL queries. Admittedly this is a very small database compared to the huge ugly monsters some of you guys obviously deal with.

Views, certainly in a DB2 context, are very inefficient principally because they cannot be indexed. Generally a view will give a subset of columns rather than rows so you can be dealing with a serious amount of data - 50 million row tables are not uncommon in enterprise scale data stores.

Any query against the view will cause the view definition to be re-run, normally to a temporary data set on disk so in addition to the query execution time you have a delay while the writes occur. Then the engine has to query that unindexed data set so a table scan is inevitable as is the consequent delay while each row is read and compared to the query predicates. It only gets worse if some lunatic user joins something to the view because you can find the engine doing a nested loop join where it tablescans the temporary data to check for a match against each row in the other table.

Therefore in general views are to be avoided. You can get away with them in a small database but at the enterprise scale they don't give you the control over the query access paths that you have from physical tables. As a result it's preferable to replace views with either summary tables created as part of an overnight batch, automatically updated materialised query tables or in the case of high volume transactional systems an entirely separate reporting database which keeps the production systems safe from the database surfers.

 

[SiriusB]

I see.

Well in my case I have a MySQL database with the following tables

user
rank
status
article
section
area

Table rank and status are linked to user. Table user is linked to article, and article is linked to the section and area tables.

And I have created a View called articleview using the query:

CREATE VIEW articleview AS
SELECT user_name, rank_name, status_name, article_title, article_body, article_date, section_name, area_name
FROM ((user JOIN rank ON user_rank = rank_id) JOIN status ON user_status = status_id) JOIN ((article JOIN section ON article_section = section_id) JOIN area ON article_area = area_id) ON user_id = article_user;

Now as you can see that's a chunky bit of SQL.

Most queries don't require the view as they are pretty simple, but I have a couple of queries that select the most recent article per section or per user.

The per article query using the view articleview:

SELECT av.user_name, av.rank_name, av.article_title, av.article_date, av.section_name FROM (
SELECT section_name, MAX(article_date) as maxdate FROM articleview GROUP BY section_name)
AS x INNER JOIN articleview AS av WHERE av.section_name = x.section_name AND av.article_date = x.maxdate;

*I just typed those queries from memory so forgive any mistakes, but you should get the idea*

As you can see both queries are pretty big, but without the view I can only imagine them being absolutely massive [I wouldn't know where to start!] I actually made a thread about trying to select the most recent date per section and the best someone could come up with was a big query i couldn't get my head around and was very, very hard to modify.

Using the view has helped me quite a lot by simplifying my query, but I am open to suggestions if there is a better way of doing it