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


nHibernate


nHibernate

Author
Message
Samuel Clough
Samuel Clough
Valued Member
Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)

Group: General Forum Members
Points: 55 Visits: 108
If they really want to toss relational design, tell them they need an object database.
Then tell them when they find one that can perform as quickly as Oracle/SQL Server, etc you'll be happy to consider supporting it.Wink
JoeA
JoeA
SSC Journeyman
SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)

Group: General Forum Members
Points: 90 Visits: 869
Hi Grant - I just came across this discussion and was wondering how your project is going.

I'm on a team that is reengineering a very large application (was overgrown 2tier RAD tool based and relational, lot's of very complex procedures, lacking documentation, etc). We will be redesigning the DB and application from the ground up and using NHibernate at the core of the DAL. I share some of your concerns. But I'm not too worried.

I'm the data architect and have worked with the app architects before, elsewhere. On our former projects we've always enjoyed a nice balance of respect between the DB design and the OO needs for the application. Our database design is a first class citizen, as is the object model. For us, data is the most valuable asset and it needs to have integrity and quality. It will be used by downstream systems like enterprise reporting and out external customers.

We work together as a team on a continuous basis; no over the wall or ivory tower mentality from either side. That makes all the difference in the world.

So, what have you learned from your ORM project so far?
Grant Fritchey
Grant Fritchey
SSC-Forever
SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)

Group: General Forum Members
Points: 41865 Visits: 32666
JoeA (1/2/2009)
Hi Grant - I just came across this discussion and was wondering how your project is going.

I'm on a team that is reengineering a very large application (was overgrown 2tier RAD tool based and relational, lot's of very complex procedures, lacking documentation, etc). We will be redesigning the DB and application from the ground up and using NHibernate at the core of the DAL. I share some of your concerns. But I'm not too worried.

I'm the data architect and have worked with the app architects before, elsewhere. On our former projects we've always enjoyed a nice balance of respect between the DB design and the OO needs for the application. Our database design is a first class citizen, as is the object model. For us, data is the most valuable asset and it needs to have integrity and quality. It will be used by downstream systems like enterprise reporting and out external customers.

We work together as a team on a continuous basis; no over the wall or ivory tower mentality from either side. That makes all the difference in the world.

So, what have you learned from your ORM project so far?


Hello,

Sounds like heaven. Where do I apply?

So far... They started coding in November, but have yet to do anything that touches a database. We haven't seen any of the generated code or structures yet. So far, I don't have anything to complain about, but then, so far, I haven't seen anything.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
JoeA
JoeA
SSC Journeyman
SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)

Group: General Forum Members
Points: 90 Visits: 869
We're in Minnesota, so this time of year the term "heaven" is debatable.

Sounds like you've still got a bit of "us vs. them" going on... they might be chucking a database design over the wall at you.

For what it's worth, we're generating our persistent object model from the database design (with plenty of app team input) which keeps things in pretty good alignment and helps to ensure that we can migrate the existing data from the legacy system.

Good luck!
Grant Fritchey
Grant Fritchey
SSC-Forever
SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)

Group: General Forum Members
Points: 41865 Visits: 32666
It sounds like you're taking the approach I would have expected based on all my readings on this topic. Logical and straight forward, playing against the strengths of each technology. Makes sense. Unfortunately, a sub-set of the industry is taking the approach our dev team is taking.

Yes, I'm standing on the other side of a wall with a net and I'm hoping to catch the database as it flies over.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
David.Poole
David.Poole
SSCertifiable
SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)

Group: General Forum Members
Points: 7969 Visits: 3291
I've currently got a big push from developers to allow nHibernate into the databases.

My concerns are security implicit in direct table access and performance.

The "Oh everyone uses it" argument doesn't cut much ice with me. I don't care if 50 blue chip companies use it. I'm more interested in the guy who is using it properly so I can gain from his/her experience.

Because I'm not a hacker I'm not sure how safe a database supporting a public facing website is when direct table access is involved. I worry about sitting there thinking I'm safe when what I am sitting behind is the Maginot Line.

From the performance point of view what happens to the network traffic if great swathes of SQL start being submitted to the database? If someone calls getCustomerDetails vs 3K of SQL surely there has to be a difference?

