NHibernate show-sql - why is the SQL shown invalid?

Question

Hi all

I love the feature in NHibernate that shows the generated SQL. But when I pasted it into SQL Server Management Studio, I realised it's not actually valid!

Here's an example:

SELECT person0_.PersonId as PersonId1_0_, person0_.Title as Title1_0_, person0_.FirstName as FirstName1_0_, person0_.MiddleNames as MiddleNa4_1_0_, person0_.LastName as LastName1_0_ FROM Kctc.People person0_ WHERE person0_.PersonId=@p0;@p0 = 1

This is not valid because of the way the parameter p0 is specified. It needs:

DECLARE @p0 int
SET @p0 = 1

before the SELECT command.

I guess my question is: why does NHibernate not show the ACTUAL SQL it sends to the database? Why does it show this instead?

Is there something I'm missing?

Thanks

David

Answer 1

The SQL is sent to the driver as a prepared statement, either the SQL driver takes care of assembling the final SQL or the parameters are sent seperatly to the server in case of a server side prepared statement - neither of which hibernate has much control over, or any way of pulling back the final SQL after it's sent down to the driver.

hibernate is just showing you that parameterized SQL as well as the value of @p0 that it hands off to the SQL driver.

Answer 2

The command sent to SQL Server is sent is a string argument to the sp_executesql system stored procedure. The parameters list is sent as a list of parameter, value pair arguments. You can easily see this using SQL Profiler. NHibernate Profiler reformats the query into one you can cut and paste into SSMS.

Here's an example of an actual NHibernate object load as sent to SQL Server:

exec sp_executesql N'SELECT track0_.TrackId as TrackId84_0_, track0_.CreatedBy as CreatedBy84_0_, track0_.CreatedDt as CreatedDt84_0_, track0_.RevisedBy as RevisedBy84_0_, track0_.RevisedDt as RevisedDt84_0_, track0_.Name as Name84_0_, track0_.Description as Descript7_84_0_ FROM Nucleus.Track track0_ WHERE track0_.Name=@p0',N'@p0 nvarchar(8)',@p0=N'6036HPGP'

Since the query is sent as a string to sp_executesql, you may get an execution plan that is different than the one generated by executing the query directly. In some cases this can be a huge performance hit.