Last thing first. 🙂
L' Eomot Inversé (5/1/2013)
So we can either continue to try to understand each other - which may be difficult, given the different backgrounds - or we can just accept that we have done different things and therefor have needed to take different approaches to doing them.
Without different viewpoints one can never learn. I'd prefer to try to continue the discussion to at least be sure we understand each other, even if we may disagree at the end. I will, however, strive for more clarity.
I too have concerns about using views, and have a very strong preference for providing a procedural interface to the apps - not because I think the app developers will write bad SQL (at least it won't be any worse than what most DBAs write, in my experience) but because they provide a functionally less constrained interface, hence poorer modularity. But of course views provide a functionally more constrained interface than does direct access to base tables, because that direct access allows the app to be dependent on the base schemata, ie there is no module boundary at all between the database and the app.
While in my experience most app developers DO write horrible SQL, but otherwise I agree with what's above.
Data width is no more a concern with views than it is with base tables - and possible less of a concern because if a sloppy app developer writes select * against a view he gets only the columns which are in the select list of the view, whereas if he writes select * against a join between base tables he gets all columns in those tables.
This is a place where I think we're starting to disconnect. In my experience, views tend to end up overloaded with more joins than are necessary for any particular query. This is more what I meant by data width, and it was a horrible term. Properly requested information from a view that expands through the optimizer properly is no worse than most other calls. It's the extra stuff that comes along for the ride that concerns me.
Security is potentially better with views than with direct access to base tables, so direct access to base tables is more often illegal in production that access through views.
When I mentioned this, I was more thinking ANY direct access to large volume data is illegal, in any form. SEC not so much but being able to pull up patient records in mass can be a significant concern. There are ways to address this, I agree, but many places I've worked at err on the side of paranoid caution.
I think we are talking about different things here: "keeping views the same constantly" doesn't have any clear meaning to me. What I want is freedom to change the base schema without changing the apps - so I want to present the same view to the app when the base schema changes, and have no objection to having to rewrite the view in order to handle the new base schema.
We're saying the same thing, just with different words. The view being the same is it's output that needs to stay consistent. Apps shouldn't be that close to the data layer in most circumstances, at least via the design philosophies I'm comfortable with.
I also want freedom to change the apps view of the data without changing the base schema - for example I may want to allow the app to see historical data which it couldn't see before, which might mean either changing a view to add some new columns or adding a new view.
This, to me, sounds like you're more familiar with coding a one size fits all view so the app can come at it with multiple approaches without having to design a proc for each call to the database. My concern with this is if your app is going to do this, it has to use dynamic SQL. To me, that should be the exception for a particular circumstance, not a design method.
And I also want to be able to add columns to a view and have the old version of the app continue to work without change, so that most of the time when an upgrade requires both schema changes and app changes I can upgrade the base schema before I upgrade the app instead of having to synchronize the upgrades - this is often useful in ensuring that you only have to support one version of the base schema even if your customers are using 4 different releases of the app.
This is not a concern I would have thought to attempt to address via views, but then my experience is always to release a SQL upgrade script (with a rollback handy) for each front end app deployment. I don't expect the app and the database to go out of sync that way.
These are all the classical reasons for wanting strong modular boundaries and concealing the internals of one component from other components so that developers can't accidentally create unwanted dependencies between modules and thus place unwanted constraints the way the business can develop and evolve.
This is where I think we're differing wholesale. Unless I'm ordered to by on high or it makes sense to cure a problem, my app devs would never even see a view unless they're writing their own procs, too. Dynamic SQL, transactional concerns, data volume... there's been so many problems with a tightly wound app-db integration that I do everything in my power to avoid it and make sure my app devs get exactly what they need with a simple proc call.
Which leads us to how we got into a different set of assumptions about who would be using this type of optimization:
My main example is a system which has extremely intelligent, competent, and experienced people working to a set of rules which rigidly enforces best practise modularity. It is of course useful that this defends you from the idiot in marketing or PR who turns an ad-hoc query into the foundation of his whole function, but that's not the important property - support of narrow modular boundaries is. Without those narrow modular boundaries, you will very quickly have either a product that is so slow to evolve that your competitors eat up all your business or an product so complex and unwieldly and riddled with kludges that it costs the earth to support it.
I've found that neither of these are the case. I can easily modify data pulls without an app change via procedure to adjust for business rule changes, and with reasonable naming conventions and a quick proc name pull from the code by your app tier it's simplistic to get to a particular piece of code when necessary. Also, by completely de-coupling the app and database tiers via procedures, you allow for simpler troubleshooting of particular components when the old 'oh gods it's slow now!' problems arise.
As long as your SQL developers aren't lazing around, they should be able to keep up with app building on (in my experience) a ratio of about 1 SQL:4 App.
It's clear that we are not both addressing the same concerns. I think that's because of different experience.
I'm coming from a product development view, where the schemata, middleware, apps, and front ends all went together and were all sold to lots of customers who demanded new features, 24X7 availability, 24/7 support, minimal unscheduled downtime risk (we couldn't guarantee the system would continue to run if vaporised by a thermonuclear device, so we didn't claim zero unscheduled downtime risk), regular upgrades, conformance with data protection regulations, very user-friendly and enjoyable end-user interface that would conform with all the latest fads and fashions, all of which meant rapid change which in turn meant very thoroughly modular design and construction was essential. You seem to be coming from a situation where you have at any time a single user community, with a well understood requirement and rare and slow change, so rare and slow that complex interdependencies between app layer and data interface layer and base schema are perfectly acceptable and modularity doesn't much matter.
I also come from such a location, but I haven't worked in vendorware. All of my development and design has been either in-house custom software or web-portal backends. Rare and slow changes however are not part of either of those design philosophies, however, though your milage may vary depending on what shop you're at. Well understood requirements are rare, though I've been at a few places that were pretty good at it. Most places you're iteratively deploying because users don't want to look at 'old dev data', they want to see what they put in yesterday... even if they could put in stuff from yesterday in dev.
I disagree however that it's a complex interdependency. It's proc. *Best caveman voice* You call proc, I deliver data. Dependency done. *end voice* No app interface to the data directly in any way. I think that's the significant disconnect we have. I don't personally care how close the app devs get to the data model and understanding it. The app should never be that close.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA