February 11, 2009 at 9:13 pm
Hi,
I need to know the difference between LinQ and NHibernate in the following characterstics:
a. Speed for insert/update/select/delete
b. Database support (Like MySQL, Oracle, etc.,) (I know that:SQL joins hand with both)
c. Performance with heavy datas.
d. Others
Thanks
"I Love Walking In The Rain So No One Can See Me Crying ! " ~ Charlie Chaplin
February 12, 2009 at 6:11 am
I don't have all the answers to your question, but I can advise you to look in a slightly different direction. LINQ is being swallowed up the Entity Framework. That's what you should be looking into and comparing to nHibernate.
I have a little experience with both. The one main difference between them is that EntityFramework is a bit more database focused and nHibernate is a bit more object focused. That made a difference for our local development team.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 13, 2009 at 3:54 am
Thanks. But could you be more precise?
"I Love Walking In The Rain So No One Can See Me Crying ! " ~ Charlie Chaplin
February 13, 2009 at 5:47 am
Entity Framework assumes a data model in the database and works with it to map between the data model and your object model. nHibernate can work that way, but our developers liked the fact that nHibernate can simply override the data model and write the object model straight down to the database. I have some serious reservations as to whether this is going to work well over time, and we're still too early in development to do serious testing with the code, so we'll see.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 13, 2009 at 7:07 am
I hate hibernate, well, if cursor mode is used. But we tried direct and started getting some locking issues (developers didn't code for it), so went back to cursor mode where it does wrap everything into transactions. We also had extreme Jave memory pressure with direct (I think they load all three LDAP databaes of the user info into memory resulting into 2 gigs of Jave memory used). Cursors are slow as molasses with it. Countless times we've tried to find where the performance problems were. All too often I found over 500,000 requests were sent to the server for some simple queries and updates. Many of them were "If @@trancount > 0 commit tran" and this was after a select had been run. Go figure. I also get sort warnings in the form of low memory.
Never heard of Linq.
February 13, 2009 at 7:47 am
Again, I'm just getting started supporting it. However, I found some fun stuff in nHibernate that is going to seriously impact our performance over time. For example, check out my blog post[/url] on how it handles variables when passed to parameterized queries.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 21, 2009 at 1:19 pm
LINQ which is math in .NET objects still exists what is being replaced by Entity Framework is LINQ to SQL which is a mistake because what is the issue is not LINQ to SQL but rather the SQL metal that converts a whole database to classes and properties. The reason LINQ did not implement needed algebraic functions so the mapping to SQL Server needed room to use existing SQL solutions as needed something not feasible with SQL metal.
LINQ to SQL cannot be compared to Nhibernate because it claims to use one code base for all RDBMS which means a lot of the relational model of an application will be generic and not optimized for each RDBMS. One example is OUTER JOIN it is not practical to use the same code for even all versions of SQL Server from 7.0 so to use the same code for all RDBMS is not good implementation. These reasonings comes from people who thinks RDBMS is storage, may be correct in Winform application but will kill your web application.
Kind regards,
Gift Peddie
February 21, 2009 at 2:40 pm
LINQ is good for querying objects cached in memory. I don't know that I would use it for something other than that.
nHibernate isn't really good for much in my opinion. You are better off designing your own ORM. nHibernate has little support for stored procedures. It loads a lot more data in memory than is necessary. There were also a lot of deadlocking issues encountered with nHibernate at a company where I had worked as well. There is a big difference between a VLDB and persistent storage. nHibernate only works well in the latter scenario.
Karen Gayda
MCP, MCSD, MCDBA
gaydaware.com
November 7, 2009 at 3:45 am
?
November 7, 2009 at 11:45 am
Hi
As previously stated, it's hard to compare LINQ2SQL and NHibernate but give me a try 🙂
From client development side
L2S is a active-record business layer design - what means a 1:1 table to objects and columns to properties mapping. This is nice for small projects and gives the possibility to quickly start to implement the customer requirements. There is no real support for other DBMS than SQL Server. By searching the web you might find some implementations for MySQL or PostgreSQL, but no supported and maintained version.
NHiberhate (NH) supports a domain object business layer design - what means it can abstract the database structure from the client object model. The general gain of this abstraction is, you can optimize both sides without touching the other one. The pain of NH is to create the mapping XML files. Also the client side performance is way slower than L2S (or EF). NH supports all common databases.
As brought up by Grant. Don't forget Entity Framework. It is the new O/R-Mapper provided by Microsoft since Framework 3.5 (SP1). The first version (as every first version - in my opinion 😀 ) wasn't really done - as MS stated on PDC (2009?). One problem was the missing persistence ignorance, what means all objects need to derive from a EF base object. EF v2, shipped with Visual Studio 2010, fixed this problem and it becomes more like NH. EF v1 supports many common DBMSs, I don't know if there are new DBMS supported in v2.
From database side
L2S is very optimized to SQL Server. AFAK the generated statements are way better than EF and NH.
NH SQL can be fine if you completely define the mapping XML, containing all column data types and data length.
EF SQL was not the best in v1. For v2 MS added some of the L2S guys to the EF team to optimize the statements. As they promise on their blog (Entity Framework Design) and on PDC it will be better in v2.
My personal suggestion
If you can choose, use EF - even in v1. If persistence ignorance is not a must have for your project start you should be on the right direction. Why? Nobody knows what will happen to L2S and many people call it dead, since MS currently has two different O/R-Mapper which are way too close to live side by side. NHibernate is currently state of the art with the most features but Microsoft just started with EF and they really push it. They even intend to add EF support to the next version of SSRS. I believe v2 will be really nice and v3 will become a feature monster like SS2k8 DBMS.
Greets
Flo
November 7, 2009 at 8:20 pm
Heh... I can't speak of Linq because I've not had to clean up any messes from it, yet, but Hibernate and NHibernate are both 4 letter words to me. Between the "aggressive GET" mentality of some developers and the single query methods of those two products, I've spent a good amount of time cleaning up huge performance problems generated by developers who know nothing of databases using a tool that makes it possible for them to think they do. 😉
On the flip side, I'll also say that I've seen both properly used by people who do know something of databases and both have worked out well in the correct hands. If Linq is more "performance aware" than either Hibernate or NHibernate, then I'll take my hat off to it especially if it does "Divide'n'Conquer" code on "aggressive GETS" instead of the performance nightmares that the other two products render.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 12, 2009 at 1:30 pm
Between the "aggressive GET" mentality of some developers and the single query methods of those two products, I've spent a good amount of time cleaning up huge performance problems generated by developers...
This is an interesting area. NHibernate and other ORMs can denormalize several queries into one, and/or perform several queries in one round trip to the DB. Balance needs to be found between too many queries vs too many joins / results. IMHO the way to ensure success with any of these tools is to have the DBAs and developers monitor and discuss the queries during the development phase. A few steps to fail at ORM are: let the tool design the final database, don't profile the queries, and then dump it on the production DBA. NHibernate's schema export is great for getting going very quickly, but eventually someone has to turn that into a real design. Also, as Florian Reischl said, explicitly setting a lot of the table / column values in the mapping files is the way to go.
If anyone is working in an NHibernate environment, I highly recommend NHProf. NHProf profiles everything from the app's perspective, so you get metrics like total number of queries per unit of work, full round trip time of each query, formatted sql statements, usage alerts and suggestions etc... SQL profiler is still useful, but you get a much better view of the big picture using both. Also, the NHProf team currently has both an Entity Framework profiler, and a Linq2Sql profiler in beta.
November 13, 2009 at 7:19 am
Please note my email address for future reference if you intend to have any scale to systems you develop with ANY ORM. You WILL be needing my performance tuning services. 😎
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
November 13, 2009 at 10:00 am
Hi
TheSQLGuru (11/13/2009)
... if you intend to have any scale to systems you develop with ANY ORM. You WILL be needing ... performance tuning services. 😎
Sorry, but I slightly disagree with this. O/R-Mapper are useful in my opinion. O/R-Mapper are great tools and I'd almost always suggest to use them. It's just the way how to use them.
They perform fine for more than 90% of required queries in the domain logic. Those 90% cover all queries like "select an order by its order-id", "select order-details for a given order-id" or "select open orders for a specific customer".
They are just not made to provide all SQL features like complex queries, running totals, bulk operations, reporting, analysis and so forth.
All common O/R-Mapper (like (N)Hibernate, EF, L2S) are able to be injected with custom procedures to extend existing feature set when existing ORM-features hit the wall.
In my opinion, it's always the way in the middle. Not to use any O/R-Mapper means to write a large bunch of very simple queries what might be quiet expensive without a real benefit. Using O/R-Mapper and reduce SQL features to the ORM-supported features means to keep the major features of each specific DBMS out of the party.
😎
Flo
November 13, 2009 at 10:54 am
There is plenty of proof in the wild that ORMs can scale. Take stackoverflow.com for example. They handle over 1 million requests a day and they use Linq2Sql. ORM is just a tool. It can be used in a performant way or in a non-performant way just as say, stored procedures. I think the scaling argument is settled for many of the more matured ORM frameworks. On the flip side, many hand written DALs don't scale because their fetching is often too static.
The way I look at it is this: If your application is OO, you are going to have an ORM one way or another because the O/R divide is a real technical issue that has to be solved in any OO application that uses a relational database. This being the case, the team has two options: 1) the developers can write their own ORM layer (non contextual fetch procs, mapping layer, identity map, dirty tracking, caching layer, yadda yadda yadda). 2) They can use an off the shelf ORM. Hand writing an ORM is extremely difficult. What usually happens in the roll-your-own scenario is you end up with a very crappy bare minimum ORM DAL at the expense of performance, flexibility, and uncounted development maintenance hours. I've never once seen a good hand written ORM/DAL.
ORM is not an all or nothing tool. Most of them allow you to decide on a per entity (and even per scenario) basis if SQL should be generated or if a sproc should be called. Additionally most ORMs' query languages have ways to influence the generated sql so that it isn't doing something stupid. I think it's a huge mistake if DBAs aren't involved with monitoring / tweaking the ORM output and making suggestions during the development phase. IMHO, it's as important as having the DBAs review developer written procs. DBAs are vital to the success of data access, ORM or not.
Additionally, obviously an ORM should only be used for the OO aspects of an application. Mass data manipulation, super complex queries, ETL, background process, etc... should still be done via procs, SSIS, etc...
Of course, the other alternative is to go the route of datasets in the business layer. But that's been proven to be unmaintainable on anything larger than a trivial application.
I just don't see ORM as a black and white issue. I see it as using the best tool for the job in many cases.
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply