Thank you, Greg. I did not know it is called "Select N+1" problem :-)
Now it runs like a charm. But if TestMaster contains many columns and TestDetail contains many rows then "all in one select" solution transfers duplicate and needless data. Is there some "Select 1+1" solution in NHibernate? Thank you IvoD Dne úterý 5. května 2015 12:53:04 UTC+2 Greg Young napsal(a): > > google "select n + 1 nhibernate" you will find lots of discussion > > On Tue, May 5, 2015 at 1:49 PM, IvoD <[email protected] <javascript:>> > 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] <javascript:>. > > To post to this group, send email to [email protected] > <javascript:>. > > 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.
