SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How should ORM tools perform SQL actions?


How should ORM tools perform SQL actions?

Author
Message
GSquared
GSquared
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24653 Visits: 9730
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
TheSQLGuru
TheSQLGuru
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13121 Visits: 8566
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! :-D

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

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Evil Kraig F
Evil Kraig F
SSCrazy Eights
SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)

Group: General Forum Members
Points: 8943 Visits: 7660
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
TheSQLGuru
TheSQLGuru
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13121 Visits: 8566
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. w00t

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
David.Poole
David.Poole
SSCrazy Eights
SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)

Group: General Forum Members
Points: 8039 Visits: 3291
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
Maksymilian Mulawa
Maksymilian Mulawa
SSC Veteran
SSC Veteran (274 reputation)SSC Veteran (274 reputation)SSC Veteran (274 reputation)SSC Veteran (274 reputation)SSC Veteran (274 reputation)SSC Veteran (274 reputation)SSC Veteran (274 reputation)SSC Veteran (274 reputation)

Group: General Forum Members
Points: 274 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
WayneS
WayneS
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10254 Visits: 10575
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
Author - SQL Server T-SQL Recipes
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

Obs
Obs
SSC Veteran
SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)

Group: General Forum Members
Points: 275 Visits: 424
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.



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search