I have recently joined a company with an unreasonable DBA. They hired me as an architect because of a maintainability problem due to poor coding practices. I have used hibernate for years, NHibernate for the past couple of years, and the usual SPROC/reader pattern before that.
Their is only one time using hibernate where I have gone back to a stored procedure, other than that I have been able to accomplish a solution to some very complex data retrieval problems with(n)hibernate. Anyhow, alot of the developers love the whole ORM approach to problems because their data model is often changing. They have around 600 sprocs so maintainability is again, a HUGE problem. In my experience, hibernate really REALLY helps with the maintainability and data model changes. Anyhow, I'm looking for some DB minds to help me fight the good fight.

Preconditions
- Latest NHibernate could be used
- All Criteria & HQL would be parameterized
- All inserts & updates are NON-dynamic. I have no set dynamic-insert or dynamic-update in any mapping to true

Below are the arguments of the DBA.

1) EVERY statement, must have WITH(NOLOCK). ie SELECT foo FROM bar WITH(NOLOCK). If it doesn't it will cause mass lock contention.
2) ALL selects must be done against views because of his "maintainability" practices. One of their views contains 14 joins!
3) Nulls are bad. Foreign keys cannot even be null, they have to map to the 0 record (which is blank information). While I agree that NULLs shouldn't be used everywhere, they are needed on foreign keys.
4) EVERY statement (insert, update, delete) MUST be a sproc. Even dynamic SQL must be in a sproc so it can be fixed live. The DBA can skip QA and often does. Apparently, deploying DLL patches is bad.
5) Identities are the devil and are not to be used because they do not replicate.
6) Unique identifiers are not be used because they do not replicate. I thought that was one of their main purposes.
7) do not use BIT, always use INT. His argument, BITs cannot be used in indexes. I'm not sure why you would EVER use a bit in an index!?
8) ORMs are bad because they create dynamic SQL that is recompiled and causes heavy load on the server.

Thank you in advance to any who reply.

Well I just can imagine something for some of the points from what I've seen with version 1.2.1GA

3) Assuming that they do not try to store objects with null values this won't be much of an issue. I hardly use NULL's for normal values since I null checks all over the place. If you may never ever have NULL's in foreign keys you might need something like a "Dummy" entry in the referenced table and map that in case of a foreign key that has to be NULL. I don't know if this works in the .hbm files but it certainly is possible in code.

5) Your can use GUID's there and set a different ID generator other than identity in the hbm files. I've also heard of separate id spaces like identity 1 to 1000000 on server A and identity 1000001 to 2000000 on server B but I don't know if that works out well or leads to one big WTF in the near future.

7) I have to admit I've never used bit for booleans so far. I've just mapped them to int columns and things were fine. Maybe all hell breaks loose when someone manually updates the column values to 4 instead of 1. Like.. this value is really really really very true. ;)

The main problem you get here with hibernate is with the unsaved-value. For most of the generators hibernate defaults to 0 as unsaved-value (or 0...-0...-0...-0... for guids). So if you decide to use 0 instead of null for fk, hibernate can't use that value. I think you can tell hibernate that you take care of saved/unsave state, but then you use a lot of very handy functionality that hibernate does behind the scens.



I'm not sure, but I think you can achieve that with writing your own dialect based on the existing one for your database.

You may want to post that question to the nhusers group at google. You'll probably get more answers there.

http://nhforge.org/groups/nhusers/default.aspx

Thanks for your replies.

I'm actually not looking for NHibernate workarounds. I can accomplish most of them with some degree of effort. What I'm looking for is an database-minded person to debunk what this DBA is saying.

I know indexes and db design. However, I am not a DBA. I've contracted in alot of places and this is the first I've come across with all these insane database rules.

I'm afraid you can't easily debunk these statements. If the DB is used as a store for multiple different applications on different plattforms then a lot of this rules makes sense.

Not that you couldn't solve this issues in you applications but from the classic DBA point of view where the DB is the center of the universe ;-).

So the only thing I can help you with is my opinion on the arguments:



Can't really judge this one. You can set an isolation level in hibernate which is used for the all statements inside a transaction. But I think that's not quite the same. As I mentioned, you may work around this with your own dialect. But If you follow argument 4) then this shouldn't be a problem anyway.



