Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

How should ORM tools perform SQL actions? Expand / Collapse
Author
Message
Posted Tuesday, April 5, 2011 6:37 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 13,872, Visits: 9,596
I've worked with a shop that did a lot in Linq as an ORM tool. It had the advantage of being a good RAD tool, but it meant that I couldn't do the kind of performance tuning that I'm used to, since I couldn't control the actual queries.

In my experience, and that's limited in this case, it's one of those things that doesn't solve what it's meant to. It works beatifully for people who really don't need it, and creates hidden problems for the very people it's meant to help.


- 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
Post #1088557
Posted Wednesday, April 6, 2011 6:20 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 2:44 PM
Points: 4,359, Visits: 6,197
How should ORM tools perform SQL actions?


They shouldn't be allowed near a SQL Server, at least not a production one. There, I said it!

There really are lots of reasons to avoid these for any app that needs (or may need) to scale or have good concurrency.

What about sproc enforcement? Well, at least with LINQ to SQL you introduce some restrictions with what you can do in the sproc, such as use temporary tables. I wouldn't be surprised if others don't have limitations about what types of metadata and operations they can interogate or accept in sprocs.

The businessman in me WANTs everyone in the world to use ORMs for SQL Server development. I get paid good money to clean up the messes they can leave behind.


Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #1089206
Posted Wednesday, April 6, 2011 11:59 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 10:43 AM
Points: 5,383, Visits: 7,455
Well, Wayne asked me to drop in with another viewpoint just to try to get as many as possible.

ORM = Incredibly annoying to inherit. ORM is (personal experience opinion) the shortcut devs take to proper database designing and coding... and I'm all for it for an application that can fit into MS Access instead, or never really needed a DB Dev in the first place and you just needed a datastore and had MS SQL handy. CRUD procs are quick and easy to generate and the ORM has little reason to need to do that. Anything else is usually a major performance fail.

Inherit a project that's been in ORM for a year and you inherit a disaster area. Especially if you're not given time and resources to cleanup the mess. Yes, I tend to consult at a lot of shops that never had a strong jack of all trades on staff.

However, I'll defer to the comments above that it *can* be done right. I don't know ORM very well (and I probably should, there's just so much time in a day). I just know the utter crap I've inherited that they've built and either have to live with or workaround. It's like anything else, where do you want to spend the time? In the beginning, or at the end? Either way, you're going to be spending time designing properly.

Oh, yeah, one other thing I've heard: It's great for 'prototyping'. Have you ever seen a prototype get a complete re-architecture between the prototype and the beginning development stage, or do you always "work with what you've got so far"?

I've never seen ORM work well. At best, it's semi-invisible on apps I never have to care about the optimization on. At worst I've made CIO's cry when I tell them it's time to rip out ORM and start doing it the right way, because they're too entrenched in the model and everything's too tightly coupled. Your webscreen should not dictate my table design.



- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1089479
Posted Wednesday, April 6, 2011 7:24 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 2:44 PM
Points: 4,359, Visits: 6,197
Your webscreen should not dictate my table design.


I LOVE that line!! I think it will be my new tag line when I hit clients that use ORMs.


Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #1089662
Posted Monday, April 11, 2011 1:20 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 10:32 AM
Points: 2,905, Visits: 1,825
In addition to the "right tool in the right hands" comment I should like to add that you should make sure that you are using the right tool for the right reason.

If the tool is being used to get around a break down in the relationship between the DBAs and the development community then things have gone badly wrong and will get worse fast. DBAs and the development community have to work together. Remember, the competition are the companies who compete with your company, not your internal structures.

The competition will love it if all they've got to do is watch you fight amongst yourselves so all they have to do is go in and bayonet the wounded!

nHibernate can use stored procs just as it can use direct table/view access. The whole point of these tools is to separate out the needs of the object layer from the needs of the relational layer. Obviously there is some cost to doing this because nothing is for free but when developed in sympathy with each paradigm a great deal can be achieved.

ORM tools seem to be good at prototyping and greenfield development but are the polish on the poo of a brown field development.
As applications become ever more open it is more vital than ever that the data layer is secure.

This Sunday there was an article in the papers about 19 companies being shut down in the UK due to data protection violations. These weren't bugs, these were violations of the data protection act. Every piece of data needs to be categorised to determine what security access is appropriate and to what roles. Again, getting the ORM tool set up appropriately is extremely important so you don't accidentally expose your data.


LinkedIn Profile
Newbie on www.simple-talk.com
Post #1091698
Posted Tuesday, April 12, 2011 12:43 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, September 2, 2011 3:40 AM
Points: 212, Visits: 222
I'm aware of at least two projects where NHibernate has been used successfully for a while now (2 years or more). Having a deep understanding of NHib internals is crucial, as we've had few problems that was caused by NHib under the cover actions (excessive number of queries, INSERT/DELETEs being triggered when not required, NVARCHAR/VARCHAR strings generating large number of query plans etc).

You get to know the tool after using it for a while, so to don't get into trouble you could try using tools such as NHibernate Profiler (http://nhprof.com/) , or just SQL Server Profiler to track what NHib is actually generating. This could be performed as part of the code review if you've got one in place.

Check Ayende site for problems you may face when working with NHibernate http://nhprof.com/Learn/Alerts.


Cheers, Max

Check-out free open-source utility Sql Server Replication Explorer
Post #1091891
Posted Tuesday, April 12, 2011 8:01 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:26 AM
Points: 5,358, Visits: 8,914
Maksymilian Mulawa (4/12/2011)
I'm aware of at least two projects where NHibernate has been used successfully for a while now (2 years or more). Having a deep understanding of NHib internals is crucial, as we've had few problems that was caused by NHib under the cover actions (excessive number of queries, INSERT/DELETEs being triggered when not required, NVARCHAR/VARCHAR strings generating large number of query plans etc).

You get to know the tool after using it for a while, so to don't get into trouble you could try using tools such as NHibernate Profiler (http://nhprof.com/) , or just SQL Server Profiler to track what NHib is actually generating. This could be performed as part of the code review if you've got one in place.

Check Ayende site for problems you may face when working with NHibernate http://nhprof.com/Learn/Alerts.


Thanks for your post - I was hoping to get a few more positive responses than what has happened, so I'm grateful for it


Wayne
Microsoft Certified Master: SQL Server 2008
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #1092105
Posted Tuesday, April 12, 2011 11:33 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, August 14, 2014 6:59 AM
Points: 247, Visits: 420
It's all about how it's used and understood. For most of the failed ORM based projects I've seen, the idea is that you let the ORM write the SQL and that's the end of it. If your idea is to map a table to an object and be done with it, then ORM is not for you.

When it comes down to it, I think the nature of an ORM guides developers into more procedural patterns and practices, which makes application design easier. Under that nature of design, it doesn't take advantage of set based operations.

Just remember, its not called MapTablesAndViewsToObjectRelationalMapping (MTAVTORM). If I recall correctly, the early days of ORMs were meant to do the equivalent of strongly typed collections.. I could be wrong though.



Post #1092282
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse