x

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?

more ▼

asked Oct 28, 2009 at 07:17 AM in Default

thatismatt gravatar image

thatismatt ♦♦
210 7 8 9

Oct 28, 2009 at 07:22 AM Melvyn Harbour 1 ♦♦
(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first

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.

more ▼

answered Oct 28, 2009 at 11:01 AM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
100k 19 21 74

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, 2009 at 12:14 PM Jack Corbett
(comments are locked)
10|1200 characters needed characters left

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

more ▼

answered Oct 28, 2009 at 12:20 PM

Steve Jones - Editor gravatar image

Steve Jones - Editor ♦♦
5.1k 77 79 82

Are you trying to do yourself out of a job?!
Oct 28, 2009 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, 2009 at 02:09 PM Tom Staab
(comments are locked)
10|1200 characters needed characters left

There are a couple of complications with ORMs:

  1. 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.

  2. 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.

  3. 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.

more ▼

answered Oct 28, 2009 at 06:14 PM

TimothyAWiseman gravatar image

TimothyAWiseman
15.5k 20 23 32

(comments are locked)
10|1200 characters needed characters left

Matt Lee

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

answered Mar 05, 2013 at 12:04 PM

asavasamuel gravatar image

asavasamuel
0

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x83
x61
x31
x4
x4

asked: Oct 28, 2009 at 07:17 AM

Seen: 2199 times

Last Updated: Mar 05, 2013 at 12:04 PM