Work fun

[baavgai]

Some day this bloody query will end and then I'll be able to figure out what the hell the developer did to make it so craptastic. Well, she joined about five views, sometimes twice, and that's on the top level. I'm sure it worked brilliantly on a few ten row tables in dev. Arrrrrrgh.

Email conversation de ju: "Can I get that in Excel?" "It a preformatted report, so no." "Can I get this in Excel?" "No." "Can I export this to Excel?" "NO!" The budget entry program will be locked down tighter than a virgin's g-string tomorrow. Can you tell?

SQL finished, diagnostics didn't reveal crap, except that it's slow. This should be fun.

Comments

[sansobel.livejournal.com]

eeewwww join on 5 views... did she forget any primaries?

[baavgai.livejournal.com]

Actually, once you start joining views, you often loose the primary keys and indexes.

Even if they're in there, a complex view can befuddle a parser. Worst case, the entire view is run and loaded into temp for the join with zero indexes. It's ugly. I keep telling them, "Views are evil." I'm thinking it will be lecture time tomorrow.

[maccuswell.livejournal.com]

About the only use for them I know of in Postgres is to format the query results.

Postgres doesn't support column-level security, so you'd think views would be the perfect method to only display certain columns from a table. Trouble is, security checks cascade from the view definition back to the original table, so just because a user has select rights to a view doesn't mean the view is accessible if the user can't select the table it references. It's still an all-or-nothing deal.

I use them rarely, and only as shortcuts for very specific data selections.

[sansobel.livejournal.com]

I know I currently use views for goldengate to replicat onto servers. But I could easily just send it right over. However, the views I use are fairly simple. (maybe 6 columns being pulled in by a couple joins). Hope you find the issue. I hate waiting on selects.

[baavgai.livejournal.com]

Sometime you have to wait, I just hate doing it for no reason.

I'll find the issue, it's not like it can hide. That's the nice difference between simple SQL and programming. In a database all the factors are in front of you, it just takes time ( and cursing and caffiene ).

[maccuswell.livejournal.com]

Just think: if it were a "real" db like MySQL, it wouldn't even support subqueries, so you'd speed it up by returning result rows to the application space, iterating through them, and launching further queries from there! That would speed things up tremendously!

Yes, I have heard that argument used.

[baavgai.livejournal.com]

I suppose "short cut" is a pretty good description. Views are used for filtering and also making complex join logic look like a flat table to the user.

e.g.
create view vw_invoice_summary as
select a.invoice_number, b.customer_name, c.invoice_total
from invoice as a, customer as b,
(select invoice_id, sum(line_amount) as invoice_total from invoice_line group by invoice_id) c
where a.invoice_id=c.invoice_id
and a.customer_id=b.customer_id

This is logical, if a little kludgy. It's not the fastest thing in the world, but it does the job. Now, along comes my junior dba and does something like this:

create view vw_invoice_report as
select a.invoice_number, a.invoice_total, b.customer_name, b.customer_id, b.billing_name, b.postal_code
from vw_invoice_summary as a, customer as b
where a.customer_id=b.customer_id

Looks innocuous, until you realize that you're doing much extra work for absolutely no reason. In fact, you're actually locking yourself. Views on views can lead to some hairy self referencing. It can create a cascading tree of doom.

A good use for views in both Oracle and SQL Server is sneaking in a business layer. In both those systems ( and others ), views can be created with "instead of" triggers. These triggers handle DML, so insert, update, and delete can be essentially handled procedurally. If you ever have to place complex logic in the middle tier, like validation checks and auto generated ids, the value of this will be apparent.

As for the mysql argument, well, doing it all in the application layer is one way to go. It makes sense for smaller stuff and where your knowlege base is primarily programmers. Still, a little database level referential integrity makes dbas sleep better.