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

avatar image

thatismatt ♦♦
210 8 12 12

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

avatar image

Grant Fritchey ♦♦
137k 20 46 81

(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

avatar image

Steve Jones - Editor ♦♦
5.2k 79 94 87

(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

avatar image

15.6k 22 55 38

(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

avatar image


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

Follow this question

By Email:

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



Answers and Comments

SQL Server Central

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



asked: Oct 28, 2009 at 07:17 AM

Seen: 2815 times

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

Copyright 2018 Redgate Software. Privacy Policy