Hi, Thanks for taking the time to answer my question.
First I'd like to say I founded the reason why the batch-size was limited to 100. It was due to Visual Studio 2008 who, for an unknown reason, was not updating my nhibernate config file (at least that dude - https://forum.hibernate.org/viewtopic.php?f=25&t=969413&view=next - has his answer, two years later). Still I studied both of answers you guys gave me (did I already thank you for sharing your knowledge?) and I made some tries. Test collection is around 36 000 items. My collection items is mapped as a collection (list) of components. Amongst benefits, I liked the fact that nhibernate does not send update statements after inserts and gets smart enough to make a one shot delete. This is pretty useful, as I lack of time (two many code, so little time), I might do some q'n'dirty create/delete for my test cases. If I map a collection as a list of entities nhibernate will send a bunch of insert statements, then as many update statements to update the foreign key and when deleting another bunch of delete statements. So mapping like this my collection divides by three the number of statements of my test cases. In addition to that, the collection has no sense without its wrapper, mapping it as a collection makes its lifetime coherent (do you say coherent in english?). Here are my first results : NHibernate, 36 000, batch size 100 : 100 seconds. NHibernate, 36 000, batch size 1000 : 75 seconds. Here I can deduce : - time between two queries is huge. 25 seconds saved. - time is non linear => I guess the physical size of resulting queries is what makes it slow. - it still need to reduce this time I also tried using ADO.Net as described in the article you linked. I used the last example (said to be the fastest). => for the same test case : more than 140 seconds. Finally I tried generating my own SQL using that trick http://blog.sqlauthority.com/2007/06/08/sql-server-insert-multiple-records-using-one-insert-statement-use-of-union-all/, then using NHibernate's IStatelessSession for executing the sql. => slowest of all :-). Thank you, string concatenation. Quick deductions & notes : - I prefer avoiding mixing two technologies which has the same purpose, especially when I do not master one of those. - NHibernate uses ExecuteNonQuery() itself (watched the source code) - NHibernate does a better job than me for query optimizing. - I think that the network traffic and the test server (physically far) are performance-killer. 75 seconds to insert a collection of 45-60 000 items??? - No indexes are used. FK + item index in list should be enough, shouldn't it? - Increasing the batch size to 5000 did not significantly increase performance Agree with Fabio, the fastest way to insert a lot of data in db is to > use bulk insert (for example using SqlDataAdapter, > http://sqlite.phxsoftware.com/forums/t/134.aspx ). Also it will be > better for performance to turn off indexes during these inserts (e.g. > turn them off before inserts and turn them on after, this is because > slq server will calculate them after each insert) > > On May 24, 1:35 am, Fabio Maulo <[email protected]> wrote: > > ah... and before think about NH perf you should think in doing the same > with > > pure ADO.NET. > > > > 2009/5/23 Fabio Maulo <[email protected]> > > > > > > > > > for NH2.0.1:1) NH is not the right way to manage massive data > manipulation > > > 2) try statelesssession > > > 3) if you want use statefull session close or clear it each 500 > iteration > > > (for example) > > > 4) NH2.1.0 has executable HQL > > >http://fabiomaulo.blogspot.com/2009/05/nhibernate-210-executable-quer. > .. > > > > > 2009/5/22 Nelson <[email protected]> > > > > >> Hi, > > > > >> My current application heavily relies on NHibernate 2.0.1. I currently > > >> have a bottleneck, in which my application might insert around 6 000 > > >> 000 entities (calculations in facts). I currently resolved the problem > > >> by setting the adonet.bath_size property to 100. > > > > >> With a batch-size of 100, 36000 entities are inserted in 100 seconds. > > >> As I am a great mathematician, I have deduced that inserting 6 000 000 > > >> (6 millions!) entities might take up to 240 minutes (=4 hours). I > > >> think it is due to the 6 000 000 / 100 = 60 000 queries (slow network > > >> I guess). > > > > >> Supposing this is linear time problem, setting the batch size to 1000 > > >> should divide this time by 10, and to 2000 should make a total time of > > >> 12 minutes, which would be awesome. But, when setting the batch size > > >> to 1000 I noticed that insert commands where batched by 100. > > > > >> My question is then : where does this limit comes from? I can't find > > >> any answer on google. You are my last hope NHUSER GROUP!! > > > > >> Have a great weekend ! > > > > > -- > > > Fabio Maulo > --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "nhusers" group. 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 -~----------~----~----~----~------~----~------~--~---