LinkedIn Profile

Newbie on www.simple-talk.com
Daniel Auger
Daniel Auger
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 97
I can see the argument against table level access whether the danger is real or perceived (I think it varies from shop-to-shop). That being the case, it may be helpful to know that NHibernate will allow you to use stored procs. Using stored procs for selects effectively negates much of NHibernate's flexibility. However, the best of all worlds may be achieved by going this route:

  • Use NHibernate generated queries to do selects off of views. This gives the dba an abstraction that allows for table reorganization, but allows the devs to write a fetch strategy that is ideal for each context via HQL, ICriteria, Linq2Nhibernate, or native SQL. Having NHibernate generate the queries will cut down on a lot of multiple round trip/ n + 1 select scenarios that occur when using one size fits all read procs.

  • Use procs for CUD. This negates the need for table level access.


  • The only major item left now is the performance of the select queries. It is imperative to use a profiling tool such as NHProf, or at least a SQL profiler. As with SQL, NHibernate queries have to be composed properly or else you'll get nightmarish queries (n + 1 is always something to look out for).

    This isn't my idea, but rather something one of the NHibernate devs suggests. I have not attempted this implementation, so I'm not sure what other downsides it has for the application developers. I'd have to guess that some of the popular usage patterns may not work. However, even if that is the case, I'm sure most developers would rather make this concession over writing a painful, bug filled, inconsistent, home grown mapping layer. I hope to give this implementation a try on a project in the near future.
    Grant Fritchey
    Grant Fritchey
    SSC-Forever
    SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)

    Group: General Forum Members
    Points: 41865 Visits: 32666
    Daniel Auger (9/4/2009)
    I can see the argument against table level access whether the danger is real or perceived (I think it varies from shop-to-shop). That being the case, it may be helpful to know that NHibernate will allow you to use stored procs. Using stored procs for selects effectively negates much of NHibernate's flexibility. However, the best of all worlds may be achieved by going this route:

  • Use NHibernate generated queries to do selects off of views. This gives the dba an abstraction that allows for table reorganization, but allows the devs to write a fetch strategy that is ideal for each context via HQL, ICriteria, Linq2Nhibernate, or native SQL. Having NHibernate generate the queries will cut down on a lot of multiple round trip/ n + 1 select scenarios that occur when using one size fits all read procs.

  • Use procs for CUD. This negates the need for table level access.


  • The only major item left now is the performance of the select queries. It is imperative to use a profiling tool such as NHProf, or at least a SQL profiler. As with SQL, NHibernate queries have to be composed properly or else you'll get nightmarish queries (n + 1 is always something to look out for).

    This isn't my idea, but rather something one of the NHibernate devs suggests. I have not attempted this implementation, so I'm not sure what other downsides it has for the application developers. I'd have to guess that some of the popular usage patterns may not work. However, even if that is the case, I'm sure most developers would rather make this concession over writing a painful, bug filled, inconsistent, home grown mapping layer. I hope to give this implementation a try on a project in the near future.


    What's this N+1 that creates "nightmarish" queries? I'd like to know because I'm going to be monitoring an nHibernate only solution shortly and any information about possible problems will make my life a little easier.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
    Product Evangelist for Red Gate Software
    Daniel Auger
    Daniel Auger
    Grasshopper
    Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

    Group: General Forum Members
    Points: 13 Visits: 97
    Here is a good description of N + 1:
    http://nhprof.com/Learn/Alert?name=SelectNPlusOne

    EDIT: I will add that Select N + 1 is not a problem that directly stems from using an ORM framework. It stems from lazy loading (usually), so most hand rolled DALs are prone to this issue as well.
    Grant Fritchey
    Grant Fritchey
    SSC-Forever
    SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)

    Group: General Forum Members
    Points: 41865 Visits: 32666
    Daniel Auger (9/4/2009)
    Here is a good description of N + 1:
    http://nhprof.com/Learn/Alert?name=SelectNPlusOne

    EDIT: I will add that Select N + 1 is not a problem that directly stems from using an ORM framework. It stems from lazy loading (usually), so most hand rolled DALs are prone to this issue as well.


    Excellent. Thank you very much.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
    Product Evangelist for Red Gate Software
    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