Evil Kraig F (5/1/2013)
Last thing first. 🙂
In this case that's the right order to tackle 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.
Yes, I agree. And your latest post has made me understand much better where you are at, and realise where you think I am at. I now think we are both in the same place, apart from minor incidentals (eg how horrible are app devs, or what is a narrow modular interface).
While in my experience most app developers DO write horrible SQL, ...
I guess it depends what you call horrible. In my experience most developers write SQL that I would be ashamed to put my name to, but then so do most DBAs; I don't call that SQL horrible, because to me "horrible" means "much worse than the usual run of the mill", but this is run of teh mill SQL and of course nothing can be much worse than itself. The good developers and the good DBAs both write excellent SQL, nothing like that.
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.
That extra stuff annoys me too, if there is any. Whoever wrote the query needs to learn to use filters, and maybe whoever wrote the view needs educating too. The fact that extra stuff can happen is the only reason I object to SELECT * (I don't object to it when it can't pull in extra unwanted stuff, as is often true for diagnostic code).
..., 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.
Data protection law is pretty fierce in Europe (including UK), and many organisations are paranoid about it. I'm inclined to believe that paranoid caution is the right approach. Given how utterly useless various anonymising schemes have turned out to be we can't trust the "experts" on what it's safe to let out, and given how frequently some idiot parks unencrypted data on a pen drive or pad or laptop and leaves it somewhere public we can't trust data users to care about data protection either. Maybe the only solution is to altogether prevent people getting large chunks of data. I've not had to deal with extremely sensitive stuff as opposed not really sensitive personally identifiable data (I've tried to instill paranoia in the non-technical management even over that).
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.
Dynamic SQL??? What on earth would that be for? I think you've misunderstood badly. I certainly don't want one-size-fits-all, every system I've been involved with since about 2000 has ended up (in some cases after I've overcome a lot of resistance from the backwoodsmen who want to embed DML statements directly in C++) as a system where the only thing the database presents to the outside world is a bunch of stored procedures.
..., 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.
So you never have to cope with a couple of servers and many hundreds of clients, no down-time permitted, client can be upgraded only when it is not in use; client is normally in use 24/7, which occassional periods of typically 4 hours where it is not in use; there is never a situation where many clients are simultaneously not in use; client upgrade is over net from servers, and will sometimes involve a complete reimage of client. Server upgrade has to be one server at a time, since there won't otherwise be enough servers to maintain acceptable response time.
I think that's the situation when you have to design for server and client being out of sync.
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.
That's what makes me think we are both on the same page, just misunderstanding. For me, using views to isolate the app from the schema is very much a second choice, something I won't do unless I'm ordered from on-high not to take everything to a procedural interface. I had misunderstood your earlier posts to say that you though views were a bad idea and it would be better to give the app access to the base schema; so my posts were arguing that using views was better than that, which seems to have given you the impression that I thought views were the right way to go. Talk about cross-misunderstandings!
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.
Tightly bound app-db integration is pure poison. If you have that you can't make upgrades of DB and app independent, and for me that was absolutely essential (a company goes bust if you can't do it issue).
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.
That I don't understand - you seem to be contradicting yourself: you will do all in your power to avoid the app and the db being wound tightly together, but you don't mind not having narrow modular boundaries? You can completely decouple the app and database tiers without narrow modular boundaries? That doesn't make any sense - if you have narrow modular boundaries two components can't be tightly wound up in each other; if you don't, they can and almost certainly are. That's the definition of a narrow modular boundary: a set of interfaces which gets the job done while preventing the two components from getting wound up in each other.
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.
Not terribly productive SQL developers then. I generally did far better than that :laugh:
Tom