July 2, 2009 at 3:02 pm
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?
September 4, 2009 at 9:29 am
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:
The only major item left now is the performance of the select queries. It is imperative to use a profiling tool such as NHProf[/url], 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.
September 4, 2009 at 9:40 am
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[/url], 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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
September 4, 2009 at 10:05 am
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.
September 4, 2009 at 10:32 am
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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply