SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


An nHibernate Head Scratcher


An nHibernate Head Scratcher

Author
Message
Dave Poole
Dave Poole
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17164 Visits: 3403
Comments posted to this topic are about the item An nHibernate Head Scratcher

LinkedIn Profile
www.simple-talk.com
James A Skipwith
James A Skipwith
Mr or Mrs. 500
Mr or Mrs. 500 (574 reputation)Mr or Mrs. 500 (574 reputation)Mr or Mrs. 500 (574 reputation)Mr or Mrs. 500 (574 reputation)Mr or Mrs. 500 (574 reputation)Mr or Mrs. 500 (574 reputation)Mr or Mrs. 500 (574 reputation)Mr or Mrs. 500 (574 reputation)

Group: General Forum Members
Points: 574 Visits: 725
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.

James
MCM [@TheSQLPimp]
GlenParker
GlenParker
SSC Eights!
SSC Eights! (807 reputation)SSC Eights! (807 reputation)SSC Eights! (807 reputation)SSC Eights! (807 reputation)SSC Eights! (807 reputation)SSC Eights! (807 reputation)SSC Eights! (807 reputation)SSC Eights! (807 reputation)

Group: General Forum Members
Points: 807 Visits: 133
Good drills, thanks for the tips!

Glen Parker :-)
Carlton Leach
Carlton Leach
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1612 Visits: 1304
In relation to the: "The answer lies in a bug in the cluster installer for SQL2008".

Does this bug exist in R2 aswell?

Carlton.
Todd M. Owens
Todd M. Owens
SSC-Enthusiastic
SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)

Group: General Forum Members
Points: 110 Visits: 471
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: Every SQL call deserves to be deliberately designed.

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.
Mitch Miller
Mitch Miller
SSC Veteran
SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)

Group: General Forum Members
Points: 237 Visits: 46
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. Smile


--Mitch
Adam Kreul
Adam Kreul
SSC Veteran
SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)

Group: General Forum Members
Points: 265 Visits: 766
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 Smile. 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
Dave Poole
Dave Poole
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17164 Visits: 3403
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 as
WHERE DateCreated BETWEEN '2011-04-01' and '2011-04-11'
and get exactly the same type of implicit conversion.

LinkedIn Profile
www.simple-talk.com
Adam Kreul
Adam Kreul
SSC Veteran
SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)

Group: General Forum Members
Points: 265 Visits: 766
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.
dg81328
dg81328
Valued Member
Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)

Group: General Forum Members
Points: 65 Visits: 160
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 collation
recreate the users and databases and import the data

Collations 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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search