google "select n + 1 nhibernate" you will find lots of discussion
On Tue, May 5, 2015 at 1:49 PM, IvoD <[email protected]> wrote: > Hi All, > > I have basic performance question about NHibernate. > > Suppose two simple test tables: > > CREATE TABLE "TestMaster" > ( > "IdM" serial NOT NULL, > "Text" character varying(20), > CONSTRAINT "PK_TestMaster" PRIMARY KEY ("IdM" ) > ); > > CREATE TABLE "TestDetail" > ( > "IdD" serial NOT NULL, > "IdM" integer NOT NULL, > "Text" character varying(30), > CONSTRAINT "PK_TestDetail" PRIMARY KEY ("IdD" ), > CONSTRAINT "FK_TestDetail_TestMaster" FOREIGN KEY ("IdM") > REFERENCES "TestMaster" ("IdM") MATCH SIMPLE > ON UPDATE CASCADE ON DELETE RESTRICT > ); > > for these two tables I have these two mappings: > > <class name="TestMaster" schema="public" table="`TestMaster`" lazy="true"> > <id name="IdM" column="`IdM`" /> > <property name="Text" column="`Text`" /> > <bag name="ListTestDetail" schema="public" table="`TestDetail`" > inverse="true" cascade="all-delete-orphan"> > <key column="`IdM`" /> > <one-to-many class="TestDetail" /> > </bag> > </class> > <class name="TestDetail" schema="public" table="`TestDetail`" lazy="true"> > <id name="IdD" column="`IdD`" /> > <property name="Text" column="`Text`" /> > <many-to-one name="LinkTestMaster" class="TestMaster" column="`IdM`" /> > </class> > and two classes: > > public class TestMaster > { > public virtual int IdM { get; set; } > public virtual string Text { get; set; } > public virtual IList<ORM.TestDetail> ListTestDetail { get; set; } > } > > public class TestDetail > { > public virtual int IdD { get; set; } > public virtual String Text { get; set; } > public virtual TestMaster LinkTestMaster { get; set; } > } > Suppose there are 1000 rows in the TestMaser table. If I run this code: > > using (ISession ormSession = OrmSessionFactory.OpenSession()) { > IList<ORM.TestMaster> lm = ormSession.CreateQuery("from > TestMaster").List<ORM.TestMaster>(); > int cnt = 0; > foreach (ORM.TestMaster tm in lm) { > cnt += tm.ListTestDetail.Count; > } > } > > then I see 1+1000 selects in the output window (I know why - first select > for the TestMaster table and 1000 selects for TestDetail where IdM=... when > I evaluate the detail records) and the performance is very low. I tried to > change the test code to: > > using (ISession ormSession = OrmSessionFactory.OpenSession()) { > IList<ORM.TestMaster> lm = ormSession.CreateQuery("from > TestMaster").List<ORM.TestMaster>(); > IList<ORM.TestDetail> ld = ormSession.CreateQuery("from > TestDetail").List<ORM.TestDetail>(); > int cnt = 0; > foreach (ORM.TestMaster tm in lm) { > cnt += tm.ListTestDetail.Count; > } > } > in order to read all TestDetail records into the NHibernate's inner memory > (and let him read TestDetail records from inner memory instead of database) > but no success. > > Is there some way I can convert scenario 1+1000 to 1+1 (1st select for > TestMaster, 2nd select for TestDetail)? > > I'm sure this behavior comes from my misunderstandig and low NH knowledge, > but I was not able to find the solution in this group. > > Thank you > > IvoD > > -- > 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 [email protected]. > To post to this group, send email to [email protected]. > Visit this group at http://groups.google.com/group/nhusers. > For more options, visit https://groups.google.com/d/optout. -- Studying for the Turing test -- 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 [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/nhusers. For more options, visit https://groups.google.com/d/optout.
