question

thatismatt avatar image
thatismatt asked

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?

dbabest-practicedeveloperpros-and-consorm
1 comment
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Melvyn Harbour 1 avatar image Melvyn Harbour 1 commented ·
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
0 Likes 0 ·

4 Answers

·
Grant Fritchey avatar image
Grant Fritchey answered

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.

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Steve Jones - Editor avatar image
Steve Jones - Editor answered

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

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

TimothyAWiseman avatar image
TimothyAWiseman answered

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.

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

asavasamuel avatar image
asavasamuel answered
Matt Lee Here is an ORM that works with SQL Server https://www.kellermansoftware.com/p-47-net-data-access-layer.aspx
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.