SQLServerCentral / Discuss Content Posted by David Poole / Article Discussions / Article Discussions by Author / An nHibernate Head Scratcher / Latest Posts

RE: An nHibernate Head Scratcher

Jon Russell — Mon, 18 Apr 2011 11:25:08 GMT

Nice article, David. Good problems solving skills.

RE: An nHibernate Head Scratcher

David.Poole — Wed, 13 Apr 2011 02:57:48 GMT

[quote][b]Thomas Keller (4/11/2011)[/b][hr]Rather than changing the server default collation just to avoid the issue on the next database creation, how about changing the collation in the model database?[/quote]Nice thought, but you can't change the collation sequence on a system DB, short of rebuilding the master

RE: An nHibernate Head Scratcher

Richard Torrone — Tue, 12 Apr 2011 11:28:19 GMT

I know you aren't happy about using the NVarchar datatype on the table columns but still I have this question. If you converted the varchar in the table to be nvarchar to match the parameter coming in from the Unicode ORM framework would it have caused a seek, solving the problem, and negating the need to change coallation settings on the DB/Server.It's so much fun to watch someone else assume the issue to be the lowest level, index/data corruption type issue, and finally work their way back up to 30,000 feet and find the issue right outside the window. I wonder if the Guru DBAs would have started with coallation? Gosh, I hope not.Great Article!

RE: An nHibernate Head Scratcher

jpenniman — Tue, 12 Apr 2011 05:57:07 GMT

We had a similar problem in one of our apps. The solution for us was to use the correct nhibernate types in the mapping.Like you, our tables have Varchar. We noticed that hibernate was generating nvarchar for the data types.Per the hibernate documentation, string will generate nvarchar in sql. The use of AnsiString will generate varchar parameter types and Sql Server is happy.I'm on my smartphone at the moment, so I can't provide specifics. When I'm at a pc, I'll update with specific docs and code samples.

RE: An nHibernate Head Scratcher

Thomas Keller — Mon, 11 Apr 2011 18:54:41 GMT

Rather than changing the server default collation just to avoid the issue on the next database creation, how about changing the collation in the model database?

RE: An nHibernate Head Scratcher

beargle — Mon, 11 Apr 2011 15:50:57 GMT

David, this was an interesting read. Thanks for your post. We have used the script here:[url]http://blogs.msdn.com/b/ikovalenko/archive/2006/12/03/alter-database-alter-collation-forced.aspx[/url]when changing database collations. Hopefully you can also put it to good use when the time comes.

RE: An nHibernate Head Scratcher

David.Poole — Mon, 11 Apr 2011 15:19:49 GMT

There are a couple of ways of fixing it. The one that springs to mind is as mentioned in Todd's blog. Change the mapping file in nHibernate from using String to using AnsiString.

RE: An nHibernate Head Scratcher

John Lingerfelt — Mon, 11 Apr 2011 15:04:33 GMT

We discovered this same problem with nHibernate about 6 months ago. It was running index scans instead of seeks. The datbase columns were defined ad VARCHR but Hibernate was passing NVARCHAR. After we brought it to the attention of the people who developed the offending application they were able to make some modifications to the configuration parameters for nHibernate and corrected the problem. I am not familier enough with nHibernate to know exactly what they did.

RE: An nHibernate Head Scratcher

corey lawson — Mon, 11 Apr 2011 12:44:24 GMT

Except, in the analysis, it isn't really a problem related to or caused by the ORM itself or the SQL it produces, it's a subtle issue invoked by implicit casting of types and collations, that could sneak in unobtrusively and unintentionally from anywhere.And, throw in a subtle bug for a non-US English installs...I think I've dealt with a similar issue with just straight up data loaded from different data sources...VERY subtle bug.

RE: An nHibernate Head Scratcher

WayneS — Mon, 11 Apr 2011 10:42:15 GMT

FYI, a short time ago I started a forum post asking for discussion on the usage of ORM tools:[url]http://www.sqlservercentral.com/Forums/Topic1087686-391-1.aspx[/url]If you have experience (good or bad) in working with ORM tools with SQL, please post your experience and thoughts there.Thanks!

RE: An nHibernate Head Scratcher

magarity kerns — Mon, 11 Apr 2011 10:34:20 GMT

[quote][b]dg81328 (4/11/2011)[/b][hr]Not a bug in SQL Server because you can choose a different collation when you are setting up SQL Server. It is more of a human bug for not checking how to do a proper setup.[/quote]You should read the bit where the author said the installer ignored his selected collation before you say snooty things like this.

RE: An nHibernate Head Scratcher

Steve Jones - SSC Editor — Mon, 11 Apr 2011 09:55:13 GMT

nvarchar has a higher precedence, so the varchar -> nvarchar works fine, as noted by Mr. Poole. [url]http://msdn.microsoft.com/en-us/library/ms190309.aspx[/url]Converting down causes the index issues. Lots of ORMs and some frameworks will submit N'' values and if you have varchar fields and indexes, they don't get used.