That's ok, if the view is simply a wrapper for a single table. When you have joins, then IMHO it's not a "maintainability" view.

You can map views with hibernate as you would map a table.



Doesn't make sense for me when speaking about fk. It's ok for "normal" columns.

This possibly would be a killer if you want to use hibernate.



Yes, that's what they love ... makeing changes to production database. Pass this argument to Change Management, sit back and watch the battle ... :-D.

If that's really an issue ... you can put almost all statement that hibernate uses into the mapping files. And the mapping files can be kept outside the dll's. So you can regard them as configuration and change them easily in a live environment.



Then what should you use ? Afaik GUID is exactly for this purpose.



Then don't use bit ... you can map bool to int with hibernate. There is a discussion about this going on on nhusers group.



Can't really judge this one. But you can use sprocs with hibernate for all operations.

You probably get more qualified answers for 1) + 8) from the guis at nhusers group.

Wow I think I've worked at that company too. No I had a 'architect' that I got into with about some of this same stuff. It was part of the reason I left the previous job. My rebuttals are below:


SQL Server 2005 does MVCC level locking so this is unnecessary:
http://en.wikipedia.org/wiki/Multiversi ... cy_control


BS: this practice is based off of years of bad programming. ORM helps adapt to refactoring faster and better than any other practice. Part of writing good code is being able to write good code.


There are business needs for nulls in situations, I call BS here to. Take an Amount for instance, zero is much different than null and is aggregated differently.


Sprocs are evil. I blogged about this extensively:
http://scottwhite.blogspot.com/2008/04/ ... dered.html
http://scottwhite.blogspot.com/2008/06/ ... lowup.html


They replicate fine for one way replication.


GUIDs are needed for two way replication from what I know about it, but this scenario is best to avoid. SQL Server doesn't scale well and you should consider Oracle.


Everything does not need to be indexed. When the only possible values of a field a 0 or 1 an index is overkill



ORMs are good because they reduce duplicity of code and the code is optimized at the engine level not at the programmer level. It also can reduce round trips to database because of Lazy Loading. i.e. no more data is returned than needed.

With his approach and his denormalized views things are going to be much less efficient, not much more efficient.

This DBA should be fired and you should begin a political revolt against him.

Latest DBA Arguments
- BITs convert to INTs for comparison, which causes performance issues thus BITs are not to be used.
- WITH(NOLOCK) and inserts with (XLOCK) are used because of idents not being used. The XLOCK locks the table down because the insert sproc needs to do a SELECT MAX(ID). #5 would apparently solve both issues lol.

I couldn't agree more with you kibbled_bits about the need for ORMs. DBA's often only see the dynamic SQL and the loss of alot of SPROC control. They see this as a performance loss, but they never seem to see the RAD factor nor do they consider the other mechanisms such as built-in caching that can actually make ORMs comparible if not faster than the standard SPROC to datareader/datatable.


I just need a little more firepower :)

PS - thank you for the excellent response!!

My guess is that you are at a large company, because it's usually only large companies who can waste money on having a debate of INTs versus BITs in database performance.

To me the INT vs BIT is moot and I couldn't care either way. Most database performance issues are not from poor indexes or even the existence of stored procedures but the process in which you get the data. This has more to do with the business process and the layout of your screens.

A DBA could tune a table in get a query 15% more efficient, and this may deliver results that are less than 5% more efficient. This is because the entire process of requesting the data involves establishing a connection, handling HTTP requests, etc. However if application caching and fewer requests to the database is made than the results can be dramatic.

I would debate on higher level issues with him, if you go low level arguments with him it's likely that both of your peers will get lost on the debate and you will not make progress. Keep the debates high level and use terms like flexibility, extensibility, effectiveness, etc because in my experience ORM provides a better total package to a project.

Example programmers could write everything procedurally and generally get better performance. Why don't we? Because it costs more to maintain, extend and refactor. This DBA of yours living in the Jurassic period is making the equivalent argument. You will likely not convince him, but may convince your peers.

With enough pressure applied to him from you and others he may back off on his demands.

I'll close with a quote from Steve Wozniak