Costa, Oskar is correct, using Identity columns does not perform, you are better off using HiLo instead. You can easily prove it, if you have a long transaction inserting many records in the table with an identity column, even the INSERT commands are delayed to the end of the process, the Save method in NHibernate returns the Id, that round trip is a killer. I have seen processes were 10k records had to be created that were 10 times slower using Identity instead of HiLo. Any communication between the AppServer and Database server on the wire is expensive.
As Oskar also mentions, batching can also improve performance, again, because you might reduce the number of loops between AppServer and Database. On Wednesday, November 28, 2012 8:39:28 PM UTC, costa wrote: > > @Gunnar Liljas: If you are joking ignore my message. If you are not joking > this is the query that gets executed by NH when a new record is inserted in > a table with an identity(1, 1) field: > > INSERT > INTO > [ > dbo].[DataType] ( > [Name] > ) > VALUES > (@p0); > select > SCOPE_IDENTITY(); > > Note the select SCOPE_IDENTITY(); statement that's returning the new id in > the same database call. Yes, it's an additional statement to get it but > it's embedded in the same call and it should be very fast to get that value. > > As Sam pointed out, there is a price to pay for using GUIDS. I actually > have done performance tests because I had an idiot at work that started to > use them everywhere, and the performance starts to degrade pretty soon. > They are also ugly when you write and run queries. > > > > > -- You received this message because you are subscribed to the Google Groups "nhusers" group. To view this discussion on the web visit https://groups.google.com/d/msg/nhusers/-/HoICwo6my_8J. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/nhusers?hl=en.
