this is the corresponding HQL of your SQL select u.Name, u.Year, p.Name from User u join u.RealPosition p
but, as you can see, there isn't the User instance nor the RealPosition instance... only scalar values. 2009/11/3 mitirino <[email protected]> > > Thank you. It does produce one query but runs 10 times slower than the > very simmillar query with DataReader > > this is the query with DataReader > > command.CommandText = "SELECT u.name, u.year, p.name FROM User u JOIN > Position p ON u.position_id=p.id;"; > using (SQLiteDataReader dataReader = command.ExecuteReader()) > { > while (dataReader.Read()) > { > User user = new User(dataReader.GetString(0), > dataReader.GetInt32 > (1), dataReader.GetString(2)); > users.Add(user); > } > } > > and with NH > > IList<User> users = > session.CreateQuery("select u from User u join > fetch u.RealPosition").List<User>(); > > return users; > > > On 100000 rows in User it runs within less than 1 sec with the > DataReader. > > With the HQL you suggested NH is producing almost exact same SQL as > the manual query above but runs in 12-13 sec. > > I realize it is not exactly equal test as the one with DataReader > doesn't care for the Position objects but is there something I can do > to get a comparable performance form NH? > > > > > > On Nov 2, 6:21 pm, Fabio Maulo <[email protected]> wrote: > > select u from User u join fetch u.RealPosition > > > > 2009/11/2 mitirino <[email protected]> > > > > > > > > > > > > > > > > > Hi, > > > > > I have many-to-one for User and Position and when I want to load the > > > users it makes one select to get all the users and then one select per > > > user to get the position for each one. > > > > > Is there any way to avoid this and actually load everything with one > > > select? > > > > > Thank you > > > > > ---- > > > > > IList<User> items = session.CreateQuery("from User").List<User>(); > > > > > --- > > > > > <class name="User" table="User" > > > > <id name="Id" column="id"> > > > <generator class="native" /> > > > </id> > > > <property name="Name" column="name" /> > > > <property name="Year" column="year" /> > > > <many-to-one name="RealPosition" > > > class="Position" > > > column="position_id" /> > > > </class> > > > > > ---- > > > > > <class name="Position" table="Position" > > > > <id name="Id" column="id"> > > > <generator class="native" /> > > > </id> > > > <property name="Name" column="name" /> > > > </class> > > > > -- > > Fabio Maulo- Hide quoted text - > > > > - Show quoted text - > > > -- 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 -~----------~----~----~----~------~----~------~--~---
