It's a bit tricky for me to understand your exact requirements I'm afraid, though I appreciate the effort you're putting in to try to articulate them! So far, I've got the following understanding:
1. ISession's are scoped on a per-form lifetime - changing this design is 'challenging'. What's the lifespan of a form in this application? 2. There are some 150K rows loaded into the session spanning a very complex graph 3. This data is being serialised to XML The reference to un-proxying the child objects/collections implies that the data's not being eagerly loaded (otherwise this step wouldn't be necessary). Something I'm not clear about is whether you need to serialise just the changed data or the entire object graph? I'm also struggling a bit to understand the use-case where a single form requires that quantity of data loading? This 'feels' a bit more like an ETL-type problem... if you're just wanting to export it to XML as opposed to doing typical ORM operations then raw ADO.NET is probably a better tool for the job, but it also sounds as though you might have a bit of a hybrid requirement going on here. From: nhusers@googlegroups.com [mailto:nhusers@googlegroups.com] On Behalf Of Jonathan Crowe Sent: 23 April 2013 16:23 To: nhusers@googlegroups.com Subject: Re: [nhusers] Efficiently loading a large object graph in a multi-threaded winforms application. I am actually working on configuring log4net this morning so the application will have a standard way of logging errors. So now I am using log4net to capture the SQL instead of an interceptor. I agree that using DTO's instead of the entity's directly would be the best practice, and if i'd been a part of the project when it started that's what I would have done. It may still be a possibility to refactor to that model, but the project manager has to make that decision since we have deadlines we must meet. So yes, we don't have the best design for our UI thread since we keep the ISession open for the life of the parent form to avoid the lazy initialization exception. So if we put that issue on the back-burner, is there a way to more efficiently serialize the entire data model into XML so it can be transmitted? Right now we get the parent entity, un-proxy all the child objects/collections, and then serialize it using the DataContractSerializer. This is my main issue. I am used to working with SQL Server and Oracle via ado.net and have never had troubles loading this many records in a few seconds; I mean it is only 150,000 records not 1.5 million, haha. On Tuesday, April 23, 2013 9:01:52 AM UTC-5, PeteA wrote: Logging is relatively simple by tweaking the log4net settings appropriately, I tend to route it to both a file + the VS debug window. Re. the ERD below... are you attempting to load the contents of the entire DB into memory - I'm wondering if the problem is a consequence of technique rather than requirement. In general, one opens an ISession per unit-of-work; so, for a WinForms app, I'd use a pattern roughly like this: - User opens a form - Form opens a session and loads data into entities, maps entities onto DTO's, then closes the session - Form binds the DTO's to controls - User edits controls (and hence DTO's) - User hits save - Form opens a session and loads entities, maps DTO's onto loaded entitys, then commits & closes the session I'm concerned that you may be trying to use a single ISession for the entire UI [thread]? From: nhu...@googlegroups.com <javascript:> [mailto:nhu...@googlegroups.com <javascript:> ] On Behalf Of Jonathan Crowe Sent: 23 April 2013 14:35 To: nhu...@googlegroups.com <javascript:> Subject: Re: [nhusers] Efficiently loading a large object graph in a multi-threaded winforms application. I've attached a doctored image of the ERD someone on our team put together. The tables without numbers currently have no data, but will eventually but not that much in comparison to the tables that have 1000+ records. I've labeled the parent table as well as the "big table" which has the majority of the records. All objects inherit from the same parent class, but I enabled UseUnionSubclassForInheritanceMapping to reduce the joins occurring; also, if it matters, our IDs are guids. I did try 2nd level cache (a modified version of syscache using System.Runtime.Caching) but it didn't seem to have any effect. Are there any monitoring tools that don't require an installation? I have no admin rights; I have logged the SQL to a file to see what was going on. <https://lh5.googleusercontent.com/-ht14vjZlGEg/UXaNOjNgj3I/AAAAAAABXMc/ i5-RGZHHl9U/s1600/Untitled.png> On Tuesday, April 23, 2013 7:43:48 AM UTC-5, PeteA wrote: <https://lh5.googleusercontent.com/-ht14vjZlGEg/UXaNOjNgj3I/AAAAAAABXMc/ i5-RGZHHl9U/s1600/Untitled.png> Can you give us an idea of the graph's shape and approximate row counts? <https://lh5.googleusercontent.com/-ht14vjZlGEg/UXaNOjNgj3I/AAAAAAABXMc/ i5-RGZHHl9U/s1600/Untitled.png> <https://lh5.googleusercontent.com/-ht14vjZlGEg/UXaNOjNgj3I/AAAAAAABXMc/ i5-RGZHHl9U/s1600/Untitled.png> Couple of immediate thoughts: <https://lh5.googleusercontent.com/-ht14vjZlGEg/UXaNOjNgj3I/AAAAAAABXMc/ i5-RGZHHl9U/s1600/Untitled.png> - Are you using a 2nd level cache? <https://lh5.googleusercontent.com/-ht14vjZlGEg/UXaNOjNgj3I/AAAAAAABXMc/ i5-RGZHHl9U/s1600/Untitled.png> - Have you monitored the SQL sent to the DB and verified that your graph is being loaded with exactly one SQL batch? <https://lh5.googleusercontent.com/-ht14vjZlGEg/UXaNOjNgj3I/AAAAAAABXMc/ i5-RGZHHl9U/s1600/Untitled.png> <https://lh5.googleusercontent.com/-ht14vjZlGEg/UXaNOjNgj3I/AAAAAAABXMc/ i5-RGZHHl9U/s1600/Untitled.png> I recollect seeing a post from somebody who had achieved a significant speedup in entity instantiation when using the LINQ API, I don't know if it's applicable to your selection nor do I remember the details I'm afraid, but it could be worth googling for... <https://lh5.googleusercontent.com/-ht14vjZlGEg/UXaNOjNgj3I/AAAAAAABXMc/ i5-RGZHHl9U/s1600/Untitled.png> <https://lh5.googleusercontent.com/-ht14vjZlGEg/UXaNOjNgj3I/AAAAAAABXMc/ i5-RGZHHl9U/s1600/Untitled.png> /Pete <https://lh5.googleusercontent.com/-ht14vjZlGEg/UXaNOjNgj3I/AAAAAAABXMc/ i5-RGZHHl9U/s1600/Untitled.png> <https://lh5.googleusercontent.com/-ht14vjZlGEg/UXaNOjNgj3I/AAAAAAABXMc/ i5-RGZHHl9U/s1600/Untitled.png> From: nhu...@googlegroups.com [mailto:nhu...@googlegroups.com] On Behalf Of Jonathan Crowe Sent: 23 April 2013 02:21 To: nhu...@googlegroups.com Subject: [nhusers] Efficiently loading a large object graph in a multi-threaded winforms application. <https://lh5.googleusercontent.com/-ht14vjZlGEg/UXaNOjNgj3I/AAAAAAABXMc/ i5-RGZHHl9U/s1600/Untitled.png> <https://lh5.googleusercontent.com/-ht14vjZlGEg/UXaNOjNgj3I/AAAAAAABXMc/ i5-RGZHHl9U/s1600/Untitled.png> I have a winforms application written in C#/.Net 4.0 using nHibernate 3.3.3, Fluent 1.3, and SQLite 1.0.84. I have been dealing with a requirement where a user chooses a particular entity in our system and we must 1) open the record up for viewing/editing, and 2) without blocking the UI, serialize the entity to be sent to another system that we have to communicate with as changes are made; I could get a little more detailed but that is the general idea. I have on ISession dedicated to the UI (since lazy loading is enabled) and another ISession for the background process. Whenever the records are changed on the UI thread we must also update the external process. Also, having a long delay before making the UI isn't really acceptable; we might get by with 10seconds or so if needed. The problem is that the entities I am dealing with are the parent to a complex object graph with lots of data (lets just say the application memory usage goes to roughly 750,000K+ when the whole record is loaded) and it takes nHibernate a long time to fully loaded it (around 2-3 minutes without any optimizations). I have tried using .Future() queries to reduce the number of database calls which has gotten the load to 30-40 seconds, however there seems to be a point where the load time starts increasing (because of too many queries executed in one go???). I've also tried using some fetch join scenarios without out much gain. I've spent quite a bit of time trying to come up with different approaches and nothing seems to help. So I am looking to the nHibernate community for some guidance. Have I hit the performance "wall" so to speak, or are there other approaches that you can recommend? <https://lh5.googleusercontent.com/-ht14vjZlGEg/UXaNOjNgj3I/AAAAAAABXMc/ i5-RGZHHl9U/s1600/Untitled.png> -- You received this message because you are subscribed to the Google Groups "nhusers" group. To unsubscribe from this group and stop receiving emails from it, send an email to nhusers+u...@googlegroups.com. To post to this group, send email to nhu...@googlegroups.com. Visit this group at http://groups.google.com/group/nhusers?hl=en-US. For more options, visit https://groups.google.com/groups/opt_out. <https://lh5.googleusercontent.com/-ht14vjZlGEg/UXaNOjNgj3I/AAAAAAABXMc/ i5-RGZHHl9U/s1600/Untitled.png> -- You received this message because you are subscribed to the Google Groups "nhusers" group. To unsubscribe from this group and stop receiving emails from it, send an email to nhusers+u...@googlegroups.com. To post to this group, send email to nhu...@googlegroups.com. Visit this group at http://groups.google.com/group/nhusers?hl=en-US. For more options, visit https://groups.google.com/groups/opt_out. <https://lh5.googleusercontent.com/-ht14vjZlGEg/UXaNOjNgj3I/AAAAAAABXMc/ i5-RGZHHl9U/s1600/Untitled.png> -- You received this message because you are subscribed to the Google Groups "nhusers" group. To unsubscribe from this group and stop receiving emails from it, send an email to nhusers+unsubscr...@googlegroups.com. To post to this group, send email to nhusers@googlegroups.com. Visit this group at http://groups.google.com/group/nhusers?hl=en-US. For more options, visit https://groups.google.com/groups/opt_out. <https://lh5.googleusercontent.com/-ht14vjZlGEg/UXaNOjNgj3I/AAAAAAABXMc/ i5-RGZHHl9U/s1600/Untitled.png> -- You received this message because you are subscribed to the Google Groups "nhusers" group. To unsubscribe from this group and stop receiving emails from it, send an email to nhusers+unsubscr...@googlegroups.com. To post to this group, send email to nhusers@googlegroups.com. Visit this group at http://groups.google.com/group/nhusers?hl=en-US. For more options, visit https://groups.google.com/groups/opt_out.
