I love Entity Framework. I also like (not love) nHibernate. That’s right, as a DBA and data professional, I’m telling you I love Object/Relational Mapping tools (ORM). I think this is a technology set that the DBA needs to more tightly embrace. Let me tell you why.
Most of the Queries
I know that the biggest pushback against Entity Framework (EF) and it’s fellow ORM tools is that they generate crap code. I know this to be true. I’ve seen it. ORM tools can, and do, generate seriously poor T-SQL. That’s not to mention the N+1 problem and a few others. However, as you see from the article in that link, these problems and how to avoid them are very well defined. You don’t have to suffer from the issues.
The majority of all queries out of ORM tools work perfectly fine. They are parameterized. They can use the indexes on the tables. They’ll see plan reuse and everything will be fine with them. We’re talking at least 90% of the queries in your system that no one has to sit and write. This is a gigantic win for development time, but it’s also a giant win for DBA types. You have a highly consistent, well-performing set of queries that are going to work flawlessly. What’s not to like?
The other 10% of queries I hear you saying. Well, it may be 10% or it may be a lot less than that. I’ve seen it on successful projects where it was down to maybe 2% or so of all the queries on the system that just didn’t generate correctly out of the ORM tool. What then? Back to the Future!
Entity Framework Does Stored Procedures Too
Yeah, we’re using EF because we don’t want to write T-SQL code and the way it works means you don’t get stored procedures either. However, that doesn’t mean that it doesn’t support stored procedures. It does.
This completely opens up the opportunities for EF. Let’s assume you have good developers who are aware of, and are actively avoiding, issues like the N+1 problem and others. Great. They can use their code through EF to fix most issues.
Then, you hit that 2% (10%) query that just wont’ generate correctly through EF. What do you do? You write a stored procedure, just like the old days. EF consumes the procedure and you’re good to go.
Hitting a performance problem or a consistency error with EF? Not an issue. Write a stored procedure where it’s needed and implement that.
Done. Fixed it.
Now, the one and only point I would raise here is that the developers have to be cognizant of the fact that in some places they will have to use stored procedures, but this will be the exception. However, we established already that these are skilled developers. They’re going to easily recognize the need for stored procedures and will readily implement them in the appropriate places.
What Should a DBA Do about Entity Framework?
Learn it. Don’t fight it. Learn about the potential problems, yes. Absolutely. However, learn how it works so that you see and understand the benefits it offers. Brush off your dusty old coding skills (or learn some new ones) and try out EF. If you need help, there’s an excellent pre-con available at PASS Summit this year on Entity Framework. I’d strongly suggest you learn everything you can about it in order to better support it. The fact is, EF or some other ORM tool will be implemented by your dev teams. Best to come at these things from a position of knowledge. Not only will this make you more useful to your organization, but your developers will work more closely with you. This all comes back around to approaching things with a DevOps mind-set.
Now that you have the majority of your code being generated, you can focus on the fun stuff, query tuning. I’ve got several opportunities to help you out coming up. First, at SQLSaturday Louisville on August 4, I’ll be presenting a new, all day class called “Using SQL Server Tools for Query Tuning.” Please register right now because space is very limited and the seats are almost all sold.
In the fall I’ll be at DevConnections in October in San Francisco doing the class, Using SQL Server 2016 Tools for Query Tuning.
I’d agree for the most part. We’re still using mainly a home-grown ORM. Some of the problems have more to do with skewed data and the bad practice of running data warehouse type queries and OLTP in the same database.
Having a large table ( actually quite a few ) where a large client may have 15 million rows and hundreds of other smaller clients may only have 100k rows leads to a lot of “inappropriate” plan re-use. Adding option(recompile) works in some cases but the developers aren’t used to that and finding where/how to do that in the code is a challenge and therefore slow to implement.
It’s been a while now but the last time I looked into data compression, the number of joins generated by the ORM was part of the reason the analysis tools didn’t recommend compression.
Now that DevOps has become such a big thing ( our manager even suggested the Project Phoenix book to our team ), what may be coming next is not a challenge for DBAs to learn ORMs but do learn about databases in the cloud ( in our case everybody seems to be all jazzed about Amazon Web Services ). The in-house DBA and in-house systems engineer may have a smaller role in that world ( but I’m retiring so 🙂 )
It is tricky to update queries through some of the ORMs, especially home-grown ones. However, from an automation stand point when you identify a negative pattern and a solution to it, the automation makes resolving everything very easy.
The hardest thing about EF and ORMs in general is troubleshooting. There’s a slow query somewhere in the code, but nobody knows where it comes from, because nobody wrote it. For instances, it could be passing a varchar value as unicode, making index use impossible. Good luck finding the exact point in code where the query comes from.
My take on EF and ORMs is: good for ER/OO translation, good for trivial queries (SELECT columns FROM table WHERE pk = ?), horrible for everything else. I can use stored procedures? Cool. BTW, a tool that saves me from writing trivial queries is not of great help. I highly prefer micro ORMs like Dapper, that will let me write my SQL code and take care of the object-relational translation only.
It can be tough. No argument. That’s why I’ve been using the Redgate tool to walk code on stuff like this. You can usually narrow down where the problem is just based on behavior. Then our tool finds the precise spot.
I’m cool with smaller ones too. I don’t have as much experience, but the concepts are the same. Automate the stuff we should be automating and let’s spend time only where we should spend time.
Replying to “I highly prefer micro ORMs like Dapper, that will let me write my SQL” … I can’t tell from your comment whether you’re from a developer background or DBA but I know from my own experience working with dev teams that most devs will do their best to not have to write SQL code … they have enough other languages to worry about and if they can use an ORM and skip talking directly to the database they’d be very happy.
As DBAs we need to learn to accept the tools that devs use or at least understand them enough to give them proper guidance when needed.
That’s the main point behind devops – to have ops and devs work together. The final goal is to work together and deliver a product that customers like to use … not to show that DBAs can write better SQL than the tool that the devs use.
I can’t agree with you on this. Looks like DBAs always have to accept whatever devs decide to use, because devops. Throughout the years, devs have invented the most fanciful excuses to avoid learning SQL, but at the same time they will happily spend countless days learning the next JavaScript framework (that will last a fortnight, at best).
It’s not about the grumpy DBA. It’s about the devs that refuse to learn one of the tools they have to master, because it’s not cool enough.
The tedious part of object/relational is translating objects into rows in DB and vice versa. Micro ORMs do that just fine. Queries? Let me write them. It takes a few seconds and every decent dev, not a DBA, can do it much better than any ORM.
BTW, I’m both a dev AND a DBA who had to fix enough ORM queries in his careerer.
I’m not arguing that we have to do what devs want regardless. I’m saying straight up that ORM tools make sense, when used appropriately (and that is the hitch). As a DBA, I like them. However, I’ve also taken the time to learn them. A lot of DBAs just don’t. They should.
> A lot of DBAs just don’t. They should.
Completely agree.
I think this is a good piece. Many thanks, as a BI professional, front end developer, c# dev coder I have to bring this back to the principles of what entity framework tries to do. Ef tries to abstract away the database translating code queries into sql queries, and for simple access where relations between tables isn’t so necessary or access is infrequent Ef is great. The minute you want an enterprise scale application such as a customer facing website, forget it. The first issue with ef is it encourages normalisation to 3nf which dbas may or may not think is great but then we end up with continued joining just to display 7 or 8 columns. The next is working with schemas and we quickly find coders who are obsessed with patterns in their domain happy to shove everything in dbo and as you say, when they need to write sql we see the worst code known to man. my main fear with entity framework is can it reliably support high concurrent access without seeing locking issues and the answer is no. sql server continues to have this problem when comparing it to oracle and it calls for a higher degree of specialisation in design to introduce redundancy which in turn can be solved by event sourcing and other approaches designed to reduce contention. Whereas with dapper you can find it comfortable to build an intelligent anaemic repository entity framework brings half of china along with it and if you were to want to we would be destroying the very thing ef was designed to do and starting to think about sharing interfaces between caching repositories and data repositories would never fly. Then consider set based and identity values, Microsoft’s output clause is a gem that few developers use and again dapper works with the database rather than inspire of it. I view ef as a prototyping tool causing more headaches than it solves which better developers avoid through using dapper or other tools. Finally, we have to make a conscious design intention upfront. Do we want a flexible open access database where any queries can be thrown at it even from the client front end odata or do we want known methods restricting control through the more standard rest methods? Perhaps if machine learning/nlp is to be fully realised possibly? Having said this, entity framework is still an awesome technology which if used in the right place and with an understanding of databases with articles like this can be a useful tool.
There are issues with it. There are issues with pretty much anything. However, I think your wrap up nails it. If implemented properly and used appropriately, this is a useful tool.
I’ll tell you what I should do about EF is finish that PS scriptlet I started that takes big fat EF queries and reformats them less verbosely, removing unnecessary aliases and brackets and pretty-printing all the unions and subqueries. Just so I can tell wtf EF has got up to this time.
I’ve been raising the consciousness of the dev team about some of the, er, limitations, of EF. They had no idea they were sending 500kb queries. They didn’t realize that EF is “chatty” by nature and encourages coding that does things one row at a time, which doesn’t scale. And (I am still learning that) EF has some obscure little statements that scale poorly when you put SQL Server under heavy load.
Oh, and just EF’s ORM-ish need to objectify everything it reads in can be app-limiting, does not scale well above a certain point, decays exponentially.
Love it if you like, I’m more into trust but verify. Which I know is what you meant, it’s like job security for a DBA.
I’m trust but verify on this too. My big push would be for DBAs and other data pro’s to take a class like the one I link to above to learn how this thing should be appropriately implemented. That’s the key here.
Some of the comments are reminding me of our current effort to start using Amazon Web Services. Right off the bat I’m being asked if our 7 TB database can get buy on a lot less memory than the 500GB we have now ( because anything at AWS over 250GB is very expensive ). So, like EF, is AWS better for small to medium sized databases? My response was ” can we test at lower memory and put a realistic load on the system, and would it be 100% solid state drives on AWS, like we have in-house now?”
The frosty, hurtful breath of ” big production realities ” has to be blown by management and development on a regular basis.
No argument from me on this.
Hmm, for once I think I find myself more jaded than you. I have a love/hate relationship with EF. I think it’s a powerful tool that is all to often misused. In most cases, it’s like putting a chainsaw in the hands of a preteen. It’s far too easy to misuse and cut off a limb or two.
That said, I do agree, used properly and in the right hands, one can make art of a stump with a chainsaw. And I do agree that as a DBA it’s partly my responsibility to make sure programmers use it properly. (that said I’m pretty much always a fan of stored procs, so simply calling them from EF can work quite well for me.)
It seems to me, from talking to too many developers and too many DBAs, often there’s a very artificial divide that needs to be overcome. This is a good area to work on removing that divide.
Excellent feedback. Thanks.
I might add, so does (Fluent) NHibernate (support stored procedures). And a bevy of other mechanism, stored queries, etc. NHibernate supports canning queries for internal use as well. Never mind its Hql support. Forgive me if my terminology is a bit off.
Yep. NHibernate can support stored procedures too.
I like stored procedures in terms of security standpoint. With EF, we need to give permissions at table level?
Good point. Security is different with EF if you only provide access through EF. However, EF can work with stored procedures too. If security demands it, using EF doesn’t preclude using stored procedures. However, be 100% sure that your security has to be defined that way and we’re not just going with the “we’ve always done it that way” approach.
Especially when talking about security, but in every other regard as well, DBAs have to work with the developers and developers have to work with the DBAs. This is one of the big reasons why I’m pushing so hard on approaching our jobs using DevOps as a communications framework. We’re a team, not enemy camps, or at least that’s how it ought to be.
This is not to say EF or other ORMs should be discarded, but a real-world example of the problems that can arise. Application developed by Agile team with no DBA – input, using home-grown ORM. Ten years later, the integer identity PK columns are nearing the maximum value for an integer on several large tables.
DBA Question: can we re-seed to negative 2.1 billion? Answer: we really don’t know what would happen due to sorting on PK ( why an application would not only sort but display ID values with no customer meaning is beyond me )
DBA Question: How about adding a new big integer column in the existing table and populating it from the integer column? Answer: since this would leave the new column at the end of the column list, we’re not sure what the impact might be. Can you move the new column back to where it “should be?” Answer: not without rebuilding a huge table and probably filling up the log.
I suppose this difficulty in finding out what is buried in the ORM code and how it might react to certain things could also be found in application code not using an ORM. I was surprised to find that the code cared whether a column was integer or big integer.
“Application developed … with no DBA input…”
I’m sorry, did you say something else?
Kidding, but herein lies the problem. It’s not EF, or any other ORM, that creates problems, inherently. It’s the lack of communication and input from specialists who can help us avoid common problems.
As I said in the blog post and in many of the comments here, proper implementation of EF (or any ORM) is a good thing. The trick is to properly implement it, not simply implement it. It’s like any other tool. Used well, it does what it’s meant to do. Used poorly, it hurts people.
Devops is hard. I’m seeing that more every day as interaction between DEV and OPS ( DBAs/product engineering) is increased. We recommend a way to deal with this integer/big integer issue and is seems there is no respect coming from DEV at all. We can all ( developers and DBAs ) get big heads and have type A personalities, questioning everything we didn’t dream up.
Maybe it’s just that I’m near retirement and don’t feel the obligation to explain myself over and over. Agile, in the worst case, wants no DBA and views your systems/network engineers as their personal butlers who at best are another roadblock. Maybe this is why so many have stars in their eyes about putting everything in the cloud.
No arguments from me on any of this. DevOps is hard. Communicating with human beings is hard. Unfortunately, it has to happen, soon or later, one way or another.