September 17, 2010 at 5:27 pm
I am a .NET developer working with a small team of developers who have to also double as our own DBDs. Recently, the subjects of LINQ to SQL and Entity Framework have been raised by one of our developers. I realize this is a SQL Server forum and developers would be using these via .NET, but I want to know how DBAs feel about these technologies hitting their databases. I've very quickly skimmed:
LINQ - http://msdn.microsoft.com/en-us/library/bb308959.aspx
Entity Framework: - http://msdn.microsoft.com/en-us/library/aa697427(VS.80).aspx
At first glance, this goes against everything I was taught. I was taught "NO INLINE SQL!" yet that's what I'm seeing here. And I even saw a foreach that looks like it is possibly masquerading as a cursor (another big no-no). I rarely have a call to the DB that is a simple "select this from that where this". Most DBAs do not want developers hitting the database like this. Am I completely missing the boat here? Is this a huge paradigm shift that we need to embrace? Does using these technologies mean DBAs roles will change to be more operational DBAs? I can't see any of you wanting that to happen, right?
I'm just looking for top of the trees feedback on these concepts. I'm not ready to drink the Kool Aid yet.
Thank you for any input -
Lisa
September 22, 2010 at 5:28 pm
LSAdvantage (9/17/2010)
I am a .NET developer working with a small team of developers who have to also double as our own DBDs. Recently, the subjects of LINQ to SQL and Entity Framework have been raised by one of our developers. I realize this is a SQL Server forum and developers would be using these via .NET, but I want to know how DBAs feel about these technologies hitting their databases. I've very quickly skimmed:LINQ - http://msdn.microsoft.com/en-us/library/bb308959.aspx
Entity Framework: - http://msdn.microsoft.com/en-us/library/aa697427(VS.80).aspx
At first glance, this goes against everything I was taught. I was taught "NO INLINE SQL!" yet that's what I'm seeing here. And I even saw a foreach that looks like it is possibly masquerading as a cursor (another big no-no). I rarely have a call to the DB that is a simple "select this from that where this". Most DBAs do not want developers hitting the database like this. Am I completely missing the boat here? Is this a huge paradigm shift that we need to embrace? Does using these technologies mean DBAs roles will change to be more operational DBAs? I can't see any of you wanting that to happen, right?
I'm just looking for top of the trees feedback on these concepts. I'm not ready to drink the Kool Aid yet.
I started working with databases before the invention of relational databases, even then programmers managed to mask row-by-row loops on the application side - some stuff is not meant to change as I can see. 🙂
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.September 29, 2010 at 10:08 am
I am a .NET developer with a lot of SQL Server 6.5 and onward experience. I have never been called a DBA but I have functioned in that role over the years.
If you search this site for "Entity Framework" you will find that most DBAs are not very excited about EF or ORM in general. Like most things, you can't stick your head in the sand and just be a coder and not worry about what kind of T-SQL the ORM is going to generate. You do need an appreciation for the structure of the database and the performance implications. However, we don't all work in places with guru MSSQL DBAs who know how to wire awesome stored procedures. Sometimes the DBAs are so busy they can barely keep up with the system administration stuff. Sometimes you have server freezes where you can only ALTER a stored procedure 5 times a year. Separating data access from business rules and user interface is a best practice and a new tool in your toolbox won't automatically prevent you from making bad decisions. So embedding the query, be it LINQ or T-SQL or whatever, inside your event handler for a button click is always possible.
ORM seems very promising because it should make code far more reusable and extensible. ORM + a good architecture for a piece of software + profiling & testing to see that the ORM is not doing something stupid on the SQL Server. The safest approach is probably to wait and see what happens with all this. And if you do try to use Entity Framework, do it on a small project, preferably something brand new.
LINQ to SQL is losing favor even among those who "are drunk on Microsoft Kool-Aid" because it doesn't support other back-end databases besides MSSQL... and other reasons you can easily find by Googling. For the moment, the big push is for EF and Microsoft really wants developers to start adopting it.
September 29, 2010 at 5:11 pm
Thank you very much for the reply Andrew. Turns out we had a consult session with Microsoft last week here are some items from my notes:
- EF may not be best for web applications. It can run out of memory doing something big. You don't want to load a lot of data into EF. It's not the best fit for short, "bursty" appications. It is also not best if you have to constantly go to the db for information.
- As far as LINQ goes, they mentioned that if you had contractors, this would keep them away from proprietary data. They indicated customers would use LINQ to talk to Entity Framework. You would also use LINQ if you don't want to go back to the database.
- Windows Communication Foundation was talked about as the preferable approach for our team (I know nothing about this and will be attending a class in November).
- They also talked about Velocity. It's primarily used at the UI level to store large amounts of data efficiently. Velocity is a private cloud and full API that's focused on in memory data.
All these technologies are completely new for me so I can't really speak to anything in more detail. This is just the best I could regurgitate from my notes! Our Dev Manager is recommending a WCF approach and I highly suspect LINQ and EF will not be coming into play with our projects at this point.
Thank you again!
October 29, 2010 at 5:25 am
Like a lot of things with Microsoft "advice", it tends to be contradictory in nature. For example, in just about all of the "n-Tier" articles and samples the main theme is to use StoredProcedures. Yet what do we find in the literature?
....A bunch of T-SQL in the application! lol.
October 29, 2010 at 6:40 am
I get the feeling that Microsoft is large and sprawling enough that it's hard to know what different groups are all up to. I ignored LINQ for the first several years because I am very comfortable with T-SQL and I didn't really need it, I thought. I use it now because it's getting harder to avoid with the frameworks MS is now giving us, especially WCF and that kind of stuff.
I now work somewhere where changing the schema is something that happens quarterly-ish, so sprocs are right out for most things. But I wonder how much unneeded data goes over the wire just that we can append that .Where with LINQ on the middle tier.
October 7, 2011 at 9:36 am
October 7, 2011 at 9:48 pm
In my opinion EF is better than Linq to SQL. Like anything it can be used well or poorly. I prefer to use stored procedures because I believe they make maintaining and tuning the T-SQL much easier. You can use SP's with either EF or Linq to SQL, so you can do it. I think Florian Reischl has a good series on his blog about business layers, data layers, and database access, starting here, http://florianreischl.blogspot.com/2011/09/tables-are-no-domain-objects-part-1.html
SQL Server MVP Mladen Pradjic also has a good post about this here, http://tinyurl.com/DecouplingPrajdic
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
December 19, 2012 at 9:31 am
As an experienced app developer and DBA, I have the following thoughts:
Selecting data within Entity framework using Stored Procs is somewhat of a nightmare for devs. Selecting data through the entity model often generates huge SQL statements which are often made of case statements to determine which part is actually executed on the SQL Server or use a massive amount of unnecessary sub queries. These generate many execution plans on the SQL server, rarely reusing a current execution plan. This is a nasty performance hit on the SQL box. It takes away scope from the DBA to rejiggle SQL code to provide a better execution plan. It leaves the DBA only with option of looking at cached plans on the server and generating any necessary indexes which is only "half a job". It also bloats SQL log files from the somewhat crazy SQL statments that Entity Framework generates to execute against the SQL Box. The product is of complete dismay to me. If you want to have a performant SQL Server and work in an enterprise environment my advise (and I talk from experience) is to stay away from Entity framework!
December 19, 2012 at 9:50 am
In my experience, both of these are usually substitutes for skill in the subject.
That can be a good thing, if the company you work for can't hire expertise (not available, not affordable, whatever), and needs something that will probably at least work, even if not well.
However, it can leave you in a situation where, when you finally can/must obtain expertise (either through hiring an expert or through training existing employees), that the expertise is pointless because of the limitations that are now imposed on things like performance tuning. This is true of any data solution that doesn't include all data access through a DAL the tuning expert (usually a DBA) can code in, whether that DAL is procs or something higher in the stack than those.
LINQ to stored procedures, with no direct table/view/UDF access, on the other hand, I've had more positive experience with. But it seems to be a rare solution. It does require having proc-writing expertise available through one means or another, so may not be available to everyone.
Since database tuning expertise is usually a DBA skillset, and since DBAs tend usually towards writing SQL in stored procedures, which can act as an API to the data (a Data Access Layer), I've found that to be a "best option" in cases where it can be done.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 26, 2018 at 10:02 am
As a DBA EF is a great job creator just because it can't be worst in terms of performance, it generates huge sql sentences that must be compiled and cached using much memory resources.
Sometimes the generated code is so bad for a simple query that it takes tens of times more then human generated sql.
But, my bigger concern is that finaly the application has to know the data model... what about the "Separation of Concerns"? if you build a system using stored procedures and you have a some problems with the physical database design you can change the model, the stored procedure, and many times the application remains without changes.
If you need a performing and scallable system, EF is not an option.
If you need rapid developement, and performance is not an issue, and you're certain it won't grow, then EF is for you.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply