Entity Framework & LINQ...
recently talked to a company who is looking for a new DBA. As got into the conversation about their environment, it came out that all (or at least the vast majority) of their SQL is coming from the application through the Entity Framework and is all coded in LINQ. Essentially, the C# developers own all of the "sql" code.
My first instinct was to simply maintain eye contact, not make any sudden movements and back out slowly... Later, upon reflection, I began to wander, is this really as bad as it sounds or am I simply projecting my own prejudices onto a perfectly reasonable model that I simply don't understand.
I've always held to the belief that the only access the application should have to the database is the ability to execute stored procedures and that all database logic should be contained in the database itself...
So, my questions are:
Has anyone here had any experience working in this type of environment?
If so, was I way off base with my initial reaction?
Is there an upside to this, that I'm simply not seeing, or is this simply a kludge for companies who don't want to invest in compliant SQL developers?
Any feedback or relevant links on the topic would be appreciated.
Yes. That's the same type of environment I walked into almost 6 years ago. There had been 4 years of silly cowboy design and development. We had 4 to 5 10 minute outages a day while something ran. The front end code kept 32 CPUs hopping at 40% average. I "moved in" right in the middle of all the front end Developers so I could hear them when they had a problem and offered my assistance. I picked the top 10 worst queries (which are almost never the longest running queries in such an environment) and show the Developers how they could be improved either using their front-end-centric code or the more sane way of using stored procedures. Once they saw the rather simple changes and how much improvement it made, they were the ones that began adopting the use of stored procedures and now brag about how much they improved code especially the legacy code the previous regime left us. It's kind of ironic that our front end Developers have become better database programmers than most database programmers that I know.
Today, most of the time, the code is consuming about 5-6% CPU and we have 200 times more data and 5 times (500) more connections than we had back then and things usually sing. I'd set up a fairly aggressive "I'll find the problems and together we'll fix them" "Continuous Improvement Prrogram", 100% peer reviews which doubled as mentoring sessions and, once they saw what we were doing, had full management support shortly after we started.
It was a great adventure and still is. It could, however, have been am absolute nightmare if management didn't buy in or the Developers simply gave me the bird. Perhaps me putting on my "Developers Hat" and attitude won the Developers over. Perhaps they were tired of the black eyes they were getting when the system screamed for help. Perhaps I just got bloody lucky. In any case, I think what happened is more rare than not especially with some of the Developers that "know it all". So, no... I don't think your reaction was off base in the least.
That's awesome. Sounds like you were able to step in and make all the right moves and things turned out far better than they otherwise could have.
My follow up question, then, would be: Were you brought on board because management knew they had a problem on their hands and they knew they needed someone of your caliber to get things back on track? or... Did you come on board and then discover that things were rotten in Denmark?
In other words, were they open to change because they already knew they had to change?
The reason I ask, while I was speaking with the group, they didn't give any indication that there were any problems that needed to be addressed. The current DBA is transitioning into a new BI role and they need someone to to fill that slot.
Given that 60% of the questions were centered around backups, I got the impression that the position mostly centered on babysitting backups and making sure the various DBs could be restored if need be....
I tried to dig a little deeper, by mentioning that it had been my experience that, outside of very simple queries, machine generated SQL tended to leave a lot to be desired in terms of plan optimization. The response: if a problem query is identified, kick it back to the application developer and have them improve the LINQ. Stored procs are used only as a weapon of last resort... I didn't get the impression that they had any current performance concerns nor did I get the impression that they were open to altering the current methodology. If anything, I got the impression that they were interested in exploring the newest, latest & greatest technologies and database side stored procs would a step backwards.