nHibernate Recompiles and Execution Plans

April 29, 2008 at 6:28 pm (nHibernate) (, , , , , )


One little potential for performance problems that we’ve seen comes out of how nHibernate generates it’s parameratized queries. It limits the length of any parameter to the length of the column, but if the length of that parameter is less than the column, it uses tha smaller length when declaring the variable. This results in a query that looks like this:

exec sp_executesql N'INSERT INTO dbo.users (Name, Password, EmailAddress, LastLogon, LogonId) VALUES (@p0, @p1, @p2, @p3, @p4)',N'@p0 nvarchar(8),@p1 nvarchar(6),@p2 nvarchar(12),@p3 datetime,@p4 nvarchar(8)',@p0=N'Ted Cool',@p1=N'abc123',@p2=N'ted@cool.com',@p3='2008-04-29 14:10:44:000',@p4=N'ted_cool'

Note the parameter @p4 which is the parameter mapping to the primary key for the little sample table. In this query it’s declared as nvarchar( 8 ) because ‘ted_cool’ is composed of eight characters. But if we changed it to ‘ted_coolish’:

exec sp_executesql N'INSERT INTO dbo.users (Name, Password, EmailAddress, LastLogon, LogonId) VALUES (@p0, @p1, @p2, @p3, @p4)',N'@p0 nvarchar(8),@p1 nvarchar(6),@p2 nvarchar(12),@p3 datetime,@p4 nvarchar(11)',@p0=N'Ted Cool',@p1=N'abc123',@p2=N'ted@cool.com',@p3='2008-04-29 14:13:30:000',@p4=N'ted_coolish'

Now that same parameter is declared as nvarchar(11). So if we look at the procedure cache to see what’s inside for this query:
 
SELECTobjtype
,p.
size_in_bytes
,[sql].
[text]
FROM
sys.dm_exec_cached_plans
p
OUTER APPLY sys.dm_exec_sql_text(p.plan_handle)
sql
WHERE
[text] LIKE
‘%INSERT INTO dbo.users%’

 We get the following results (I ran the test a few times, so I have more than two rows):

Prepared 40960 (@p0 nvarchar(8),@p1 nvarchar(6),@p2 nvarchar(12),@p3 datetime,@p4 nvarchar(11))INSERT INTO dbo.users (Name, Password, EmailAddress, LastLogon, LogonId) VALUES (@p0, @p1, @p2, @p3, @p4)

Prepared 40960 (@p0 nvarchar(8),@p1 nvarchar(6),@p2 nvarchar(12),@p3 datetime,@p4 nvarchar(10))INSERT INTO dbo.users (Name, Password, EmailAddress, LastLogon, LogonId) VALUES (@p0, @p1, @p2, @p3, @p4)

Prepared 40960 (@p0 nvarchar(8),@p1 nvarchar(6),@p2 nvarchar(12),@p3 datetime,@p4 nvarchar(12))INSERT INTO dbo.users (Name, Password, EmailAddress, LastLogon, LogonId) VALUES (@p0, @p1, @p2, @p3, @p4)

Prepared 40960 (@p0 nvarchar(8),@p1 nvarchar(6),@p2 nvarchar(12),@p3 datetime,@p4 nvarchar(8))INSERT INTO dbo.users (Name, Password, EmailAddress, LastLogon, LogonId) VALUES (@p0, @p1, @p2, @p3, @p4)

For what should have been a single plan, I have four (or more) clogging up the cache and causing unecessary compiles, etc. I’m not completely enamored with what this tool is going to do tofor me as a dba.

5 Comments

  1. Roger said,

    Interesting, we’re having a problem with this.

    I posted a quesiton on the NHib forum.

    http://forum.hibernate.org/viewtopic.php?p=2385009#2385009

  2. Query Fingerprints and Plan Fingerprints « Home of the Scary DBA said,

    […] I noted in a previous post, nHibernate will create a lot of execution plans. With the capabilities here, we’ll be able […]

  3. Dario Quintana said,

    Have you tried setting up to true the ‘prepare_sql’ option into your configuration?

  4. scarydba said,

    No. I haven’t done a lot more experimentation since I posted this data. We’re still waiting for an initial release from the development team so that we can start looking at what they did and how it behaves. I will check to see if they used that option.

  5. Yesterday's news | NHibernate parameter sizes controversy said,

    […] issue was first reported in a blog post by Grant Fritchey on 04/29/2008. He discovered that the way NHibernate (then 1.2) generates its parametrized queries (specifically […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: