Improve performance of a field update?

knocte · **Joined:** Thu Jun 29, 2006 11:49 am **Posts:** 13

Hello!

I have made my first proof of concept with NHibernate (1.2.0 alpha), in which I load an object and change one property of it.

Tracing the SQL received by the database I have observed that NHibernate is using a SET statament where it updates many fields, instead of only updating the one that has been changed:

Code:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;BEGIN TRANSACTION
exec sp_executesql N'SELECT irmtermina0_.IDTERMINAL as IDTERMINAL0_, irmtermina0_.NAME as NAME1_0_, irmtermina0_.ISADTERMINAL as ISADTERM4_1_0_, irmtermina0_.GUID as GUID1_0_ FROM IRM_TERMINALS irmtermina0_ WHERE irmtermina0_.IDTERMINAL=@p0', N'@p0 int', @p0 = 22467
exec sp_executesql N'UPDATE IRM_TERMINALS SET NAME = @p0, ISADTERMINAL = @p1, GUID = @p2 WHERE IDTERMINAL = @p3', N'@p0 nvarchar(4000),@p1 bit,@p2 nvarchar(4000),@p3 int', @p0 = N'MUX-11MUX-11MUX-11MUX-11', @p1 = 0, @p2 = N'43a1a195-ace6-4939-aed1-416805dd578e', @p3 = 22467
COMMIT TRANSACTION

Does anybody know how to avoid this to improve performance? Is this a feature that is not yet implemented??

Thanks in advance.

Andrés G. Aragoneses [ knocte ]

--

gert · **Joined:** Thu Jan 19, 2006 4:29 pm **Posts:** 348

knocte wrote:

Does anybody know how to avoid this to improve performance? Is this a feature that is not yet implemented??

Specify dynamic-update="true" for <class> mapping

Gert

knocte · **Joined:** Thu Jun 29, 2006 11:49 am **Posts:** 13

Thanks for your reply Gert!

Quote:

Specify dynamic-update="true" for <class> mapping

And now I'm wondering, why this setting is not the default?

Thanks again.

Andrés G. Aragoneses [ knocte ]

--

nokiola · **Joined:** Sun Jun 19, 2005 2:21 pm **Posts:** 21

Is not default due to performance penalty... NHibernate has to determine which fields have changed their value.

Regards,
Robert

knocte · **Joined:** Thu Jun 29, 2006 11:49 am **Posts:** 13

But re-setting all values for a database row is also dramatic for performance... Which loss is worst? I suppose that the database loss is worst because it involves writing data to disk instead of just checking for boolean values that are in memory...

Regards and thanks for your answer.

subdigital · **Posted:** Thu Jun 29, 2006 9:56 pm

They are in memory, but NHib reads them using reflection, and this happens for every single object in the graph that is being saved.

I think the performance benefit is greater on the .NET side by not specifying dynamic-update.

knocte · **Joined:** Thu Jun 29, 2006 11:49 am **Posts:** 13

Thanks for your answer subdigital.

Any more opinions on this?

Couldn't it be done without reflection?

Regards.

gert · **Joined:** Thu Jan 19, 2006 4:29 pm **Posts:** 348

subdigital wrote:

They are in memory, but NHib reads them using reflection, and this happens for every single object in the graph that is being saved.

I do not quite understand.

NHibernate has to read them in any case - to assign values to the qurey parameters. And to determine if the object is dirty or not. So, the only difference should be that on dynamic update, the SQL update statament must be contructed for each object separately.

Gert

knocte · **Joined:** Thu Jun 29, 2006 11:49 am **Posts:** 13

Thanks for your answer Gert!

Quote:

NHibernate has to read them in any case - to assign values to the qurey parameters. And to determine if the object is dirty or not. So, the only difference should be that on dynamic update, the SQL update statament must be contructed for each object separately.

From you comment, then, I understand the following:

1) No reflection is used to distinguish dirty attributes from non-dirty ones.
2) If the first is true, then the performance loss is not such dramatic.

Then why this is not the default behaviour? :)

Thanks

subdigital · **Posted:** Fri Jun 30, 2006 12:14 pm

gert wrote:

subdigital wrote:

They are in memory, but NHib reads them using reflection, and this happens for every single object in the graph that is being saved.

I do not quite understand.

NHibernate has to read them in any case - to assign values to the qurey parameters. And to determine if the object is dirty or not. So, the only difference should be that on dynamic update, the SQL update statament must be contructed for each object separately.

Gert

Ahh, you're right. This makes a lot more sense, b/c each object will need a different update statement.

gert · **Joined:** Thu Jan 19, 2006 4:29 pm **Posts:** 348

knocte wrote:

Quote:

NHibernate has to read them in any case - to assign values to the qurey parameters. And to determine if the object is dirty or not. So, the only difference should be that on dynamic update, the SQL update statament must be contructed for each object separately.

From you comment, then, I understand the following:

1) No reflection is used to distinguish dirty attributes from non-dirty ones.

No: refelection IS used to read values ov properties. BUT: those values must be read in, at least to set the value of query parameter

Quote:

2) If the first is true, then the performance loss is not such dramatic.

Well, string manipulation is quite costly operation in .Net

Edit: There might be other perfomance considerations also, but someone more intimate with NHibernate internals should comment that.

Gert

smudges · **Posted:** Mon Jul 03, 2006 3:43 pm

If you are using Nhibernate 1.2Alpha with the new lighweight code generation, then reflection is NOT used for setting/retrieving values from the object. It uses lightweight, compiled code and is as fast as if you HAND WROTE the code yourself! :-)

gcook1@shaw.ca · **Joined:** Tue Feb 07, 2006 4:27 pm **Posts:** 69

Great discussion guys. I plan on moving to 1.2 Alpha to take advantage of the performance improvements...

I'd also like to know if a developer can ask NHibernate for a list of changes to an object?

I need this to provide an audit trail of the changes to each object, and since NH needs to know what has changed for the dynamic insert/update features, could I not just 'plug' into this?

subdigital · **Posted:** Tue Jul 04, 2006 3:41 pm

Yeah, you can. just create a class that implements IInterceptor. You'll see a few methods in there were you can see currentState vs. previousState. I would construct some objects for logging this information and then save it all OnPostFlush().

knocte · **Joined:** Thu Jun 29, 2006 11:49 am **Posts:** 13

smudges wrote:

If you are using Nhibernate 1.2Alpha with the new lighweight code generation, then reflection is NOT used for setting/retrieving values from the object. It uses lightweight, compiled code and is as fast as if you HAND WROTE the code yourself! :-)

Wow, very interesting information! Thanks.

Then, if the dynamic-update feature is less performance-killer in the new alpha version, why not enabling it by default?