AW: NHibernate as ORM

Well Jeff, NHibernate is an Object Relational Mapping (ORM) blah blah blah abstracting the DB relational model to familiar .NET POCO’s. The chaps that regularly develop code with ORM technologies, and I count myself as one of them, will often bandy around terms such as impedance mismatch, testability, database agnostic, idiomatic .NET RDBMS persistence, fluent ... and many more bullshit bingo words in their design documentation. You can pull further information from Wikipedia or the NHibernate project page using these and other keywords mentioned below.

For the record, you can use ORM on top of DB packages/stored procs so you can (sometimes) retain a feeling of confidence in the technology; ie. finely tuned targeted DB code that resides in the DB might, depending on the approach used by the developers advocating NHibernate, still exist. The application persistence layer that directly references NHibernate namespace libraries can push out ‚Table' joins between the POCO/entities to the database using the .NET IQueryable interface. Grunt job queries/projections and joins can be performed within the persistence layer (sometimes called the application database layer) too using instead the IEnumerable interface (so query joins performed in code in the middleware, not pushed out to the DB). The NHibernate mapped POCO’s can reference views too, so efficient joins or references to packages/procs can be implemented there as well. Also NHibernate can execute native SQL and PL/SQL using a native query (so think a hard coded string compiled inside the original source code somewhere). My point is that this is just not about ORM’s and NHibernate specifically, but the way the developers have configured the technology, their design decisions, and their general skills and nous.

Often, very often, these ORM’s map 1:1 with every database table and construct the joins, updates, upserts in .NET and ORM code and on-the-fly. Obviously this can perform badly on OLTP systems. Furthermore when the application code has been written this way and these performance issues arise, it is near impossible to identify and tune database query performance without a debugger (ie. a developer sitting there with Visual Studio open pressing the F10 button to step through the code line by line), trawling through application log files identifying slow steps or blocks or reviewing exception stack traces, or worst of all, raising a JIRA ticket and waiting for someone/something to happen hamstrung in the knowledge that you know you could quickly resolve the problem yourself if only you could get access to the codebase. Putting this another way, don’t count on being able to just nip in and change a piece of PL/SQL logic to improve performance or fix a big, as the join and/or business logic may be embedded in the application executables - and not as (PL/)SQL but for NHibernate in HQL, LINQ, and the .NET programming language [C#, F#, ...]). If you think you are, in HQL, going to get the benefits of the result cache, or efficient ‚insert ... returning into‘ single DB round trip constructs, or efficient merge on update/delete handling, or use more left field Oracle functionality such as pipelined table functions ... it really just isn’t going to happen. If you want to Google out HQL and convince yourself it a variant of SQL, you would be right, almost, sometimes, but it is a poor mans SQL (in my view if you want to limit SQL to the subset that is HQL ‚like‘ and ‚subcontract out' the performance to the ORM, there really is no point in paying big bucks for the features of an enterprise database such as Oracle). There are many other negatives but on the plus side, NHibernate (and other ORM’s) more often than not provide the ability to create database schema objects and FK relationships within the application code, so formal hand-crafted schema genesis scripts are not necessary for application installation and setup. Most ORM’s, including NHibernate, provide the ability to swap-in/swap-out one DB implementation for another, meaning the application codebase, with little reconfiguration, may be used against Oracle, MySQL and more. Automated testing, with judicious choice of Mocks and Stubs for the various DB implementations/drivers and test cases, is also easily achievable too (utPLSQL has always been there, but when you adopt the use of ORM’s such as NHibernate and the like, this is now done in the .NET unit or integration test suite, not in the native database programming language). ORM’s such as NHibernate allow the same .NET code to target different database versions as well, again usually by configuration rather than changing handcrafted PL/SQL (that you rarely write).

I could ramble on for an hour but I will stop. From the information provided however, there is not enough information to highlight the pitfalls or the advantages of their approach. Perhaps you should ask your (external) developers to elaborate, for ask for a technical/architecture review? At that juncture only then will you know whether you are going to sleep easy, panic, or consider taking early retirement.

Oh, and

> generated are large and often they are not using bind variables.

Depending on how the NHibernate and persistence layer logic is configured and written by the external developers, it may be using bind variables/named variables.

Finally count yourself lucky you are encountering ORM’s in 2019. They have matured a lot. Just over 15 years ago I was forced to write code against a terrible buggy underperforming buggy (have I mentioned this) poorly documented buggy ORM known as TopLink. I cannot remember the TopLink vendor of the top of my head :-) You might choose to Google ’Toplink ORM’ and let me know.

Mike

—

Dr. Michael D. O’Shea, https://www.linkedin.com/in/michaeldoshea Woodward Informatics Ltd, http://www.strychnine.co.uk

> Am 30.04.2019 um 22:26 schrieb Jeff Chirco <backseatdba_at_gmail.com>:
>
> Anybody here have developers using NHibernate as ORM for their applications? Apparently it is some sort of translation layer from applications objects to database tables. Kind of lke Entity Framework. We are used to doing all custom development with procedures but we have a third party company developing an applications for us and this is what they want to use.
> As a DBA I am hesitant with this type of auto development layers. I tend to find the queries generated are large and often they are not using bind variables. If anyone has options on NHibernate I would love to her it.
>
> Thanks,
> Jeff
>
>

--
http://www.freelists.org/webpage/oracle-l