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.
The post I Love Entity Framework appeared first on Grant Fritchey.