RE: An nHibernate Head Scratcher

dg81328 — Mon, 11 Apr 2011 09:54:31 GMT

Not a bug in SQL Server because you can choose a different collation when you are setting up SQL Server. It is more of a human bug for not checking how to do a proper setup.To change a server collation:export all the data and script all the users.drop the databases and rebuild the master using the correct collationrecreate the users and databases and import the dataCollations are hierarchical. By that I mean they can be easily over-ridden.A database collation can be set to operate differently from the server and a column can be set to operate differently from its database. A piece of code can be set to operate according to the collation of the object from which it is pulling data. MS example: SELECT name FROM customer ORDER BY name COLLATE Latin1_General_CI_AS. (I changed the collation name from the MS example to make it more relevant)The problem here was PBCK.DLL; not SQL Server. Sorry to be bobby badnews but I would like to see articles that are consistent with MS documentation when it is correct and consistent with the behavior of SQL Server when it is administered correctly.Don

RE: An nHibernate Head Scratcher

CheeseheadDBA — Mon, 11 Apr 2011 09:39:11 GMT

Right - I was still getting the seek even when I explicity declare a parameter as a nvarchar and used that to query my nvarchar field, like he did in his article. I still received a seek even after I cleared out the query cache and rebuilt the non-clustered index.

RE: An nHibernate Head Scratcher

David.Poole — Mon, 11 Apr 2011 09:36:52 GMT

If your field is an NVARCHAR and you use VARCHAR parameters you will get a seek because SQL is happier to turn a VARCHAR into an NVARCHAR than the other way around.It's worth noting that anything that involved converting a string value into another value can also have the problem.SQL Server can take a statement such asWHERE DateCreated BETWEEN '2011-04-01' and '2011-04-11'and get exactly the same type of implicit conversion.

RE: An nHibernate Head Scratcher

CheeseheadDBA — Mon, 11 Apr 2011 09:19:37 GMT

Our developers use nHibernate here and we also use a collation of SQL_Latin1_General_CP1_CI_AS, so I was curious if I would be able to reproduce this same issue. We actually have a very similiar scenario, a table that holds person information with a non-clustered index on the LastName field, which is a dataype of nvarchar(100). Everytime I tried to reproduce your issue, it always did a clustered index seek, not a scan. This is good, however I was kind of hoping I would be able to reproduce it to convince our developers to move away from nvarchar :). I've tried clearing the query cache, dropping/recreating indexes, etc, and it always seems to perform a scan. We are using SQL Server 2005 - any ideas?Adam

RE: An nHibernate Head Scratcher

Mitch Miller — Mon, 11 Apr 2011 08:21:23 GMT

Oh wow. I'd have never thought of checking the collation. Thanks for the article and the good reminder. Headed off now to check the collation on my prod. databases to make sure it's what I think it should be. :)

RE: An nHibernate Head Scratcher

Todd M. Owens — Mon, 11 Apr 2011 07:58:16 GMT

In my experience, use of so-called ORM (Object-Relational Management) tools create as many or more problems than they solve. There are many times we haven't got a choice because a shop's methods & tools team have drank the ORM Kool-Aid. However I always recommend against using ORM for one primary reason:[b] Every SQL call deserves to be deliberately designed.[/b] As most readers here know, when an ORM is used, code runs to dynamically generate SQL. This code is designed at the meta-level, so it is difficult if not impossible to optimize the SQL out of the box.It remains my considered opinion that ORM tools are nothing but an OO developers excuse to not to have to deal with SQL specifically and the relational model in general. The wisest OO developers know that the very best domain models are compliant with, if not based upon, a fully-normalized relational database models.

RE: An nHibernate Head Scratcher

Carlton Leach — Mon, 11 Apr 2011 05:53:55 GMT

In relation to the: "The answer lies in a bug in the cluster installer for SQL2008".Does this bug exist in R2 aswell?Carlton.

RE: An nHibernate Head Scratcher

GlenParker — Mon, 11 Apr 2011 02:51:52 GMT

Good drills, thanks for the tips!

RE: An nHibernate Head Scratcher

James A Skipwith — Mon, 11 Apr 2011 02:30:18 GMT

Nice Article David, it certainly highlights a potential problem when using ORMs against SQL Databases (and dynamic SQL in general!).I think many people though, especially junior developers/DBAs, could make more use of this article as a fine example of iterative problem solving - i.e. not taking a sledgehammer to the issue but working through it logically one step at a time.This was both an enjoyable and interesting read for a Monday morning. Thanks.

An nHibernate Head Scratcher

David.Poole — Sun, 10 Apr 2011 22:39:29 GMT

Comments posted to this topic are about the item [B]An nHibernate Head Scratcher[/B]