Sorry to bump this thread but when I posted this I thought I got it to
work. But it required some other changes in the system so I did not
implement the solution at the time. Now when I had time to implement
it I cannot get it to work anymore :-(.
So I have isolated the problem to a small test project. The goal is to
do like in the typed dataset system, use a single SELECT to select a
whole hierarchy of objects into memory and then traverse them in
memory without generating further SELECT.
Is there some way of doing this in NHibernate. Perhaps some extension
pint I can use?
Here are the files for the testproject:
SQL
--------
CREATE TABLE Test1(
Id int NOT NULL,
ParentId int NULL,
Data varchar(50) NULL,
PRIMARY KEY(Id)
)
INSERT INTO Test1(Id, ParentId, Data) VALUES(1, NULL, 'Node 1')
INSERT INTO Test1(Id, ParentId, Data) VALUES(11, 1, 'Node 1.1')
INSERT INTO Test1(Id, ParentId, Data) VALUES(111, 11, 'Node 1.1.1')
INSERT INTO Test1(Id, ParentId, Data) VALUES(2, NULL, 'Node 2')
Test1.hbm.xml
-----------------------
<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
assembly="NHibernateTest1" namespace="NHibernateTest1">
<class name="Test1" table="Test1">
<id name="Id" unsaved-value="-1">
<column name="Id" />
<generator class="assigned" />
</id>
<property name="Data" />
<!-- Parent -->
<many-to-one name="Parent"
class="Test1"
cascade="none"
insert="false"
update="false">
<column name="ParentId" />
</many-to-one>
<!-- Children -->
<bag name="Children"
inverse="true"
cascade="all-delete-orphan"
access="field.camelcase-underscore">
<key>
<column name="ParentId" />
</key>
<one-to-many class="Test1"/>
</bag>
</class>
</hibernate-mapping>
Test1.vb
-------------
Imports System.Collections.ObjectModel
Imports System.Diagnostics
Imports System.Collections.Generic
Public Class Test1
Private _id As Integer
Private _parentId As Integer
Private _data As String
Private _parent As Test1
Private _children As IList(Of Test1) = New List(Of Test1)
Public Overridable Property Id() As Integer
Get
Return _id
End Get
Set(ByVal value As Integer)
_id = value
End Set
End Property
Public Overridable Property Parent() As Test1
Get
Return _parent
End Get
Set(ByVal value As Test1)
_parent = value
End Set
End Property
Public Overridable Property Data() As String
Get
Return _data
End Get
Set(ByVal value As String)
_data = value
End Set
End Property
Public Overridable ReadOnly Property Children() As ReadOnlyCollection
(Of Test1)
Get
Return New ReadOnlyCollection(Of Test1)(_children)
End Get
End Property
End Class
Form1.vb
--------------
Imports System
Imports System.Collections
Imports NHibernate
Imports NHibernate.Cfg
Public Class Form1
Private _sessionFactory As ISessionFactory
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
'Create factory
Dim cfg As New Configuration()
cfg.SetProperty("connection.provider",
"NHibernate.Connection.DriverConnectionProvider")
cfg.SetProperty("dialect", "NHibernate.Dialect.MsSql2005Dialect")
cfg.SetProperty("connection.driver_class",
"NHibernate.Driver.SqlClientDriver")
cfg.SetProperty("connection.connection_string",
"Server=localhost;Database=Test01;Integrated Security=SSPI")
cfg.AddAssembly("NHibernateTest1")
cfg.SetProperty("show_sql", "true")
_sessionFactory = cfg.BuildSessionFactory()
End Sub
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click
Dim session As ISession = _sessionFactory.OpenSession()
Try
'This generates a single SELECT and puts all objects in a flat
list
Dim crit = session.CreateCriteria(GetType(Test1))
Dim list = crit.List(Of Test1)()
'This generates another 2 SELECT but the same objects and all
its children are already loaded in the first list
Dim x = list(0).Children(0).Children(0)
'When there are 3000 objects and 10 levels, searching the
hierarchy recursivly generates a lot of SELECT
'How can we load all object into RAM with one SELECT?
Stop
Finally
session.Close()
End Try
End Sub
End Class
On Mar 19, 8:10 pm, Fabio Maulo <[email protected]> wrote:
> I mean do the equivalent in HQL or Criteria
>
> 2009/3/19 kello <[email protected]>
>
>
>
>
>
>
>
> > I actually tried to use such a query and return only the root entity
> > only I am not using DAOs but a Repository which directly uses the
> > nhibernate session to create a SQL Query. Much simplified It goes
> > something like this:
>
> > Dim query = Session.CreateSQLQuery("SELECT * FROM BomLines WHERE
> > BomId=foo ORDER BY foo")
> > Dim list = query.List()
> > Return list(0)
>
> > What happened was that the children collections were still loaded by a
> > SELECT each when traversing the tree from the root entity. I think it
> > is because the query only returns a flat list of all the entities, it
> > does not load the children collection in each entity?
>
> > /Jonas
>
> > On Mar 19, 4:13 am, Fabio Maulo <[email protected]> wrote:
> > > You can use a similar select you are using in the old system and then you
> > > can return only the root from your DAO.BTW 3000 entities in RAM will have
> > > some cost.
>
> > > 2009/3/18 kello <[email protected]>
>
> > > > I am working on a system that has Bill Of Materials (BOM) which
> > > > contains about 1000-3000 parts organized in a hierarchy about 10
> > > > levels deep. The system was using Typed Datasets but I have converterd
> > > > parts of it to use an object model and NHibernate for persistance.
>
> > > > The problem is that the NHibernate version is currently much slower
> > > > than the dataset version because of the time it takes to load a BOM
> > > > into memory. In the old system the entire BOM is loaded into a dataset
> > > > in less than a second using a single SELECT (then there is of course a
> > > > lot of ugly code working with the dataset in-memory).
>
> > > > Now in the NHibernate version the BOM is loaded as a root entity which
> > > > has a persistent collection of its parts which are entities of the
> > > > same class and they have collection of their parts etc. This causes
> > > > the loading to happen with one SELECT per collection load. I have read
> > > > the performance part of the reference docs and have added "batch-size"
> > > > to get more collections loaded with each level and it helped some but
> > > > it is still generating a lot of SELECT since the BOM is so large and
> > > > have lots of levels. Using a very large batch-size does not seem to
> > > > work either....
>
> > > > It is quite easy to make a special query using a single SELECT to get
> > > > all the objects in the BOM back in a single list but then I loose the
> > > > hierarchical organization since the collection of children in each
> > > > object are not loaded and when the collections are accessed they will
> > > > each generate a new SELECT to get filled anyway.
>
> > > > I thought about making a special repository method to load all objects
> > > > in a single list with a single SELECT and then using some sorting code
> > > > to put the objects from this list into each BOM objects childrens
> > > > collection. The problem with this approach is that since the children
> > > > collection list is a mapped persistant collection the proxy object
> > > > will fill the collection when I access it. I would have to find some
> > > > way to temporarily bybass the proxy object's collection loading and
> > > > fill the childrens collection manually but I don't know how... or am I
> > > > on the wrong track here? Is there some other way to fill all
> > > > collections in a single SELECT but still have them mapped so they
> > > > cacade save etc.?
>
> > > > Another problem is the deletion of a BOM. What happens is that first a
> > > > lot of SELECT is generated to sort out which objects belong to the BOM
> > > > and then a lot of DELETE is generated. It would be more effective if I
> > > > could do a single DELETE and then find and evict any BOM objects that
> > > > might be loaded in the session for the BOM being deleted. Could this
> > > > be done?
>
> > > --
> > > Fabio Maulo- Hide quoted text -
>
> > > - Show quoted text -
>
> --
> Fabio Maulo- Hide quoted text -
>
> - Show quoted text -
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
