ORMs from a DBA's perspective

As a developer ORMs can be a real benefit, they can speed up development time significantly, and they offer a proven, standardised structure to your data access layer, if used well you can even write code that is database agnostic.

But in some ways these are also the problems.

Developers no longer have to worry about what is going on at the database level, and I hear a lot of DBAs complaining about the use of ORMs, how they aren't good for the database and make a DBAs life harder. I feel I have a fairly good understanding of what some of the problems are (query plans etc.) but what I'd really like to know is:

Is there is a way of using an ORM so that I don't make the DBA's life a nightmare?

asked Oct 28 '09 at 07:17 AM in Default

thatismatt ♦♦
210 ● 7 ● 8 ● 9

Many of the answers will probably overlap for content with http://ask.sqlservercentral.com/questions/283/stored-procedures-vs-a-business-logic-layer-in-code

Oct 28 '09 at 07:22 AM Melvyn Harbour 1 ♦♦

(comments are locked)

4 answers:

I have no issues with ORM tools when they are used as defined, Object to Relational Mapping. The issue I have is when the tools are used as Object to Object Mapping inside of a relational engine. That causes no end of trouble. Unfortunately, that's how most developers seem to see the tools.

answered Oct 28 '09 at 11:01 AM

Grant Fritchey ♦♦
64.9k ● 13 ● 20 ● 66

I agree with Grant. In my opinion you use the ORM to create your classes and then use stored procedures in the data layer to populate your classes.

Oct 28 '09 at 12:14 PM Jack Corbett

(comments are locked)

Can't we just teach developers to write SQL :)

answered Oct 28 '09 at 12:20 PM

Steve Jones - Editor ♦♦
5.1k ● 74 ● 78 ● 82

Are you trying to do yourself out of a job?!

Oct 28 '09 at 12:23 PM thatismatt ♦♦

I think most developers already "know" SQL. That's the problem. SELECT * FROM a INNER JOIN b INNER JOIN c ...

Nov 13 '09 at 02:09 PM Tom Staab

(comments are locked)

Matt Lee

Here is an ORM that works with SQL Server https://www.kellermansoftware.com/p-47-net-data-access-layer.aspx

answered Mar 05 at 12:04 PM

asavasamuel
0

(comments are locked)

There are a couple of complications with ORMs:

They normally are less effecient than hand written SQL will produce, you can see this using the SQL Profiler to watch what the ORM code is doing on the database side. I suspect this issue will be reduced/eliminated as more research goes into the optimization side of the ORM, but I have seen it cause issues first hand when performance is an issue.
They make it harder to troubleshoot since they act like a black box. This is especially true when the DBA is trying to assist with the troubleshooting. This is partially alleviated by using an open source ORM like SQL Alchemy but that is still a very large and complicated link in the chain when troubleshooting.
They can make it harder to tightly control security as the ORM is designed with the idea that it can do more than selects and calls to stored procedures. There are of course ways of working around this and still having a secure database, but you cannot realistically say "All non-administrators on the sql machine, to include applications, will only have read access to what they strictly need and ability to call approved stored procedures."

In many cases, they are still well worth using, but the trade offs must be examined realistically first.

answered Oct 28 '09 at 06:14 PM