John Fager (6/12/2014)
I'd say that when there is a book almost the size of the Bible describing just "Inside the SQL Query Optimization Engine"... um that's where we are in trouble already! It's CRUD for data.
I believe you've just illustrated my point, as well. 🙂 The engine is as complex a piece of machinery as the front end app. It's not just CRUD for data, though I understand the perspective. In the end, an app is just 'an interface for the data to tell us when it's requested and to sort and format the results' to the database.
SQL is ridiculously complex and the underlying architecture and language has simply not be rethought or advanced in ways that other languages and technologies have. The roots are deep, complex and old.
There are reasons for that. Note, this isn't a dig at the app devs, they can do this model because they don't need persistence. But for every 10 changes the app makes (and can be modularly rolled back out with code controls for the module), we make 1, and we make it slowly. Lost data can't be easily recovered. Worse is corrupted data being detected. I don't mean corruption caused by disk failures, I mean logic failures or unanticipated intersections of code.
Those roots are deep and old to keep things from moving around too much on purpose. I'm not saying we're against adopting new techniques and technologies, or methods of doing things. I'm saying the base libraries are never going to move much at once because of the caution that's built into our very atoms by the time we become a senior at a location.
A little clarity on this... It works great; not just on paper either. We don't avoid optimizing for a specific engine at all. Via architecture, we ensure that SQL optimization code stays in it's appropriate repository and is accessed in a very specific way and place.
AH! My apologies then, when I hear of this type of architecture I usually get the agnostic code discussion, so I jumped to an assumption there, apologies.
Caching, specific connections and query guts are out of their reach and safely in the hands of experienced app developers that can work very well with SQL and the DBA. When it's time to modify data stores, there is nowhere to look except in the repositories to make the switch. The new DLL's simply need to be added and the configuration changed. None of the business logic, web code, UI stuff and app code has to be changed or republished at all.
We started looking at this to be more fault tolerant as the cloud evolves and apps are mobile and sometimes temporarly disconnected on devices but still should work to a certain extent. As it turns out, good architecture also keeps app developers that don't understand SQL very well from writing code that generates queries that will never work under load.
You have a more advanced architecture than most I've heard of. I'm not sure, from my perspective, that I'd understand it thoroughly but you've definately thought it through and everything sounds like a solid approach from my limited knowledge. It seems like a lot of hoops to jump through but if you're not seeing a lot of pain in transferring between that many layers, and it's significantly helping your maintenance, awesome!
I'd love to see a white paper on that one. If I can make enough sense of it I'll hand it directly to every ORM default settings touting maniac I can find and help them build it. :w00t:
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